The more you work with DBs and queries, the easier it is to do; even experts sometimes struggle
with creating the perfect query or get bogged down keeping track of the joins and so forth, so take heart and
keep at it. It really is a case of practice makes perfect!
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 135
catalog_count_products_on_catalog
The catalog_count_products_on_catalog stored procedure returns the count of the number of
products to be displayed on the catalog??™s front page. These are products whose display fields
have the value of 1 (product is promoted on the first page) or 3 (product is promoted on the
first page and on the department pages).
-- Create catalog_count_products_on_catalog stored procedure
CREATE PROCEDURE catalog_count_products_on_catalog()
BEGIN
SELECT COUNT(*) AS products_on_catalog_count
FROM product
WHERE display = 1 OR display = 3;
END$$
catalog_get_products_on_catalog
The catalog_get_products_on_catalog stored procedure returns the actual products to be displayed
on the catalog??™s front page. These are products whose display fields have the value of 1
(product is promoted on the first page) or 3 (product is promoted on the first page and on the
department pages). The product description is trimmed at a specified number of characters.
The pagination is implemented the same way as in the previous two stored procedures that
return lists of products.
Pages:
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234