Exercise: Implementing the Stored Procedures
1. Use phpMyAdmin to create the stored procedures described in the following steps. Don??™t forget to set the
$$ delimiter before executing the code of each step.
2. Create the shopping_cart_add_product stored procedure in your tshirtshop database by executing
this code:
-- Create shopping_cart_add_product stored procedure
CREATE PROCEDURE shopping_cart_add_product(IN inCartId CHAR(32),
IN inProductId INT, IN inAttributes VARCHAR(1000))
BEGIN
DECLARE productQuantity INT;
-- Obtain current shopping cart quantity for the product
SELECT quantity
FROM shopping_cart
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes
INTO productQuantity;
-- Create new shopping cart record, or increase quantity of existing record
IF productQuantity IS NULL THEN
INSERT INTO shopping_cart(cart_id, product_id, attributes,
quantity, added_on)
VALUES (inCartId, inProductId, inAttributes, 1, NOW());
ELSE
UPDATE shopping_cart
SET quantity = quantity + 1, buy_now = true
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes;
END IF;
END$$
CHAPTER 12 ?– CREATING YOUR OWN SHOPPING CART 368
The shopping_cart_add_product stored procedure is called when the visitor clicks the Add to Cart
button for one of the products. If the selected product already exists in the shopping cart, its quantity is
increased by one; otherwise, one new unit is added to the shopping cart (a new shopping_cart record is
created).
Pages:
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477