-- Create catalog_get_products_on_catalog stored procedure
CREATE PROCEDURE catalog_get_products_on_catalog(
IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
PREPARE statement FROM
"SELECT product_id, name,
IF(LENGTH(description) <= ?,
description,
CONCAT(LEFT(description, ?),
'...')) AS description,
price, discounted_price, thumbnail
FROM product
WHERE display = 1 OR display = 3
ORDER BY display DESC
LIMIT ?, ?";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inStartItem;
SET @p4 = inProductsPerPage;
EXECUTE statement USING @p1, @p2, @p3, @p4;
END$$
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 136
catalog_get_product_details
The catalog_get_product_details stored procedure returns detailed information about a
product and is called to get the data that will be displayed on the product??™s details page.
-- Create catalog_get_product_details stored procedure
CREATE PROCEDURE catalog_get_product_details(IN inProductId INT)
BEGIN
SELECT product_id, name, description,
price, discounted_price, image, image_2
FROM product
WHERE product_id = inProductId;
END$$
catalog_get_product_locations
The catalog_get_product_locations stored procedure returns the categories and departments
a product is part of. This will help us display the ???Find similar products in our catalog??? feature
in the product details pages.
Pages:
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235