Sum field using sql in php

Question

How do I get this code to show a price total for each customers order total amount for books? I tried using sum(

I have the following code:

<style type="text/css">
table{font-size:1.11em;}
tr{background-color:#eee; border-top:1px solid #333;}
</style>
<?php
$con = mysql_connect("localhost","root","pass");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("bookorama", $con);

$sql="SELECT customers.name, books.title, books.isbn, books.price 
         FROM customers, orders, order_items, books
         WHERE customers.customerID = orders.customerID 
         AND orders.orderID = order_items.orderID 
         AND order_items.isbn = books.isbn;";

$result = mysql_query($sql);     // You actually have to execute the $sql with mysql_query();
if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}
echo "<h1 style='color:#3366ff;'>Each customer's book orders</h1>";
echo "<table>";  //start the table

while($row = mysql_fetch_array($result, MYSQL_ASSOC))  //Loop through the results
{
    //echo each row of the table
    echo "<tr>";              
    echo "<th><strong>Customer Name:</strong><br></th>";               
    echo "<td>$row[name]</td>";       
    echo "<th><strong>Book Title</strong><br></th>";                
    echo "<td>$row[title]</td>";
    echo "<th><strong>ISBN</strong><br></th>";  
    echo "<td>$row[isbn]</td>";
    echo "<th><strong>Book Price</strong><br></th>";  
    echo "<td>$row[price]</td>";
    echo "</tr>";
} 

echo '</table>';   //close out the table

?>

Not the most elegant solution, but is there a reason not to assign a variable $priceSum and do $priceSum += $row[price]; in your while loop? — Farray
– Farray, Commented Mar 2, 2011 at 20:48
Please don't post a wall of irrelevant code - you're asking about how to write a query, so we don't need to see any of the PHP or HTML code. — Marc B
– Marc B, Commented Mar 2, 2011 at 20:48

Marc B · Accepted Answer · 2011-03-02 20:57:57Z

2

SELECT customers.name, SUM(books.price)
FROM customers, orders, order_items, books
WHERE customers.customerID = orders.customerID 
AND orders.orderID = order_items.orderID 
AND order_items.isbn = books.isbn
GROUP BY customers.customerID;

edited Mar 2, 2011 at 20:57

answered Mar 2, 2011 at 20:47

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon · Accepted Answer · 2011-03-02 20:51:20Z

0

What was your SUM() query? Something like:

    $sql="SELECT customers.name, books.title, books.isbn, SUM(books.price)           
FROM customers, orders, order_items, books          
WHERE customers.customerID = orders.customerID           
AND orders.orderID = order_items.orderID           
AND order_items.isbn = books.isbn GROUP BY customers.name";

answered Mar 2, 2011 at 20:51

Simon

9,42515 gold badges79 silver badges117 bronze badges

2 Comments

Jshee Over a year ago

Yeah, but that does not give me the desired output which should show total for each customer, not ALL customers orders combined.

Simon Over a year ago

the GROUP BY customers.name clause should do exactly what you ask: total for each customer. Marc B beat me to it - see his answer. The books.title and books.isbn are irrelevant in the output.

Collectives™ on Stack Overflow

Sum field using sql in php

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related