The following SQL statement achieves this:
SELECT name, description FROM product
WHERE MATCH (name, description) AGAINST ("beautiful flower");
Executing this query when the tshirtshop database contains the sample data would
return 33 product records.
When performing such searches, you usually want to retrieve the results sorted in descending
order by relevancy. This is can be done using the ORDER BY clause and providing the MATCH
rule as an argument. Always remember to use the DESC option, so that the most relevant result is
placed at the top.
SELECT name, description FROM product
WHERE MATCH (name, description) AGAINST ("beautiful flower")
ORDER BY MATCH (name, description) AGAINST ("beautiful flower") DESC
The query has 33 results using our sample data, shown partially in Figure 8-4. The results
represent the records ordered based on search relevance value, the most relevant results being
shown first (the list in Figure 8-4 was generated by executing the query and clicking the ???Print
view (with full texts)??? link that shows up at the bottom of the phpMyAdmin page).
For example, products that contain both the words ???beautiful??? and ???flower??? (or contain
more instances of them) appear higher in the list than products that contain only one of the
words.
Figure 8-4. Sample search results
CHAPTER 8 ?– SEARCHING THE CATALOG 228
FINE-TUNING MYSQL FULLTEXT SEARCHING
By default, words that aren??™t at least four characters long are not indexed (and as a result they are never
included in any searches), but you can change this behavior if you want.
Pages:
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339