add_to_cart procedure

[dmx1]

Hi Larry,

 

Regarding the stored procedure on page 231, which is below

DELIMITER $$
CREATE PROCEDURE add_to_cart (uid CHAR(32), usr_id INT(6), type VARCHAR(12), pid MEDIUMINT, qty TINYINT)
BEGIN
DECLARE cid INT;
SELECT id INTO cid FROM carts WHERE user_session_id=uid AND product_type=type AND product_id=pid;
IF cid > 0 THEN
UPDATE carts SET quantity=quantity+qty, date_modified=NOW() WHERE id=cid;
ELSE
INSERT INTO carts (user_session_id, users_id, product_type, product_id, quantity) VALUES (uid, usr_id, type, pid, qty);
END IF;
END$$
DELIMITER ;

 

 

I am trying to modify the original 'add_to_cart' procedure by adding the parameters 'usr_id' and 'em' parameters, which represent the user id and email from the users table.

In the carts table, I put these values as DEFAULT NULL.

Every time I press the 'add to cart' button, no items are added to the shopping cart. The message that is shown is 'The Shopping Cart is Empty'

I have tried to figure out why no items are being added to the shopping cart. I know the problem is with the 'add_to_cart' procedure that I have modified, because when I add the original procedure, items are added to the shopping cart, which is what I want. But for some reason, my modified version is not working.

I have only added an extra two parameters, but this somehow, stops items from being added to the shopping cart.

Can you let me know what I am doing wrong please.

My modified version of the 'add_to_cart procedure is below.

Thanks

 

 

DELIMITER $$
CREATE PROCEDURE add_to_cart (uid CHAR(32), usr_id INT, em VARCHAR(80), type VARCHAR(12), pid MEDIUMINT, qty TINYINT)
BEGIN
DECLARE cid INT;
SELECT id INTO cid FROM carts WHERE user_session_id=uid  AND product_type=type AND product_id=pid;
IF cid > 0 THEN
UPDATE carts SET quantity=quantity+qty, date_modified=NOW() WHERE id=cid;
ELSE
INSERT INTO carts (user_session_id, users_id, email, product_type, product_id, quantity) VALUES (uid, usr_id, em, type, pid, qty);
END IF;
END$$
DELIMITER ;

 

[dmx1]

Hi Larry,

 

I have solved the problem now!

[Larry]

That's fantastic! Kudos! For the benefit of others, would you kindly share what the problem and solution were? Thanks!

[dmx1]

No items were being added to the cart for some strange reason. The structure looked fine, so I didn't understand why no items were being added.

I put the default value of the user_id in my cart table, which is an int, as NULL. This is not allowed.

The default value should be '0'.

After I adjusted it, it worked fine!!

[Larry]

Excellent! Thanks for sharing!