Announcement

Collapse
No announcement yet.

SUM (total) for each customer.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUM (total) for each customer.

    I have the following code that draws the total amount spent by a customer which works nicely and the customerID is defined by the page.

    PHP Code:
    $query "SELECT SUM(total) as yeartotal FROM invoices, jobs, customers
    WHERE invoices.jobID = jobs.jobID
    AND jobs.customerID = customers.customerID
    AND customers.customerID = 
    $selectedcustID";

    $result mysql_query($query$connection) or die(mysql_error());

    $gettotal mysql_fetch_array($result);
    $total =  ($gettotal['yeartotal'] /1.2);
    $dectotal number_format($total,2); 

    Now I would like to create another page where it lists all the customers and shows their total spends ordered by the highest to the lowest, the problem I have is that I don't know how to define the customerID for each customer. I've tried a for loop inside a for loop but it doesn't seem to work.

    I would be grateful of any advice.

    Tom
    Tom

    My questions might not make any sense but at least they are interesting.

  • #2
    You don't care about the customerID as that will come from the database.
    PHP Code:
    $query "SELECT SUM(total) as yeartotal, customers.customerID FROM invoices, jobs, customers
    WHERE invoices.jobID = jobs.jobID
    AND jobs.customerID = customers.customerID"
    order by customers.customerID ASC;

    $result mysql_query($query$connection) or die(mysql_error());

    while(
    $gettotal mysql_fetch_array($result)){
    $total =  ($gettotal['yeartotal'] /1.2);
    $dectotal number_format($total,2); 
    echo 
    "Customer ".$gettotal['customerID']." had a total of $dectotal <br>";

    Not sure how you want to display it, but that should list all customers
    Have a Script or Snippet you want to share?

    WWW Standards: HTML 4.01,
    HTML 5, CSS2.1, CSS3, XHTML 1.0
    PHP Standards: PHP Standards

    Comment


    • #3
      Hi Scoutt

      Thanks for your reply, it spat this out at me

      Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
      Tom

      My questions might not make any sense but at least they are interesting.

      Comment


      • #4
        Add a GROUP BY clause that makes sense for the query, at the end (after customers.customerID) - GROUP BY customers.customerID is probably what you're looking for.
        DarksidePuffin
        "It's the bucket o' nothing, for only 99,99,99!"

        Comment


        • #5
          Thanks that's great, final code looks like this:

          PHP Code:
          <?php

          echo "<table class='jobtable'>";


          $query "SELECT *, SUM(total) as yeartotal, customers.customerID FROM invoices, jobs, customers
          WHERE invoices.jobID = jobs.jobID
          AND jobs.customerID = customers.customerID
          GROUP BY customers.customerID
          order by yeartotal DESC"
          ;

          $result mysql_query($query$connection) or die(mysql_error());



          while(
          $gettotal mysql_fetch_array($result)){
          $total =  ($gettotal['yeartotal'] /1.2);
          $dectotal number_format($total,2);
          $customerName $gettotal['customerName'];
          $customerID $gettotal['customerID'];



          echo 
          "<tr bgcolor='$bgcolor'><td><a href='index.php?page=custdisp&custid=$customerID'>$customerName</a></td><td>$dectotal</td></tr>";
          }

          echo 
          "</table>";

          ?>

          Which works great but can this be done with a for loop as I love using modulus for highlighting every other row on tables with lots of entries and this doesn't work as far as I can tell with while loops. I am actually going to use this code but am just curious for future.
          Tom

          My questions might not make any sense but at least they are interesting.

          Comment


          • #6
            mysql_fetch_array can theoretically be used with a for loop (the function works by advancing an internal pointer for the mysql result, so it's based on calls, not any specific control structure - if you called, e.g, mysql_fetch_array($result) twice, you'd get the first row, then the second row...regardless of whether you're looping it). You could, for example, do a for loop up to mysql_num_rows...but it's an unnecessarily painful syntax to use just for zebra striping (I can't think of a real legit reason why you'd ever need a for loop for mysql_fetch_array, unless you wanted to arbitrarily skip rows, which can still be done with the next method).

            One of the standard solutions to this is just to use an accumulator, e.g:

            PHP Code:

            $count 
            0;
            while(
            $row mysql_fetch_array($result))
            {
            if((
            $count 2) == 0)
            {
            echo 
            'Even<br/>';
            }
            else
            {
            echo 
            'Odd<br/>';
            }
            $count++;

            You could also just alternate a boolean value, 0/1, etc. but a count also gives you the result of mysql_num_rows without calling the additional function, lets you output a header every X rows, etc.
            DarksidePuffin
            "It's the bucket o' nothing, for only 99,99,99!"

            Comment

            Working...
            X