When I click the 'Add Items to Cart' with the values on the image above, echoing my SQL query would give me this:
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;
By manually inserting this query via phpMyAdmin. It is working fine, it inserts all the three queries but it gives me
1 Row affected
Now, the problem on my website is when I click 'Add Items to Cart', it only inserts the first row with a quantity.
So the result would give me this (It only added the first row: Coca-Cola2 with a value of 10):
This is my Add to Cart code:
<?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;
}
}
}
?>
And this is my table of products
<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>
What is the problem here? And how do I insert all the three queries on my page?
EDIT: Customer's Cart Code
<!-- 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 -->
EDIT 2: Problem solved. The problem was the mysqli_multi_query so I seperated the "UPDATE products SET quantity = quantity - $value WHERE product_id = $cartProd_id;"; as another query.
$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);
I'll appreciate an explanation on why mysqli_multi_query doesn't work.
Customers Cartcode, I suspect it's in there.Customers Cartcode