Then, execute this
code, which adds the review table in your tshirtshop database:
-- Create review table
CREATE TABLE `review` (
`review_id` INT NOT NULL AUTO_INCREMENT,
`customer_id` INT NOT NULL,
`product_id` INT NOT NULL,
`review` TEXT NOT NULL,
`rating` SMALLINT NOT NULL,
`created_on` DATETIME NOT NULL,
PRIMARY KEY (`review_id`),
KEY `idx_review_customer_id` (`customer_id`),
KEY `idx_review_product_id` (`product_id`)
);
2. Execute the following code, which creates the catalog_get_product_reviews stored procedure in the
tshirtshop database (don??™t forget to set the delimiter to $$). The catalog_get_product_review
stored procedure retrieves the reviews for the product identified by the inProductId parameter.We also
need the name of the reviewer, so we made an INNER JOIN with the customer table.
-- Create catalog_get_product_reviews stored procedure
CREATE PROCEDURE catalog_get_product_reviews(IN inProductId INT)
BEGIN
SELECT c.name, r.review, r.rating, r.created_on
FROM review r
INNER JOIN customer c
ON c.customer_id = r.customer_id
WHERE r.product_id = inProductId
ORDER BY r.created_on DESC;
END$$
3. Execute the following code, which adds the catalog_create_product_review stored procedure in the
tshirtshop database (don??™t forget to set the delimiter to $$). When a registered visitor adds a product
review, the catalog_create_product_review stored procedure is called.
Pages:
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772