I have the following code to search the stores by name and and order by transaction date by Asc which works perfect.
My questions is how to performe a subtraction by row (checkamount-redeposited-replacement-paidcash = balance) and totalize by columns (total checkamount, total redeposited, total replacement, total paidcash and total balance) by store name and by all stores using a drop down combo box.
See an exemplo:
Bounced checks report!
From Main Office 2012-01-01 To 2012-09-20
Check ID Transaction Date Store Name Check Date Date Cashed Date Return Check Number Maker Name Payee Name Bank Name Check Amount Return Reason Date Solved Redeposited Replacement Paid Cash Balance Disposition
16 9/6/2012 Main Office 9/6/2012 9/6/2012 9/6/2012 123 Maker Payee Bank 350.55 NSF 9/6/2012 100.00 10.00 10.00 230.55 none
63 9/6/2012 Main Office 9/2/2012 9/6/2012 9/6/2012 1531 House Renovations Co. Luis Tapioca Bank 1,600.00 Stop PAyment - - - 1,600.00
38 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 LEE Van Renovations LLC Luis Tapioca Bank 2,000.00 Stop Payment 9/15/2012 - - 800.00 1,200.00 partial payment
39 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 All Renovations LLC Luis Tapioca Bank 1,500.00 Stop Payment 9/15/2012 - - 800.00 700.00 partial payment
40 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 Great Renovations LLC Luis Tapioca Bank 1,300.00 Stop Payment 9/15/2012 - - 800.00 500.00 partial payment
64 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 Last Renovations Co. Luis Tapioca Bank 2,300.00 Stop PAyment 9/15/2012 - - 800.00 1,500.00
21 9/20/2012 Main Office 9/20/2012 9/20/2012 9/20/2012 22222 Maker Name Payee Name Bank 123.00 Reason 1/12/2012 123.00 123.00 123.00 (246.00) 123
22 9/20/2012 Main Office 9/20/2012 9/20/2012 9/20/2012 22222 Maker Name Payee Name Bank 123.00 Reason 1/12/2012 123.00 123.00 123.00 (246.00) test
23 9/20/2012 Main Office 9/20/2012 9/20/2012 9/20/2012 22222 Maker Name Payee Name Bank 123.00 Reason 1/12/2012 123.00 123.00 123.00 (246.00) test
Totals 9,419.55 469.00 379.00 3,579.00 4,992.55
...
<?php
include("db.php"); //this connection opens database for the stores names drop down
if(!isset($_POST['find']))
{
?>
<body bgcolor="#E6E6FA">
<form method = "post" action = "<?php echo $_SERVER['PHP_SELF'];?>">
<table width = "600" align = "center">
<tr>
<td><b><i>Please enter in the fields below Store Name and Date - smaller date first - (yyyy-mm-dd)</i></b></td>
</tr>
<tr>
<td>
<select name="storename">
<?php $result= mysql_query('SELECT * FROM stores_names'); ?>
<?php while($row= mysql_fetch_assoc($result)) { ?>
<option value="<?php echo htmlspecialchars($row['stores']);?>">
<?php echo htmlspecialchars($row['stores']); ?>
</option>
<?php } ?>
</select>
From <input type = "text" name = "small">
To <input type = "text" name = "large"></td>
</tr>
<tr>
<td align = "center">
<input type = "submit" name = "find" value = "SEARCH">
<input type = "reset" value = "CLEAR FORM">
</td>
</tr>
</table>
</form>
<?php
}
else
{
$storename = trim($_POST['storename']);
$small = trim($_POST['small']);
$large = trim($_POST['large']);
//$connection = mysql_pconnect("localhost", "root", "") or die("Connection failed. ".myslq_error());
//mysql_select_db("books") or die("Unable to select db. ".mysql_error());
//Add 1 to the upper range, $large, else it won't make it inclusive
$query = "SELECT * FROM books WHERE transactiondate BETWEEN DATE_FORMAT(transactiondate,'%".$small."') AND DATE_FORMAT(transactiondate, '%".($large)."') AND storename LIKE '$storename' ORDER BY transactiondate ASC";
//order by creationdate, creationtime desc;
$result = mysql_query($query) or die(mysql_error());
//echo "<span>Not logged in</span>" ;
echo "Bounced checks report!</br>";
echo "From", " ", $storename," ", $small, " To ", $large;
echo "<table width = '1000' align = 'center' border ='1'>";
echo "<tr><b>";
echo "<td>Check ID</td>";
echo "<td>Transaction Date</td>";
echo "<td>Store Name</td>";
echo "<td>Check Date</td>";
echo "<td>Date Cashed</td>";
echo "<td>Date Return</td>";
echo "<td>Check Number</td>";
echo "<td>Maker Name</td>";
echo "<td>Payee Name</td>";
echo "<td>Bank Name</td>";
echo "<td>Check Amount</td>";
echo "<td>Return Reason</td>";
echo "<td>Date Solved</td>";
echo "<td>Redeposited</td>";
echo "<td>Replacement</td>";
echo "<td>Paid Cash</td>";
echo "<td>Balance</td>";
echo "<td>Disposition</td>";
echo "</b></tr>";
while($record = mysql_fetch_object($result))
{
echo "<tr>";
echo "<td>".$record->BookID."</td>";
$year_part_of_date = explode('-', $record->transactiondate);
//echo "<td>".$year_part_of_date[0]."</td>";
echo "<td>".$record->transactiondate."</td>";
//if you want the full date replace the $year_part_of_date[0] with $record->date
echo "<td>".$record->storename."</td>";
echo "<td>".$record->checkdate."</td>";
echo "<td>".$record->datecashed."</td>";
echo "<td>".$record->datereturn."</td>";
echo "<td>".$record->checknumber."</td>";
echo "<td>".$record->makername."</td>";
echo "<td>".$record->payeename."</td>";
echo "<td>".$record->bankname."</td>";
echo "<td>".$record->checkamount."</td>";
echo "<td>".$record->returnreason."</td>";
echo "<td>".$record->datesolved."</td>";
echo "<td>".$record->redeposited."</td>";
echo "<td>".$record->replacement."</td>";
echo "<td>".$record->paidcash."</td>";
echo "<td>".$record->balance."</td>";
echo "<td>".$record->disposition."</td>";
echo "</tr>";
}
echo "</table>";
}
?>
](/attachments/large/2/books_table.jpg "books_table")](https://static.daniweb.com/attachments/2/books_table.jpg "align-left")
