PHP 只添加第一行输入，而 MySQL 工作正常？

Question

当我使用上图中的值单击“将项目添加到购物车”时，回显我的 SQL 查询会得到以下信息:

INSERT INTO cart (product_id, quantityCart) VALUES (1, 10) ON DUPLICATE KEY UPDATE quantityCart = quantityCart + 10;
UPDATE products SET quantity = quantity - 10 WHERE product_id = 1;

INSERT INTO cart (product_id, quantityCart) VALUES (2, 15) ON DUPLICATE KEY UPDATE quantityCart = quantityCart + 15;
UPDATE products SET quantity = quantity - 15 WHERE product_id = 2;

INSERT INTO cart (product_id, quantityCart) VALUES (3, 20) ON DUPLICATE KEY UPDATE quantityCart = quantityCart + 20;
UPDATE products SET quantity = quantity - 20 WHERE product_id = 3;

通过 phpMyAdmin 手动插入此查询。它工作正常，它插入了所有三个查询，但它给了我

1 Row affected

现在，我网站上的问题是当我点击“将商品添加到购物车”时，它只插入第一行的数量。

所以结果会给我这个(它只添加了第一行:Coca-Cola2，值为 10):

这是我的添加到购物车代码:

<?php
   if (isset($_POST['addCart']) && $_POST['addCart']=="Add Items to Cart") {
     foreach($_POST['qtyBuy'] as $index=>$value){
       if($value > 0){
        $cartProd_id = $_POST['product_id'][$index];

           $addQuery = "INSERT INTO cart (product_id, quantityCart) 
                        VALUES ($cartProd_id, $value)
                        ON DUPLICATE KEY UPDATE quantityCart = quantityCart + $value;";
           $addQuery .= "UPDATE products SET quantity = quantity - $value WHERE product_id = $cartProd_id;";

           $execQuery = mysqli_multi_query($connection, $addQuery);

           echo $addQuery;
        }  
     }

  }
?>

这是我的产品表

<form action="add_sales.php" method="POST">
            <table class="table table-striped table-bordered table-hover results table-fixed table-condensed">
              <thead>
                <tr>
                    <th class="text-center">#</th>
                    <th>Product Name</th>
                    <th>Description</th>
                    <th>Price</th>
                    <th>In Stock</th>
                    <th style="width: 20%">Quantity</th>
                </tr>
                <tr class="warning no-result">
                  <td colspan="8"><i class="fa fa-warning"></i> No Product Found</td>
                </tr>
              </thead>


              <tbody>
              <?php 
                $query = "SELECT * FROM products;";
                $exec = mysqli_query($connection, $query);
                $a = 1;
                $b = 1;

                while ($row = mysqli_fetch_array($exec)) {
                  $product_id = $row['product_id'];
                  $product_name = $row['product_name'];
                  $product_price = $row['sell_price'];
                  $description = $row['description'];
                  $product_quantity = $row['quantity'];

               ?>
              <tr>
              <td class="text-center"><?php echo $product_id; ?>
                <input type="hidden" name="product_id[]" value="<?php echo $product_id; ?>">
              </td>
                  <td><?php echo $product_name; ?></td>
                  <td><?php echo $description; ?></td>
                  <td><?php echo $product_price; ?></td>
                  <td><input type="number" value="<?php echo $product_quantity; ?>" id="<?php echo "qtyResult" . $a++; ?>" disabled></td>
                  <td><input type="number" name="qtyBuy[]" id="<?php echo "qtyBuy" . $b++; ?>" onkeyup="updateStock(this, event)"></td>
              </tr>
              <?php } ?>
              </tbody>
          </table>


          </div>
          <div class="form-group">
              <input type="submit" name="addCart" value="Add Items to Cart" class="btn btn-info pull-right">

          </div>
          </form>

这里有什么问题？以及如何在我的页面上插入所有三个查询？

编辑:客户的购物车代码

<!-- Start of Customer's Cart -->
    <div class="col-md-12"> 
      <div class="panel panel-default">
        <div class="panel-heading">
          <strong>
            <span class="fa fa-shopping-cart"></span>
            <span>Customer's Cart</span>
          </strong>
        </div>
      <div class="panel-body">
        <table class="table table-hover">
          <thead>
            <tr>
              <th class="text-center">Product ID</th>
              <th class="text-center">Product Name</th>
              <th class="text-center">Description</th>
              <th class="text-center">Quantity</th>
              <th class="text-center">Price per Unit</th>
              <th class="text-center">Total Amount</th>
              <th class="text-center">Remove</th>
            </tr>
          </thead>
          <tbody>


          <?php 

            $selectCart = "SELECT * FROM cart INNER JOIN products ON products.product_id = cart.product_id";
            $execSelectCart = mysqli_query($connection, $selectCart);

            while ($row = mysqli_fetch_array($execSelectCart)) {

              $cartProId = $row['product_id'];
              $cartProName = $row['product_name'];
              $cartProDesc = $row['description'];
              $cartSellPrice = $row['sell_price'];
              $cartQty = $row['quantityCart'];

              $compute = $cartSellPrice * $cartQty;
              $totalAmount = number_format((float)$compute, 2, '.', '');
           ?>

            <tr>
              <td class="text-center"><?php echo $cartProId; ?></td>
              <td class="text-center"><?php echo $cartProName; ?></td>
              <td class="text-center"><?php echo $cartProDesc; ?></td>
              <td class="text-center"><?php echo $cartQty; ?></td>
              <td class="text-center"><?php echo $cartSellPrice; ?></td>
              <td class="text-center"><?php echo $totalAmount ?></td>
              <td class="text-center">
                <div class="btn-group">
                  <a href="add_sales.php?remove=<?php echo $cartProId; ?>"  class="btn btn-xs btn-danger" data-toggle="tooltip" title="Remove">
                    <span class="glyphicon glyphicon-trash"></span>
                  </a>
                </div>
              </td>
            </tr>

          <?php } ?>
          </tbody>
        </table>
       </div>

      </div>
          <div class="form-group">
            <a href="checkout.php" class="btn btn-success pull-right">Checkout</a>

          </div>
    </div> 

    <!-- End of Customer Cart -->

编辑 2:问题已解决。问题是 mysqli_multi_query 所以我将 "UPDATE products SET quantity = quantity - $value WHERE product_id = $cartProd_id;"; 分开作为另一个查询。

   $addQuery = "INSERT INTO cart (product_id, quantityCart) 
                VALUES ($cartProd_id, $value)
                ON DUPLICATE KEY UPDATE quantityCart = quantityCart + $value;";
   $addQuery2 = "UPDATE products SET quantity = quantity - $value WHERE product_id = $cartProd_id;";

   $execQuery = mysqli_query($connection, $addQuery);
   $execQuery2 = mysqli_query($connection, $addQuery2);

如果您能解释为什么 mysqli_multi_query 不起作用，我将不胜感激。

Answer 1

您要将一件或多件商品添加到购物车，因此请检查 $values!==[] 并执行。