Jump to content

Problem outputting totals in while loop


speckytwat2

Recommended Posts

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 :)

 

 

Link to comment
Share on other sites

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"?

Link to comment
Share on other sites

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

image.thumb.png.0eb7d53067a9ecc9ef98dccf75bc20f2.png

 

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         |       +-----------------+
+----------------+        +---------------+        +------------------+

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.