How can I display all the individual fields that led to the calculated total in MySQL?

Question

I would like to have some help with my problem, I am displaying total sales gained within a time period, which i have done successfully and what i need now is how to display all the individual prices that leads to the total. for example total is 10, i bought apple for 4, and oranges for 6, 10 is the total price.

I have here my query for displaying it in a time period using input type date,

$datefrom   = $_GET['datefrom'];
$dateto     = $_GET['dateto'];
$qry = "SELECT sum(order_detail.quantity*order_detail.price) as chenes, orders.date
        FROM order_detail 
        LEFT JOIN orders ON order_detail.orderid=orders.serial
        WHERE date(orders.date) BETWEEN '$datefrom' AND '$dateto'";

mysql_set_charset("UTF8");
$result = @mysql_query($qry);

while ($row=mysql_fetch_array($result)){
    echo "Sales from ".date("F d Y",strtotime($datefrom))." ";
    echo "to ".date("F d Y",strtotime($dateto))." are a total of ";
    echo "₱".number_format($row['chenes'],2);
}

and here is my code for displaying the individual prices for each orderid.

$AAA = "SELECT order_detail.quantity,order_detail.price,order_detail.orderid, orders.date
        FROM order_detail 
        LEFT JOIN orders ON order_detail.orderid=orders.serial
        WHERE orderid=3";
$BBB=@mysql_query($AAA);
while($CCC=mysql_fetch_array($BBB)){
    echo "<br>".$CCC['price']."<br>";
}

It works but so far i have no idea how to automatically display it without having the orderid.

Please help me display the price for each, because so far i have only displayed the total, and what i need now is to display the total and it's break down. i've tried the query above and so far i am stuck. please help . thank you.

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Oct 07 '14 at 13:41
Do you have multiple entries for single orderid in order_detail table? — K.D, Oct 07 '14 at 13:53
Why don't you bring back order_detail.orderid in your first query so that you can use that in your second query? Or.. use the date range in the second query as well as the first. — JNevill, Oct 07 '14 at 13:53
@K.D Yes i do, orderid's will display the individual orders, i have multiple orderids which is per order. for example orderid=1 has 1 apple 1 banana and 1 orange, and so on — geds13, Oct 07 '14 at 14:01
Do you want to show detail by the day? The product_name column (apples, oranges) is also not returned in your query, yet that is what you want in your result. — Chris Barlow, Oct 07 '14 at 14:02
No need to display the name as of now, just the breakdown of the total price. the prices for each order that leads to the sum. @ChrisBarlow — geds13, Oct 07 '14 at 14:03
You are vulnerable to [sql injection attacks](http://bobby-tables.com), are simply assuming that queries can never fail, and are using the `@` error suppression operator. In other words, your code is going to bite you in the rump, but you'll never notice, because you're doing the programmatic equivalent of stuffing your fingers in your ears and going "lalalalala can't hear you" — Marc B, Oct 07 '14 at 14:07

score 2 · Answer 1 · edited Oct 07 '14 at 14:10

2

By using this query you will get comma separated quantity and price in different two columns foreach orderid.

SELECT GROUP_CONCAT(IFNULL(od.quantity,0)) AS quantity,
       GROUP_CONCAT(IFNULL(od.price,0)) AS price,
       od.orderid,o.date
FROM orders o
    LEFT JOIN order_detail od ON od.orderid=o.serial
GROUP BY o.serial;

edited Oct 07 '14 at 14:10

RiggsFolly

93,638
21
103
149

answered Oct 07 '14 at 14:07

K.D

407
3
10

score 0 · Accepted Answer · answered Oct 07 '14 at 15:34

i fixed you code.

$qry = "SELECT sum(order_detail.quantity*order_detail.price) as chenes, orders.date, order_detail.price
        FROM order_detail 
        LEFT JOIN orders 
        ON order_detail.orderid=orders.serial
        WHERE date(orders.date) BETWEEN '$datefrom' AND '$dateto'";
        $result = @mysql_query($qry);

        while ($row=mysql_fetch_array($result)){
        echo "Sales from ".date("F d Y",strtotime($datefrom))." ";
        echo "to ".date("F d Y",strtotime($dateto))." are a total of ";
        echo "₱".number_format($row['chenes'],2);

            $AAA = "SELECT order_detail.quantity*order_detail.price as xax,order_detail.price,order_detail.orderid, orders.date
                FROM order_detail 
                LEFT JOIN orders 
                ON order_detail.orderid=orders.serial
                WHERE date(orders.date) BETWEEN '$datefrom' AND '$dateto'";
            $BBB=@mysql_query($AAA);
            while($CCC=mysql_fetch_array($BBB)){
                echo "<br>".$CCC['xax']."<br>";
            }

        }

Based on the comments i've read, here is what i did. Created a new query, baased on your post and added your WHERE Clause.

How can I display all the individual fields that led to the calculated total in MySQL?

2 Answers2