Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Posts
    184
    Thanks
    19
    Thanked 0 Times in 0 Posts

    Outputting summary of totals in PHP

    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:

    Code:
    $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?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.
    PHP Code:
    $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.

  • Users who have thanked Fou-Lu for this post:

    galahad3 (05-17-2011)

  • #3
    Regular Coder
    Join Date
    Sep 2009
    Posts
    184
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Unforunately I'm getting no results even for values I know should work, I';ve edited the code and I'm using this:

    Code:
    $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:

    Code:
    <?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>' ;
        } 
    }  
    }
    ?>

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Kill of the query to see if its just bad data:
    PHP Code:
    if ($qry mysql_query($sQry)) 
    {
        ....
    }
    else
    {
        die(
    'Error in query: ' mysql_error());


  • #5
    Regular Coder
    Join Date
    Sep 2009
    Posts
    184
    Thanks
    19
    Thanked 0 Times in 0 Posts
    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.
    Last edited by galahad3; 05-17-2011 at 07:58 PM.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by galahad3 View Post
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •