[SOLVED] Outputting two queries into one table

nealios · 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>

nealios · January 10, 2008

bump

teng84 · 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 ^^

nealios · 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

Sign In

[SOLVED] Outputting two queries into one table

Recommended Posts

nealios

Link to comment

Share on other sites

nealios

Link to comment

Share on other sites

teng84

Link to comment

Share on other sites

nealios

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information