speckytwat2 Posted May 7, 2020 Share Posted May 7, 2020 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 :) Quote Link to comment Share on other sites More sharing options...
requinix Posted May 7, 2020 Share Posted May 7, 2020 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"? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 7, 2020 Share Posted May 7, 2020 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'> </td></tr>"; $prevorder = $r['order_id']; } $tdata .= "<tr><td colspan='2'> </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 | +-----------------+ +----------------+ +---------------+ +------------------+ Quote Link to comment Share on other sites More sharing options...
speckytwat2 Posted May 7, 2020 Author Share Posted May 7, 2020 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 7, 2020 Share Posted May 7, 2020 https://www.php.net/manual/en/mysqli.prepare.php Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.