PHP MYSQL INNER JOIN 循环再循环

Question

我在 PHP MYSQL INNER JOIN 上循环和循环(再次)有问题

所以，这是我的代码:

这里有两个表product和stock

它们中的每一个都包含一个具有关系的行 = id_product

id_product // this is product table
-----------
     1
     2
     3

id_product |  stock  // this is stock table
---------------------
     1     |    2
     2     |    2
     3     |    2

$stockSum = 0;
$tommorow = mysqli_query($con, 
           "SELECT product.id_product,
                    stock.id_product,
                    stock.stock AS allstock
            FROM product INNER JOIN stock
            ON stock.id_product = product.id_product
            WHERE stock.id_product = product.id_product");

while ($dataTommorow = mysqli_fetch_assoc($tommorow)) {

    $stockSum += $dataTommorow['allstock'];

    <li><?php echo $stockSum; ?> Stocks</li> // output = 2
    <li><?php echo $stockSum; ?> Stocks</li> // problem occure it's stacking -> output = 4
    <li><?php echo $stockSum; ?> Stocks</li> // (again) now become -> output = 6 
}

所以，我期望的是:

<li><?php echo $stockSum; ?> Stocks</li> // output = 2
<li><?php echo $stockSum; ?> Stocks</li> // output = 2
<li><?php echo $stockSum; ?> Stocks</li> // output = 2

我的代码有什么问题？

提前致谢

Answer 1

$stockSum = 0; // You are casting to an integer instead cast to an array
$stockSumArray = array(); // Do this instead

while($dataTomorrow = mysqli_fetch_assoc($tomorrow)) {

    // You are appending data to this, so if it returned 2 on the next return 
    // it will add what ever integer was returned to the previous data that 
    // was returned. If it returns a string it will try to cast the string to 
    // an integer and add it together / add on to the existing string
    $stockSum += $dataTomorrow['allstock'];


    // !~~~ Instead Do This ~~~!

    // So instead of appending / adding the returned data together lets
    // push it to an array so we can later loop through it.
    array_push($stockSumArray, $dataTomorrow['allstock'];
}

for($i = 0; $i < count($stockSumArray); $i++) {

   // We are going to do exactly what you did before but just echo it out 
   // while looping through the array

   <li><?php echo $stockSumArray[$i]; ?> Stocks</li>
}

上面的代码可能会让您变得更加复杂和疯狂，但与您在上面所做的相比，这是一个更加优雅的解决方案。希望这会有所帮助，尽量避免在具有相同数据集的同一个表上运行多个 JOINS，您只需运行一次即可获取所需的数据。更少的调用，等于更快的页面加载，反过来等于更高性能的代码，这反过来等于提高代码的可测试性。