Odie Odie - 3 months ago 8
Javascript Question

Merge two rows if ID is the same in MySQL

How to combine the product's quantity into a single row if the Product ID is the same? This is for my customer's cart. When I add to cart a product, I want it to combine into a single row instead of a seperate row.

EDIT: I have two tables which contains a seperate quantity field (tblproducts, tblcart).

EDIT2: PHP is giving me an error


mysqli_fetch_array() expects parameter 1 to be mysqli_result boolean
given on my line 61


<?php
$query = "SELECT * FROM products";
$exec = mysqli_query($connection, $query);
$a = 1;
$b = 1;
while ($row = mysqli_fetch_array($exec)) {


but my code works just fine. Can anyone find the problem?

Code for my customer's cart

<table class="table table-hover">
<thead>
<tr>
<th>Product ID</th>
<th>Product Name</th>
<th>Description</th>
<th>Quantity</th>
<th>Price per Unit</th>
<th>Total Amount</th>
<th>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><?php echo $cartProId; ?></td>
<td><?php echo $cartProName; ?></td>
<td><?php echo $cartProDesc; ?></td>
<td><?php echo $cartQty; ?></td>
<td><?php echo $cartSellPrice; ?></td>
<td><?php echo $totalAmount ?></td>
<td class="text-center">
<div class="btn-group">
<a href="edit_brand.php?brand_id=<?php echo $brand_id; ?>" class="btn btn-xs btn-info" data-toggle="tooltip" title="Edit">
<span class="glyphicon glyphicon-edit"></span>
</a>
<a href="manage_brands.php?delete=<?php echo $brand_id; ?>" class="btn btn-xs btn-danger" data-toggle="tooltip" title="Remove">
<span class="glyphicon glyphicon-trash"></span>
</a>
</div>
</td>
</tr>

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


Code for adding a product

<?php
if (isset($_POST['addCart']) && $_POST['addCart']=="Add Items to Cart") {
foreach($_POST['qtyBuy'] as $index=>$value){
if($value > 0){
$cartID = $_POST['product_id'][$index];
$addQuery = "INSERT INTO cart (product_id, quantityCart) VALUES (".$_POST['product_id'][$index].", ".$value.");";
$addQuery .= "UPDATE products SET quantity = quantity - $value WHERE product_id = $cartID;";
$execQuery = mysqli_multi_query($connection, $addQuery);
}
}
header('Refresh: 0; url=add_sales.php');
}
?>

<div class="row">
<div class="col-md-12"> <!-- Product List Info Start -->
<div class="panel panel-default">
<div class="panel-heading">
<strong>
<span class="glyphicon glyphicon-th"></span>
<span>Select Products</span>
</strong>
</div>

<div class="panel-body">
<div class="form-group pull-left">
<input type="text" class="search form-control" placeholder="Search products">
</div>
<span class="counter pull-left"></span>
<div class="span3">

<form action="add_sales.php" method="POST">
<table class="table table-striped table-bordered table-hover results table-fixed">
<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" name="hehe" 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>


Table's image

enter image description here

Answer

If I understand, your basic problem is that you allow the database to store the same product ID multiple times under the same cart in the cart table. My solution relies on the presence of a field that has the same value for all items in the same cart. I use cart_id but it could be something else.

Since you want a product to appear only once in the same cart, you should do two things:

1. Add a UNIQUE INDEX(cart_id, product_id) the cart table. Once you've done this any attempt to add the same product to the same cart will fail with an error. Run this query just once:

ALTER TABLE `cart` ADD UNIQUE INDEX (cart_id, product_id);

2. Change the query you use to add items to cart. As I pointed out above, a regular INSERT will fail if the product is already in that cart. Instead, use INSERT...ON DUPLICATE KEY UPDATE... query. This query will insert the product id if it's not found. However if the cart already has that product, the query will update the existing record.

Change:

INSERT INTO cart (product_id, quantityCart) 
    VALUES (".$_POST['product_id'][$index].", ".$value.")

To:

INSERT INTO cart (cart_id, product_id, quantityCart) 
    VALUES ($cartID, {$_POST['product_id'][$index]}, $value)
ON DUPLICATE KEY UPDATE quantityCart = quantityCart + $value

I used cart_id in my solution as the way to determine all items that belong to the same cart. If you have another field that does this (eg: user_id), use that instead of cart_id. The basic solution is the same.