Display calculation problem for each row from database

[Skylight_lady]

Hi Guys, i am trying to get my head around doing a calculation for the total and getting the discount from each row and display the total. I am using both mySql and php. How do i get the TOTAL BALANCE (this is = total balance - the discount) and the TOTAL PAYMENT DUE (this is = TOTAL CLIENT PAYMENT - TOTAL BALANCE). Please note that the discount settings are different for each row and there are an number of different rows.

 

The table is as follows:

`invoiceID` int(11) NOT NULL AUTO_INCREMENT,
  `USERID` varchar(255) NOT NULL DEFAULT '',
  `CLIENTID` varchar(150) NOT NULL DEFAULT '',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Discount` double(4,2) NOT NULL DEFAULT '0.00'

 

The page code is as follows:

 

<?php
$query = "SELECT ID FROM clients WHERE username = '$u'";
$result = mysql_query($query);
if(mysql_num_rows($result))
{
while($row = mysql_fetch_assoc($result)){
	$USERID = $row['ID'];
	$client = mysql_real_escape_string($_GET['id']);
	$query = "SELECT * FROM payment WHERE USERID = '$USERID' AND CLIENTID = '$client'";
	$result = mysql_query($query);
	$num=mysql_numrows($result);
?>
<table>
<thead><tr>
        <th>Invoice ID</th>
        <th>Discount</th>
        <th>Cost</th>
        <th>Balance</th>
        </tr>
</thead>
<tbody>
<?php
	$i=0;
	while ($i < $num) {
		$invoiceID=mysql_result($result,$i,"invoiceID");
		$USERID=mysql_result($result,$i,"USERID");
		$CLIENTID=mysql_result($result,$i,"CLIENTID");
		$amount=mysql_result($result,$i,"amount");
		$Discount=mysql_result($result,$i,"Discount");
		$balance = $amount - (($amount/100)*$Discount);
?>
<tr>
<td><?php echo $invoiceID; ?></td>
<td><?php echo $Discount; ?></td>
<td><?php echo $amount; ?></td>
<td><?php echo number_format($balance,2); ?></td>
</tr>
<?php
		$i++;
	}
}
}
$CLIENTID = mysql_real_escape_string($_GET['id']);
$USERID = mysql_real_escape_string($_GET['USERID']);
$sql = "SELECT SUM(amount) AS TOTALPAYMENT FROM payment WHERE CLIENTID = '".$_GET['id']."' AND USERID = '".$_GET['USERID']."'" ;
$resulte = mysql_query($sql);
mysql_close();
if(mysql_num_rows($resulte))
{
while($row = mysql_fetch_assoc($resulte)){
	$amount = $row['amount'];
	$Discount = $row['Discount'];
	$totalPayment = $row['TOTALPAYMENT'];
	$balance = $amount - (($amount/100)*$Discount);
?>
</tbody><tfoot>
<tr><td></td></tr>
</tfoot>
</table>
<table>
<tbody>
<tr>
<td>Total Client Payment:</td>
    <td><?php echo number_format($totalPayment,2); ?></td>
</tr>
<tr>
<td>Total Balance:</td>
    <td><?php echo number_format($balance,2); ?></td>
</tr>
<tr>
<td>Total Payment due:</td>
    <td><?php echo ""; ?></td>
</tr></tbody></table>
<?php
}
}
?>

 

I forgot to note that the TOTAL BALANCE calculation is not correct. Any help would be appreciated.

[Skylight_lady]

No advice? It'll take me a while to figure it out.

[sasa]

change

<?php
	$i=0;
	while ($i < $num) {
		$invoiceID=mysql_result($result,$i,"invoiceID");
		$USERID=mysql_result($result,$i,"USERID");
		$CLIENTID=mysql_result($result,$i,"CLIENTID");
		$amount=mysql_result($result,$i,"amount");
		$Discount=mysql_result($result,$i,"Discount");
		$balance = $amount - (($amount/100)*$Discount);
?>

to

<?php
	$i=0;
                $total_amount=0;
                $total_balance =0;
	while ($i < $num) {
		$invoiceID=mysql_result($result,$i,"invoiceID");
		$USERID=mysql_result($result,$i,"USERID");
		$CLIENTID=mysql_result($result,$i,"CLIENTID");
		$amount=mysql_result($result,$i,"amount");
		$Discount=mysql_result($result,$i,"Discount");
		$balance = $amount - (($amount/100)*$Discount);
                        $total_amount += $amount;
                        $total_balance += $balance;
?>

and after while loop just echo totals

[Skylight_lady]

Thanks. I had to make a small change to that. i'm not using:

 

+=

 

And additional code was required. However, i noticed that i'm using:

echo number_format($balance,2);

 

In the html table row the amounts are displayed like:

236.92
236.92

 

So when i display the total from that i get at total of:

473.83

 

when it should be 473.84. I know its because of the long amount thats only showing the last 2 numbers after the decimal point.

 

Is there a solution to correct this?

[sasa]

change to

$total_balance += round($balance,2);

?>