One last detail you can notice in Figure 8-2 is that the site employs paging. If there are
a lot of search results, you??™ll only present a fixed (but configurable) number of products per
page and allow the visitor to browse through the pages using navigational links.
Let??™s begin implementing the functionality starting, as usual, with the data tier.
Teaching the Database to Search Itself
You have two main options to implement searching in the database:
??? Implement searching using WHERE and LIKE.
??? Search using the full-text search feature in MySQL.
Let??™s analyze these options.
CHAPTER 8 ?– SEARCHING THE CATALOG 223
Searching Using WHERE and LIKE
The straightforward solution, frequently used to implement searching, consists of using LIKE
in the WHERE clause of the SELECT statement. Let??™s take a look at a simple example that will return
the products that have the word ???flower??? somewhere in their descriptions:
SELECT name FROM product WHERE description LIKE '%flower%'
The LIKE operator matches parts of strings, and the percent wildcard (%) is used to specify
any string of zero or more characters. That??™s why in the previous example, the pattern %flower%
matches all records whose description column has the word ???flower??? somewhere in it. This
search is case-insensitive.
If you want to retrieve all the products that contain the word ???flower??? somewhere in the
product??™s name or description, the query will look like this:
SELECT name
FROM product
WHERE description LIKE '%flower%' OR name LIKE '%flower%';
This method of searching has the great advantage that it works on any type of MySQL
tables (such as InnoDB table type), but has three important drawbacks:
Speed: Because we need to search for text somewhere inside the description and name
fields, the entire database must be searched on each query.
Pages:
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333