If the total number of products in the category is larger than this number,
we return only a page containing the number of inProductsPerPage products. Again,
this variable??™s value will be declared globally in config.php later on; for now, you only
need to know that for our project we will set this to 4.
??? inStartItem is the index of the first product to return. So, for example, when the visitor
visits the second page of products??”using pagination and displaying four products per
page??”inStartItem will be 4 and inProductsPerPage will be 4. With these values, the
catalog_get_products_in_category function will return the products from the fifth to
ninth rows of results.
-- Create catalog_get_products_in_category stored procedure
CREATE PROCEDURE catalog_get_products_in_category(
IN inCategoryId INT, IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
-- Prepare statement
PREPARE statement FROM
"SELECT p.product_id, p.name,
IF(LENGTH(p.description) <= ?,
p.description,
CONCAT(LEFT(p.description, ?),
'...')) AS description,
p.price, p.discounted_price, p.thumbnail
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = ?
ORDER BY p.display DESC
LIMIT ?, ?";
-- Define query parameters
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inCategoryId;
SET @p4 = inStartItem;
SET @p5 = inProductsPerPage;
-- Execute the statement
EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
END$$
In this procedure, you can find a demonstration of using prepared statements, which
represent query templates that contain input parameters whose values you supply right before
executing the statement.
Pages:
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230