...

View Full Version : Outputting summary of totals in PHP



galahad3
05-17-2011, 04:50 PM
Hi, I need to find a way of outputting the total (sum) values of three decimal fields in a mySQL table using PHP.

I thought this might be a good query string to use:



$query = "SELECT Nett, SUM(Nett), VAT, SUM(VAT), Gross, SUM(Gross) from Customers WHERE Name LIKE '$name'";


This will act on data from a previous page where the user selects the Customer from a dropdown- and this produces the $name variable.

Nett, VAT and Gross are the three fields. There are about 50 or more values for each, for example Nett values might be -123.54, -234.76 and so on. For each of these three fields, I want to be able to add them all up (for that specified Customer name) so we end up with the added-up values of all three fields in the table that fit the criteria (i.e which are for that Customer).

Assuming the query is ok (and it probably isn't), I need to find a way of getting PHP to do the work, and output a variable which equals the respective totals. What would be the best way of doing this?

Fou-Lu
05-17-2011, 05:27 PM
Using a sum from the DB will work fine in PHP as well, but you're limited to using either fetch_row and indexing it, or aliasing the fields so they make sense (different DBMS will kick out different display names for them, such as EXPR1 for example). The query will also need to be grouped in order to use the aggregates.


$sQry = "SELECT Nett, SUM(Nett) AS SumOfNett, VAT, SUM(VAT) AS SumOfVAT, Gross, SUM(Gross) AS SumOfGross
FROM Customers
WHERE Name LIKE '$name'
GROUP BY Nett, VAT, Gross";
if ($qry = @mysql_query($sQry))
{
while ($row = mysql_fetch_assoc($qry))
{
vprintf("Nett: %0.2f (%0.2f), VAT: %0.2f (%0.2f), Gross: %0.2f (%0.2f)\n", $row);
// Or extract each, such as $row['SumOfVAT']
}
}


Try that.

galahad3
05-17-2011, 05:53 PM
Unforunately I'm getting no results even for values I know should work, I';ve edited the code and I'm using this:



$sQry = "SELECT Nett, SUM(Nett) AS SumOfNett, VAT, SUM(VAT) AS SumOfVAT, Gross, SUM(Gross) AS SumOfGross FROM Customers WHERE Name LIKE '$name' GROUP BY Nett, VAT, Gross";
if ($qry = @mysql_query($sQry))
{
while ($row = mysql_fetch_assoc($qry))
{
//vprintf("Nett: %0.2f (%0.2f), VAT: %0.2f (%0.2f), Gross: %0.2f (%0.2f)\n", $row);
// Or extract each, such as $row['SumOfVAT']
$nett = $row['SumOfNett'];
$vat = $row['SumOfVAT'];
$gross = $row['SumOfGross'];
echo '<table><tr><td align="left" width="100">' . $nett . '</td><td align="left" width="100">' . $vat . '</td><td align="left" width="100">' . $gross . '</td></tr></table>' ;
}
}


Not sure why it's outputting nothing? It's getting the $name variable as I checked by echoing it earlier...

This is the whole script / page for reference:



<?php

if($_POST['paymentreport_supplier_all'])
{

include ('inc/dbconnect.php');

$name =@$_POST['_Name'];

echo '<p>Summary - Payments Total for ' . $name . '</p>';


$sQry = "SELECT Nett, SUM(Nett) AS SumOfNett, VAT, SUM(VAT) AS SumOfVAT, Gross, SUM(Gross) AS SumOfGross FROM Customers WHERE Name LIKE '$name' GROUP BY Nett, VAT, Gross";
if ($qry = mysql_query($sQry))
{
while ($row = mysql_fetch_assoc($qry))
{
//vprintf("Nett: %0.2f (%0.2f), VAT: %0.2f (%0.2f), Gross: %0.2f (%0.2f)\n", $row);
// Or extract each, such as $row['SumOfVAT']
$nett = $row['SumOfNett'];
$vat = $row['SumOfVAT'];
$gross = $row['SumOfGross'];
echo '<table><tr><td align="left" width="100">' . $nett . '</td><td align="left" width="100">' . $vat . '</td><td align="left" width="100">' . $gross . '</td></tr></table>' ;
}
}
}
?>

Fou-Lu
05-17-2011, 06:20 PM
Kill of the query to see if its just bad data:


if ($qry = mysql_query($sQry))
{
....
}
else
{
die('Error in query: ' . mysql_error());
}

galahad3
05-17-2011, 06:47 PM
Hmm, seem to have got somewhere, the query didn't generate an error but I actually changed the $name variable to allow wildcards - i.e turned it into
'%$name%' in the SELECT, and it now outputs numbers.

Only problem is, it seems to be outputting the actual values that need to be added up, rather than the sums (totals) for each field?

UPDATE: Sorted it, removed the Group By.

Just need to format it nicely now.

Thanks for the help on that. :)

Fou-Lu
05-17-2011, 10:37 PM
Hmm, seem to have got somewhere, the query didn't generate an error but I actually changed the $name variable to allow wildcards - i.e turned it into
'%$name%' in the SELECT, and it now outputs numbers.

Only problem is, it seems to be outputting the actual values that need to be added up, rather than the sums (totals) for each field?

UPDATE: Sorted it, removed the Group By.

Just need to format it nicely now.

Thanks for the help on that. :)

Both of those are bizarre, you shouldn't need to wildcard a LIKE if it make a match (so that would indicate that the $name didn't match a full value in a database), and I'd expect that the query would puke without a group by. Not sure how its managing that since it has both a standard column and an aggregate in there.
Maybe old pedant will see this and comment on it - he's much much much better with SQL than most people I know.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum