MySQL have a very nifty search function built in, which in many ways are more sophisticated than the WHERE LIKE (‘%’). Using full text search with MATCH (…) AGAINST (…) AS relevancy it is also possible to get results ranked by relevancy. Here’s the quick, dirty way to do it:
1. First we need to create a full text index for the fields we want to search (note that fields have to be of TEXT or VARCHAR type). This is done by
ALTER table1 ADD FULLTEXT (‘column1′,’column2′,’…’)
2. The search query could then look like this:
SELECT *, MATCH(`column1`,`column2`) AGAINST (‘$phrase’) AS relevancy
FROM table 1
WHERE MATCH(`column1`,`column2`) AGAINST (‘$phrase’)
and the result like this:
data1 data2 data3 0.9454332
where the tailing number would be the score, or relevancy that can be used to rank search results. This is very handy and a lot more user-friendly than the rigid WHERE LIKE construct.
Popularity: 5% [?]