nealios Posted January 10, 2008 Share Posted January 10, 2008 My brother is VAT registered that means on certain bits of income he can claim VAT back. I have a table that stores all income. The table has a field that states whether a record is vatable or not. I then have a page which outputs all the records that are vatable. WHERE vattype='yes') I then perform some calculations taking the VAT off the income and calculating the net figure. This all works corrrectly.. $sql = "SELECT incomeid, intransactiondate, invoiceno, amount, ROUND(amount * 7/47, 2) AS vat, ROUND(amount-(amount* 7/47), 2) AS net FROM `income`, `vat` WHERE income.vatid = vat.vatid AND vattype='yes' AND `intransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' order by $sort"; I then can also output all the jobs that arent vatable...no calculations neccessary. $sql3 = "SELECT incomeid, intransactiondate, invoiceno, amount FROM `income`, `vat` WHERE income.vatid = vat.vatid AND vattype='no' AND `intransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' order by $sort"; However i want to be able to output all these records into a single table, rather than two seperate ones. So i need to be able to calculate VAT on all the fields (WHERE vattype='yes') and i simply need to output the other non vatable records(WHERE vattype='no') Im finding it tricky, any ideas? Here is the whole script <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> <title>Income Search</title> </head> <body> <?php require('connect.php'); if ( isset($_GET['sort']) ) { //Checks whether the variable sort is sorted by selected field switch( $_GET['sort'] ) { case 'incomeid': $sort = 'incomeid'; break; case 'intransactiondate': $sort = 'intransactiondate'; break; case 'invoiceno': $sort = 'invoiceno'; break; case 'amount': $sort = 'amount'; break; case 'vat': $sort = 'vat'; break; case 'net': $sort = 'net'; break; default: $sort = 'intransactiondate'; } } else { $sort = 'intransactiondate';//or default sort by surname } $date1 = $_POST['date1']; $date2 = $_POST['date2']; $sql = "SELECT incomeid, intransactiondate, invoiceno, amount, ROUND(amount * 7/47, 2) AS vat, ROUND(amount-(amount* 7/47), 2) AS net FROM `income`, `vat` WHERE income.vatid = vat.vatid AND vattype='yes' AND `intransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' order by $sort"; $result = mysql_query($sql, $connection) or die("unable to perform query<br>$sql"); ?> <table border="1" align="center" cellpadding="4"> <tr> <th><a href="daterangesearchincome.php?sort=incomeid">Transaction ID</a></th> <th><a href="daterangesearchincome.php?sort=intransactiondate">Transaction Date</a></th> <th><a href="daterangesearchincome.php?sort=invoiceno">Invoice No</a></th> <th><a href="daterangesearchincome.php?sort=amount">Gross</a></th> <th><a href="daterangesearchincome.php?sort=vat">VAT</a></th> <th><a href="daterangesearchincome.php?sort=net">NET</a></th> </tr> <?php while($row = mysql_fetch_array($result)) { ?> <tr> <td><?=$row['incomeid']?></td> <td><?=$row['intransactiondate']?></td> <td><?=$row['invoiceno']?></td> <td><?=$row['amount']?></td> <td><?=$row['vat']?></td> <td><?=$row['net']?></td> </tr> <?php } ?> </table> <?php $sql3 = "SELECT incomeid, intransactiondate, invoiceno, amount FROM `income`, `vat` WHERE income.vatid = vat.vatid AND vattype='no' AND `intransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' order by $sort"; $result3 = mysql_query($sql3, $connection) or die("unable to perform query<br>$sql3"); ?> <p> <table border="1" align="center" cellpadding="4"> <tr> <th>Income ID</th> <th>Transaction Date</th> <th>Invoice No</th> <th>Amount</th> </tr> <?php while($row1 = mysql_fetch_array($result3)) { ?> <tr> <td><?=$row1['incomeid']?></td> <td><?=$row1['intransactiondate']?></td> <td><?=$row1['invoiceno']?></td> <td><?=$row1['amount']?></td> </tr> <?php } ?> </table> </body> </html> Link to comment https://forums.phpfreaks.com/topic/85396-solved-outputting-two-queries-into-one-table/ Share on other sites More sharing options...
nealios Posted January 10, 2008 Author Share Posted January 10, 2008 bump Link to comment https://forums.phpfreaks.com/topic/85396-solved-outputting-two-queries-into-one-table/#findComment-436049 Share on other sites More sharing options...
teng84 Posted January 10, 2008 Share Posted January 10, 2008 $sql = "SELECT incomeid, intransactiondate, invoiceno, amount, IF(vattype='yes',ROUND(amount * 7/47, 2),amount) AS vat, ROUND(amount-(amount* 7/47), 2) AS net FROM `income`, `vat` WHERE income.vatid = vat.vatid AND AND `intransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' order by $sort"; maybe that ^^ Link to comment https://forums.phpfreaks.com/topic/85396-solved-outputting-two-queries-into-one-table/#findComment-436058 Share on other sites More sharing options...
nealios Posted January 11, 2008 Author Share Posted January 11, 2008 Thanks for your reply, it kind of works. However what i want it do is in the alias vat column i want it to display the calculation where the record is yes. But if the vattype is set to 'no' i want it to appear null. so something like this but i dont know how to write the query. IF(vattype='yes',ROUND(amount * 7/47, 2),amount), IF(vattype='no',ROUND(null) AS vat My actual query. SELECT incomeid, intransactiondate, invoiceno, amount, IF(vattype='yes',ROUND(amount * 7/47, 2),amount) AS vat, IF(vattype='yes',ROUND(amount-(amount* 7/47), 2),amount) AS net FROM `income`, `vat` WHERE income.vatid = vat.vatid; any help with be appreciated! Thanks Link to comment https://forums.phpfreaks.com/topic/85396-solved-outputting-two-queries-into-one-table/#findComment-436337 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.