Otherwise,
it updates the quantity of the item in the shopping cart and also updates added_on to accurately
reflect the time the record was last modified. Updating added_on is useful for the catalog administration
page, where this field is used to calculate the shopping cart age and remove old shopping carts. For this
purpose, we consider that an item whose quantity has been modified is a ???new item.???
4. Execute the following code, which creates the shopping_cart_remove_product stored procedure in
your tshirtshop database:
-- Create shopping_cart_remove_product stored procedure
CREATE PROCEDURE shopping_cart_remove_product(IN inItemId INT)
BEGIN
DELETE FROM shopping_cart WHERE item_id = inItemId;
END$$
The shopping_cart_remove_product stored procedure removes an item from the shopping cart when
a visitor clicks the Remove button for one of the items in the shopping cart.
CHAPTER 12 ?– CREATING YOUR OWN SHOPPING CART 369
5. Execute this code, which creates the shopping_cart_get_products stored procedure in your
tshirtshop database:
-- Create shopping_cart_get_products stored procedure
CREATE PROCEDURE shopping_cart_get_products(IN inCartId CHAR(32))
BEGIN
SELECT sc.item_id, p.name, sc.attributes,
COALESCE(NULLIF(p.discounted_price, 0), p.price) AS price,
sc.quantity,
COALESCE(NULLIF(p.discounted_price, 0),
p.price) * sc.quantity AS subtotal
FROM shopping_cart sc
INNER JOIN product p
ON sc.
Pages:
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479