adding values together from multiple results

blackwidow

Hi all,

I've been struggling with this problem for hours!!

I have a script that returns a set of results from a 'transaction' database table. One field is called 'amount'. What I would like to do is be able to mathmatically add the values from the results together and output a 'Grand Total' result.

here is the results page:

<?php require ("database.php"); ?>

<?php

if($logged_in){

$tid = $_POST['tid'];
$purl = $_POST['url'];


?>
<table width="90%" border="0" class="column_right">
  <tr>
    <td class="faq">Date</td>
    <td class="faq">Amount</td>
    <td class="faq">Transaction ID</td>
    <td class="faq">Senders Email</td>	
  </tr>
  <tr>
    <td colspan="5" class="faq"><hr size="1" /></td>
    </tr>

<?php

$purl = $_POST['url'];
$txns=@mysql_query("SELECT id, url, date, amount, txnid, sendersemail FROM transaction WHERE url='$purl'") or die(mysql_error()); 
if (!$txns) {
echo "Unable to perform search";
}

while ($txn=mysql_fetch_array($txns)) {
echo "<tr>";
$tid = $txn['id'];
$url = $txn['url'];
$date = $txn['date'];
$amount = $txn['amount'];
$txnid = $txn['txnid'];
$sendersemail = $txn['sendersemail'];
$total = $amount;
echo "<td class=\"box_text\">$date</td>
    <td class=\"box_text\">$amount</td>
    <td class=\"box_text\">$txnid</td>
    <td class=\"box_text\">$sendersemail</td>
  </tr>";
  }
?>

</table>

<p><a href="index.php">New Search</a></p>


<?php

}

else{
   displayLogin();
}
?>

Any help greatly appreciated!

Mark

superwormy

You can do it in PHP:
Just loop through the result set, adding $amount to $total on every loop.

You can do it in SQL:
SELECT SUM(amount) AS grand_total FROM transaction WHERE url = '$purl'

blackwidow

Hi Kieth,

Many thanks for the advice.

There were some new SQL words in there that I hadnt heard of, so decided to use that method and learn a couple of new things along the way.

The resulting code which worked like a dream is:

<?php
$gt = @mysql_query("SELECT SUM(amount) AS grand_total FROM transaction WHERE url = '$purl'");
while ($t=mysql_fetch_array($gt)) {
$total = $t['grand_total'];
echo "<table width=\"90%\" class=\"column_right\">";
echo "<tr><td colspan=\"5\" class=\"box_text\" align=\"right\">Total amount paid:  <strong>£$total</strong></td></tr>";
echo "</table>";
}
?>

Once again, many thanks.

Mark

laserlight

$result = @mysql_query("SELECT SUM(amount) AS grand_total FROM transaction WHERE url = '$purl'");
$total = mysql_result($result, 0);
echo "<table width=\"90%\" class=\"column_right\">";
echo "<tr><td colspan=\"5\" class=\"box_text\" align=\"right\">Total amount paid:  <strong>£$total</strong></td></tr>";
echo "</table>";