Reputation: 604
I have a table containing a column named title.
Input is taken from the user in form of string. The string is actually a group of keywords delimited by space.
I want to search the entire table looking for records with their title fields containing some of the keywords as present in the string input by the user and the results need to be displayed in order of match.
For Ex:
RecordA -> title -> "apple mango grapes banana"
RecordB -> title -> "apple lemon orange"
RecordC -> title -> "banana"
RecordD -> title -> "lemon mango grapes"
Suppose the string input from the user is : "apple mango orange" Then I want the result from the php page in order
Record A // 2 matches
Record B // 2 matches
Record D // 1 match
Record C // 0 match
Suggest me an efficient way to do this in php..
Note: Database is in Mysql. It cannot be modified to add fulltext search index to the table.
Upvotes: 1
Views: 206
Reputation: 1269553
The best method is full text search (see here). This is the right way to do what you want, and it is the most efficient by far.
What you can do in the absence of full text search is something like this:
select t.*
from table t
where title like '%word1%' or
title like '%word2%' or
. . .
order by ((title like '%word1%') +
(title like '%word2%') +
. . .
) desc;
You will have to parse the words the user provides to get each word. Also note that you have a problem with word boundaries. As phrased above this will find any occurrence of the string of letters, even if within a word. If that is an issue, then use regular expressions instead of like
. But, the real solution is full text search.
Upvotes: 1