Use phpMyAdmin to execute the following code, which creates the catalog_count_search_result
stored procedure into your tshirtshop database. Don??™t forget to set $$ as the delimiter before executing
the code.
-- Create catalog_count_search_result stored procedure
CREATE PROCEDURE catalog_count_search_result(
IN inSearchString TEXT, IN inAllWords VARCHAR(3))
BEGIN
IF inAllWords = "on" THEN
PREPARE statement FROM
"SELECT count(*)
FROM product
WHERE MATCH (name, description) AGAINST (? IN BOOLEAN MODE)";
ELSE
PREPARE statement FROM
"SELECT count(*)
FROM product
WHERE MATCH (name, description) AGAINST (?)";
END IF;
SET @p1 = inSearchString;
EXECUTE statement USING @p1;
END$$
2. Now, follow the same procedure to execute this code, which creates the catalog_ search stored procedure
into your tshirtshop database:
-- Create catalog_search stored procedure
CREATE PROCEDURE catalog_search(
IN inSearchString TEXT, IN inAllWords VARCHAR(3),
IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
IF inAllWords = "on" THEN
PREPARE statement FROM
"SELECT product_id, name,
IF(LENGTH(description) <= ?,
description,
CONCAT(LEFT(description, ?),
'...')) AS description,
price, discounted_price, thumbnail
FROM product
WHERE MATCH (name, description)
AGAINST (? IN BOOLEAN MODE)
CHAPTER 8 ?– SEARCHING THE CATALOG 231
ORDER BY MATCH (name, description)
AGAINST (? IN BOOLEAN MODE) DESC
LIMIT ?, ?";
ELSE
PREPARE statement FROM
"SELECT product_id, name,
IF(LENGTH(description) <= ?,
description,
CONCAT(LEFT(description, ?),
'.
Pages:
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343