PHP search on keywords

Question

I have a table in a database with records containing keywords as well as other data. What would be a logical way to create a search function that would allow people to search based on keywords, and order results based on the number of matched keywords?

Answer 1

Mysql provide FULLTEXT search options. Check this link mysql full text search. These search results will be sorted according to best match. it also has support for boolean mode and NATURAL LANGUAGE MODE(default). You need to add FULLTEXT index on search column.

Answer 2

Here is the query that will work for you.

SELECT *, MATCH (ab,cd) AGAINST ('sample text' IN BOOLEAN MODE) AS relevancy 
FROM table_name 
WHERE MATCH (ab,cd) AGAINST ('sample text' IN BOOLEAN MODE) 
ORDER BY relevancy DESC;