Problem outputting totals in while loop

[speckytwat2]

Hi, I'm having issues trying to add up the totals of each product type in a while loop- I'm sure it's straightforward to fix but for some reason I can't find a way to get it to add up all the totals of the various product types and list them so we have say 5 of product 1, 11 of product 2, 8 of product 3 and so on.

So firstly the user searches between two different dates / times to get the orders made during that time period: (I'm using separare date and time fields as the datetime-local input doesn't work in a lot of browsers)

if($_POST['search'])
{
    $searchdate1 = mysqli_real_escape_string($conn, $_POST['_Date1']);
    $searchdate2 = mysqli_real_escape_string($conn, $_POST['_Date2']);
    
    $searchtime1 = mysqli_real_escape_string($conn, $_POST['_Time1']);
    $searchtime2 = mysqli_real_escape_string($conn, $_POST['_Time2']);
    
    $datetime1 = $searchdate1 . ' ' . $searchtime1;
    $datetime2 = $searchdate2 . ' ' . $searchtime2;
    
    $nicedate1 = date("l F j, Y", strtotime($searchdate1) ); //Just used for readability in the report
    $nicedate2 = date("l F j, Y", strtotime($searchdate2) );

//Now get the orders from the db

$getorders = $oc_conn->query("SELECT * FROM oc_order WHERE date_added >= '$datetime1' AND date_added <= '$datetime2' AND order_status_id != 0 ORDER BY order_id DESC");

//Then generate start of a table in HTML, the while loop that follows will populate the rows

while ($row = $getorders->fetch_assoc()) { //Get the order data

          $firstname = $row["firstname"];
          $lastname = $row["lastname"];
          $fullname = $firstname . ' ' . $lastname;
        $orderid = $row["order_id"];
        $orderdate = $row["date_added"];    
          $email = $row["email"];
        $total = $row['total'];
          $total = number_format($total, 2);
            
        $niceorderdate = date('F j, Y H:i', strtotime($orderdate));    
            
             echo '<tr><td>'.$fullname.'</td><td>'. $orderid . '</td><td>';
    
            $getproducts = $oc_conn->query('SELECT * FROM oc_order_product WHERE order_id = '.$orderid); //Product names and details are held in a separate table
        
                while ($row = $getproducts->fetch_assoc()) {
                    
                    $orderproductid = $row["order_product_id"];
                    $productname = $row["name"];            
                    $quantity = $row["quantity"];
                    echo $quantity . ' x '.$productname.' <br>'; //This works for each individual order, but what I need is the total $quantity of each $productname, across ALL orders in the time range
                
                }
            echo '</td><td>'. $niceorderdate.'</td></tr>';           
        }
    echo '</tbody></table>';
}
?>

[/PHP]

As mentioned in the comment above, what I need is the total $quantity of each $productname, across ALL orders in the time range, so that I can see how many of each product ($productname) were ordered in total.

I tried using SUM for quantity in the mySQL, and COUNT for adding up the different product names but for some reason it didn't work. Can someone let me know what I need to do? Thanks :)

 

 

[requinix]

1 hour ago, speckytwat2 said:

I tried using SUM for quantity in the mySQL, and COUNT for adding up the different product names but for some reason it didn't work. Can someone let me know what I need to do?

What you need to do is go back to that SUM/COUNT thing you tried. Because that is, in fact, the right way to go here. Probably.

What was that query and what do you mean by "it didn't work"?

[Barand]

Don't run queries in side loops. Use a single query which joins the two tables.

Use prepared statements.

Use PDO, it's easier and better than mysqli.

You could use a second query to group/sum the product totals but as you are listing all the products anyway, you may as well total them into an array as you go.

<?php

    // assumes you have a PDO conection here

$tdata = '';
$totdata = '';

$date_from = $_POST['date1'] ?? date('Y-m-d', strtotime('first day of this month'));
$date_to   = $_POST['date2'] ?? date('Y-m-d');

$res = $db->prepare("SELECT o.order_id
                         , CONCAT(o.firstname, ' ', o.lastname) as full_name
                         , DATE_FORMAT(o.date_added, '%M %e, %Y') as date
                         , p.name as product
                         , p.quantity as qty
                    FROM oc_order o 
                         JOIN
                         oc_order_product p USING (order_id)
                    WHERE o.order_status_id <> 0
                          AND o.date_added BETWEEN ? AND ?
                    ORDER BY o.order_id, product
                    ");
$res->execute( [ $date_from, $date_to ] );

$prevorder = 0;
$totals = [];
foreach ($res as $r) {
    if ($r['order_id'] != $prevorder) {
        $tdata .= "<tr><td>{$r['full_name']}</td><td>{$r['date']}</td><td colspan='2'>&nbsp;</td></tr>";
        $prevorder = $r['order_id'];
    }
    $tdata .= "<tr><td colspan='2'>&nbsp;</td><td>{$r['product']}</td><td>{$r['qty']}</td></tr>";
    // accumulate totals by product
    if (isset($totals[$r['product']])) {
        $totals[$r['product']] += $r['qty'];
    }
    else {
        $totals[$r['product']] = $r['qty'];
    }
}                   

arsort($totals);
foreach ($totals as $p => $q) {
    $totdata .= "<tr><td>$p</td><td>$q</td></tr>";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Example</title>
<style type="text/css">
table {width: 50%; margin: 16px 50px; border-collapse: collapse; }
th    {background-color: #000; color: #FFF; padding: 8px;}
td    {padding: 4px 8px;}
</style>
</head>
<body>
    <h3>Orders</h3>
    <table>
        <tr><th>Customer</th><th>Date</th><th>Product</th><th>Quantity</th></tr>
        <?=$tdata?>
    </table>
    <h3>Product Totals</h3>
    <table>
        <tr><th>Product</th><th>Total Qty</th></tr>
        <?=$totdata?>
    </table>
</body>
</html>

Giving

 

Your data needs nomalizing. It should be something like this

+----------------+        +---------------+        +------------------+       +-----------------+
| customer       |        | oc_order      |        | oc_order_product |       | product         |
+----------------+        +---------------+        +------------------+       +-----------------+
|  cust_id       |----+   |  order_id     |----+   | order_product_id |   +---|  product_id     |
|  firstname     |    |   |  date_added   |    +---| order_id         |   |   |  name           |
|  lastname      |    +---|  cust_id      |        | product_id       |---+   |  price          |
|  email         |        |  status_id    |        | quantity         |       +-----------------+
+----------------+        +---------------+        +------------------+

 

[speckytwat2]

Thanks, is there a way to do the same thing using mysqli? I will look into PDO but need to fix this first- then I will go and learn PDO as soon as I can.

[Barand]

https://www.php.net/manual/en/mysqli.prepare.php