Search 5 keywords in mysql table

Question

I'm trying to search keywords from tags users input in a MySQL table, and return the best result based on the number of matches.

Code:

MySQL structure:

 id    | keywords       | phrase
 1     | apple king pearl   | I was eating an apple when the king hit me
 2     | brush brute fancy  | you fancy this brush?   
 3     | king queen kingdom | shall the queen obey the king or the kingdom?

PHP:

 $keywords_raw='me wall like king apple'   //define keywords based on the tags the user inputs
 $keywords=explode(' ', $keywords_raw);

.... That's where I got stuck. My ideass are:

1. the search would be performed on each keyword such as "me", "wall", "like" and so on

2. for each keyword, it would search through both columns "keywords" and "phrase" in each row of the table, and return the number of matches found. For example, searching keywords inputted against the first row would return that keywords "me" has 0 matches, "wall" has 0 matches, "like" 0, "king" 2 and "apple" 2. Thus the total matches would be 2+2 = 4.

3. At the end, compare the total number of matches found from all rows, and pick the top 3 rows that have the most matches.

A side question to #2 would be how to ignore words that contain the keyword searched, such as "kingdom" that contains "king" but is a different word.

---

Update:

following the helpful answers, I have used fulltext search.

    $keywords='bb';

    $data['recommendation']=$this->db->query
    ("SELECT *, MATCH(keywords, phrase) AGAINST ('$keywords') as score 
    FROM game
    WHERE MATCH(keywords, phrase) AGAINST ('$keywords') 
    ORDER BY score 
    LIMIT 3");

    var_dump($data['recommendation']);
    die;

for some reason var_dump returns an empty result with no row found. But I do have the phrase 'bb' in at least 2 rows of the table as below.

 id    | keywords       | phrase
 1     | bb king        | I was eating an apple when bb the king hit me
 2     | bb             | you fancy this brush?   

Answer 1

As Barmar says you can use full text functions:

SELECT id, customer_id, phrase 
FROM table
WHERE MATCH(phrase) AGAINST ('me wall like king apple');

If need to search on another column too then add it to MATCH:

SELECT id, customer_id, phrase 
FROM table
WHERE MATCH(phrase,keywords) AGAINST ('me wall like king apple');

Edit:

For the point 3 you can use the same functions:

SELECT id, customer_id, phrase, MATCH(phrase,keywords) AGAINST ('me wall like king apple') as score 
FROM table
WHERE MATCH(phrase,keywords) AGAINST ('me wall like king apple') 
ORDER BY score 
LIMIT 3;

This query will return the three best matches

For more information take a look to the manual

UPDATE:

According to manual:

Some words are ignored in full-text searches:

• Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is four characters.

• Words in the stopword list are ignored. A stopword is a word such as “the” or “some” that is so common that it is considered to have zero semantic value. There is a built-in stopword list, but it can be overwritten by a user-defined list.

You are using a short word to search with, this is why you didn't getting any result.

UPDATE 09-09-14:

From documentation:

If you modify full-text variables that affect indexing (ft_min_word_len, ft_max_word_len, or ft_stopword_file), or if you change the stopword file itself, you must rebuild your FULLTEXT indexes after making the changes and restarting the server. To rebuild the indexes in this case, it is sufficient to do a QUICK repair operation:

mysql> REPAIR TABLE tbl_name QUICK;

So, after executing ALTER TABLE table ADD FULLTEXT(phrase, keywords); you must execute REPAIR TABLE tbl_name QUICK; but only once

Answer 2

  $keywords=explode(' ', $keywords_raw);
  if(count($keywords)>1)
                 {
                     $stmt.="(";
                     $i=0;
                     foreach($arr_s1 as $kv)
                     {
                        if($i>0)
                        {
                                $stmt.=" AND ";
                        }
                        $stmt.=" phrase like '%".$kv."%'";                       
                        $i++;
                     }
                     $stmt.=")";                    

                 }

Hope it will work for you.