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> Quote 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 Quote 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 ^^ Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.