MySql fulltext search in PHP using string containing keywords

Question

I have a string that contains some keywords like this $string = 'one, two, "phrase three words"' I am trying to do a full-text search using:

    SELECT *, MATCH (column) AGAINST ('$string') AS score, 
FROM table WHERE MATCH (column) AGAINST ('$string' IN BOOLEAN MODE) ORDER BY score DESC

When MySql gives back the results, the keyword "phrase three words" is not matched as a phrase, but every word from it is matched as a single.

How should I modify the string or the query to receive the results where the whole phrase matches? I have tried with stripslashes() for the string, but the result is the same.

Solution

As MySQL manual says:

A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed.

Let's look at the example table:

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial   | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"database comparison"' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

Order matters, when the words are quoted:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"comparison database"' IN BOOLEAN MODE);

Empty set (0.01 sec)

When we remove the quotes, it will search for rows, containing words "database" or "comparison":

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('database comparison' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

Order doesn't matter now:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('comparison database' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

If we want to get rows, containing either word "PostgreSQL" or phrase "database comparison", we should use this request:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('PostgreSQL "database comparison"' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

fiddle

Make sure, that the words, you are searching for, are not in the list of stopwords, that are ignored.

Comments

[Vasko]

Hi user4035, I understand the full-text search basics and how it works, my point is to get to the results like in your last example, but the problem is when I put a string that contains double quotes in against. The results don't contain the full phrase, but the words in the phrase.

[user4035]

@Vasko That's very strange, because for me when I put the words in quotes, MySQL searches for the exact phrase and not the words. Please, copy my example table from the fiddle and try the last query in your system. Let me know, how it works.

[Vasko]

I have used sphinxsearch.com instead of built in FULLTEXT search of mySql and now the application is much more responsive. Thank you anyway for for your detailed answer.

[Vignesh Prajapati]

This answer is really very helpful. +1 for this.