Check if record exists, update quantity if it does or insert new record otherwise

jeffcodedev123 · February 18, 2021

Hello I need some help please!

I have a web page that currently inserts a record containing customerID, productID and quantity into the cart table if a user adds a product to their cart. However, if the user goes back to the shop and re-adds that same product to their cart. The php/mysql creates a new record regardless of what they have in their cart.

Current output in table:

customerID productID quantity

1 ABC12 3

1 ABC12 1

Expected output:

customerID productID quantity

1 ABC12 4

All help is greatly appreciated.

<!doctype html>
<html>
<head>
<title>Add Item to Cart</title>

<?php
session_start();
echo $_SESSION["user_id"];

$user_id = $_SESSION["user_id"];

$stockID = $_POST['stockNo'];
$quantity = $_POST['quantity'];


//connect to database.
include 'dbConnect.php';

//create MySQL insert command
$query1 = "insert into cart (customerID,stockID,quantity) values('$user_id','$stockID','$quantity');
//execute the MySQL statement
$result1 = mysqli_query($db,$query1);
?>

</head>
<body>
<?php include 'navBar.php'; ?>
<h1>Adding to cart....</h1>
<br>
<?php

if ($result1){
echo "Item has been added to your cart.";
}
else {
echo "Theres been a problem. Item was not added to your cart.";
}
?>
</body>
<?php //close connection to the database
mysqli_close();
?>
</html>

Edited February 18, 2021 by jeffcodedev123
error

Barand · February 18, 2021

Define customer/product as UNIQUE...

CREATE TABLE `cart` (
  `cart_id` int(11) NOT NULL AUTO_INCREMENT,
  `customerID` int(11) DEFAULT NULL,
  `productID` varchar(5) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`cart_id`),
  UNIQUE KEY `UNQ_cart_product` (`customerID`,`productID`)
)

BEFORE

+---------+------------+-----------+----------+
| cart_id | customerID | productID | quantity |
+---------+------------+-----------+----------+
|       1 |          1 | ABC12     |        3 |
|       2 |          1 | DEF23     |        5 |
+---------+------------+-----------+----------+

UPDATE

INSERT INTO cart (customerID, productID, quantity)
VALUES 
(1, 'ABC12', 2),
(1, 'DEF23', 5),
(1, 'GHI34', 10)
ON DUPLICATE KEY UPDATE
    quantity = quantity + VALUES(quantity);

AFTER

+---------+------------+-----------+----------+
| cart_id | customerID | productID | quantity |
+---------+------------+-----------+----------+
|       1 |          1 | ABC12     |        5 |
|       2 |          1 | DEF23     |       10 |
|       3 |          1 | GHI34     |       10 |
+---------+------------+-----------+----------+

Edited February 18, 2021 by Barand

Sign In

Check if record exists, update quantity if it does or insert new record otherwise

Recommended Posts

jeffcodedev123

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information