How to total a numeric field/column?

Simple table results require a total at end of output. how to achieve?

[hugotobi]

This is the script that works just fine to output the data nicely into a table of results, I need to get the total to show in the last row for "Payments" and Reciepts". How do I do it?

----------

<?
include("dbconect.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM this_table ORDER by date asc, id asc";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "</p>
<p>";

echo "<b><center><font color=#3300CC><input type=button value=Back onclick=history.back()></font><br>";

//?><font face="Arial, Helvetica, sans-serif"><b><center><font color=#FF0000><? echo ""?></center></b></p></font>
<div align="center">

<div align="center">
<center><table border="0" cellspacing="1" cellpadding="2" bgcolor="#D9E8FF" width="100%"><tr>

<center>
<th align="left" bgcolor="#DFEFFF"><font face="MS Sans Serif" color="#000080" size="2">Date</font></th>
<th align="right" bgcolor="#DFEFFF"><font face="MS Sans Serif" color="#000080" size="2">Paid</font></th>
<th align="left" bgcolor="#DFEFFF"><font face="MS Sans Serif" color="#000080" size="2">Payment info</font></th>
<th align="right" bgcolor="#DFEFFF"><font face="MS Sans Serif" color="#000080" size="2">Receipt</font></th>
<th align="left" bgcolor="#DFEFFF"><font face="MS Sans Serif" color="#000080" size="2">Receipt info</font></th>

</tr>
<font color="#FFFFFF">

<?
$i=0;
while ($i < $num) {
$date=mysql_result($result,$i,"date");
$receipt=mysql_result($result,$i,"receipt");
$recptfor=mysql_result($result,$i,"recptfor");
$payment=mysql_result($result,$i,"payment");
$payfor=mysql_result($result,$i,"payfor");
$id=mysql_result($result,$i,"id");
?></font>

<tr>
<td bgcolor="#FFFFFF"><font face="MS Sans Serif" size="2"><? echo "$date";?></font></td>
<td align="right" bgcolor="#FFFFFF"><font face="MS Sans Serif" size="2"><? echo "$payment";?></font></td>
<td bgcolor="#FFFFFF"><font face="MS Sans Serif" size="2"><? echo "$payfor";?></font></td>
<td align="right" bgcolor="#FFFFFF"><font face="MS Sans Serif" size="2"><? echo "$receipt";?></font></td>
<td bgcolor="#FFFFFF"><font face="MS Sans Serif" size="2"><? echo "$recptfor";?></i></font></i></td>

</tr>
<?
++$i;
}
echo "</table></center>
</div>
";

if($num > 10)
echo "<br><b><center><font color=#3300CC><input type=button value=Back onclick=history.back()></font><br>";

?>

---------------

[Zipper]

add the following two lines after "$id=mysql_result($result,$i,"id");"
$t_payment += $payment;
$t_receipt += $receipt;

P.S: You should consider tweaking ur code a bit.

[edited by: Zipper at 4:25 pm (utc) on Sep. 15, 2004]

[Birdman]

Hello,

What you do is create two new variables to hold the totals, then add the values to it during the loop. See below..

<? 
$i=0; 
[b]$total_payments = 0;
$total_reciepts = 0;[/b]
while ($i < $num) { 
$date=mysql_result($result,$i,"date"); 
$receipt=mysql_result($result,$i,"receipt"); 
$recptfor=mysql_result($result,$i,"recptfor"); 
$payment=mysql_result($result,$i,"payment"); 
$payfor=mysql_result($result,$i,"payfor"); 
$id=mysql_result($result,$i,"id"); 
[b]$total_payments += $payment;
$total_reciepts += $receipt;[/b]
?>

Then you can print your totals in the last row of the table.

Birdman

[hugotobi]

Thanks. I pretty much figured this much out. but my problem still rermains, being new to php, what do I add in the script to make the totals print out at the end/last row of the table? sorry if I asked a silly question, but I am really a novice....

[hugotobi]

add the following two lines after "$id=mysql_result($result,$i,"id");"
$t_payment += $payment;
$t_receipt += $receipt;
P.S: You should consider tweaking ur code a bit.

[edited by: Zipper at 4:25 pm (utc) on Sep. 15, 2004]

yep. am sure my script is not optimum. but this is just a result of copying scripts from various places and making them work for what I need to achieve.... guess some of the script does need a tweak... let me which parts.... as this script does work for me and does what I require... except for the TOTAL at the end of the rows! for which i still need help.

[Zipper]

I hope u wouldn't mind copy-pasting one more time.
Put the following lines on top of echo "</table></center>

?>
<tr>
<td></td>
<td align="right"><?=$t_payment;?></td>
<td></td>
<td align="right"><?=$t_receipt;?></td>
<td></td>
</tr>
<?

talking abt tweaking, first of all you need to eliminate the repitition of mysql_result()

[hugotobi]

Thanks! it works great.

[hugotobi]

Zipper

one more question: the total is ok, but when the total is, say, 100.10 it shows up as 100.1 - the last "0" gets eliminated. how do I format the numerical total to retain 2 decimal places?

[Zipper]

replace 'em with,

<?=number_format($t_payment, 2, '.', '');?>
<?=number_format($t_receipt, 2, '.', '');?>