php - 重置买家购物车中每个供应商的总计，汇总每个供应商的总和

Question

问题是这样的:

每件商品的售价为 200.00 美元，因此 15 件商品的总价为 3,000 美元。

这些商品来自不同的供应商，因此我需要根据买家选择的数量获取每个供应商的总计。这是如何实现的？

代码按预期工作，直到我尝试获取每个供应商的 grand_totals。

<?php
$user_ref = '00007939789';

$grand_total = 0;
$sellers = array();

$fetch_sellers = mysqli_query($conn, "
SELECT s_c_vendor 
  FROM shopping_cart 
 WHERE s_c_user_ref='$user_ref' 
 GROUP 
    BY s_c_vendor
");

if (mysqli_num_rows($fetch_sellers) > 0) {
    while ($row = mysqli_fetch_assoc($fetch_sellers)) {
        array_push($sellers, $row['s_c_vendor']);
}

for ($i=0; $i < count($sellers); $i++) { 
    $get_products = mysqli_query($conn, "
SELECT s_c_product
     , s_c_qty 
  FROM shopping_cart 
 WHERE s_c_user_ref='$user_ref' 
   AND s_c_vendor = '$sellers[$i]' 
");
    if (mysqli_num_rows($get_products) > 0) {
        while ($row1 = mysqli_fetch_assoc($get_products)) {
            echo "Product Title: ";
            $product_ref = $row1['s_c_product'];
            echo $product_ref;
            echo " Qty: ";
            echo $row1['s_c_qty'];
            $qty = $row1['s_c_qty'];
            echo "<br>";

            $get_subtotal = mysqli_query($conn, "
SELECT L_PRICE price 
  FROM listings 
 WHERE L_REF = '$product_ref' 
");

            while ($row2 = mysqli_fetch_assoc($get_subtotal)) {
                $price = $row2['price'];
                $subtotal = $row2['price'] * $qty;
                echo "Price: ".number_format($price)." ";
                echo "Subtotal: ".number_format($subtotal)."<br><br>";
            }

            $get_grandtotal = mysqli_query($conn, "
SELECT SUM(L_PRICE) grand_total 
  FROM listings 
 WHERE v_ref = '$sellers[$i]' 
");
            $grand_total += mysqli_fetch_assoc($get_grandtotal)['grand_total'];

        }
        echo "<h2>Grand Total for seller ".$sellers[$i]." : ".$grand_total."</h2>";
    }
}

}

我的数据库中有多个表，如下图所示。

Answer 1

尝试将最后一个 SQL 查询更改为:

$get_grandtotal = mysqli_query($conn, "
SELECT SUM(L_PRICE) grand_total 
  FROM listings 
 WHERE v_ref = '$sellers[$i]' AND L_REF = '$product_ref'
");

在 for 循环打开的位置设置 $grand_total = 0;:

for ($i=0; $i < count($sellers); $i++) { 
        $grand_total = 0;
        $get_products = ....
        ...

并阅读我上面的评论。