Use phpMyAdmin to execute and create the following stored procedures. Don??™t forget to set $$ as the delimiter
before executing the code.
-- Create catalog_get_category_products stored procedure
CREATE PROCEDURE catalog_get_category_products(IN inCategoryId INT)
BEGIN
SELECT p.product_id, p.name, p.description, p.price,
p.discounted_price
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = inCategoryId
ORDER BY p.product_id;
END$$
-- Create catalog_add_product_to_category stored procedure
CREATE PROCEDURE catalog_add_product_to_category(IN inCategoryId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000),
IN inPrice DECIMAL(10, 2))
BEGIN
DECLARE productLastInsertId INT;
INSERT INTO product (name, description, price)
VALUES (inName, inDescription, inPrice);
SELECT LAST_INSERT_ID() INTO productLastInsertId;
INSERT INTO product_category (product_id, category_id)
VALUES (productLastInsertId, inCategoryId);
END$$
7. You can now load your administration page, navigate to a category, and click the Edit Products button. You
should get a page similar to the one shown in Figure 11-3.
CHAPTER 11 ?– CATALOG ADMINISTRATION: PRODUCTS AND ATTRIBUTES 326
How It Works: Product Administration
So, now you can see the products that belong to a category. You can??™t edit product details yet, but having them
listed is an important step to getting there.
Pages:
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440