MySQL Fulltext Search Multiple Words

mysql FULLTEXT search multiple words

$sql = mysql_query("SELECT * FROM 
         patient_db WHERE 
         MATCH ( Name, id_number ) 
         AGAINST ('+first_word +second_word +third_word' IN BOOLEAN MODE);");

and if you want to do exact search:

$sql = mysql_query("SELECT * 
                  FROM patient_db 
                  WHERE MATCH ( Name, id_number ) 
                  AGAINST ('"exact phrase"' IN BOOLEAN MODE);");

Full Text Search on MySQL : Perform search by multiple words in one DB table

Thanks, O. Jones

Here is a solution

  SELECT *,
  MATCH (firstname, lastname, username, Country) 
         AGAINST ('+Ricky +Cris +Germany' IN boolean MODE) AS score
  FROM user 
  WHERE MATCH (firstname, lastname, username, Country)
         AGAINST ('+Ricky +Cris +Germany' IN boolean MODE)
  ORDER BY score DESC

How to make a multiple column mysql fulltext search where partial words are matched

In boolean mode, requiring strings to be present (instead of just scoring higher), is done with +. prefix matching is done with an ending *. This seems to be what you want, so search for:

+John* +S*
+John* +Smith*
+Smith* +J*
+Jo* +S*

Note that Full Text indexes cannot help you searching 'anywhere in a word'. so something like *mith* is bound to fail: they're meant to match from character 1 in an index.

If you also want to order them by match values, and for instance, need John Smith before Johnny Smithson, you'd do this:

 SELECT * FROM user 
 WHERE MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE)
 ORDER BY MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE) DESC;

Which you will see will get you nowhere unless you add all the words >= ft_min_word_len again separately:

+John* +S* John
+John* +Smith* John Smith
+Smith* +J* Smith
+Jo* +S*

For the last one, both are < the default 4 characters, so we can't add sorting params for that in default mysql, but you could set ft_min_world_len differently is desired.

MySQL: Finding multiple words in full text search - exact matches only

As far as I can see in the docs, using quotes should be enough. From the examples on the docs page:

"some words"

Find rows that contain the exact phrase “some words” (for example, rows that contain “some words of wisdom” but not “some noise words”). Note that the “"” characters that enclose the phrase are operator characters that delimit the phrase. They are not the quotation marks that enclose the search string itself.

html mysql full-text search query with multiple words

I want to know how to recognize when a person types multiple words.

How do you define "multiple words"? Are those words with a space in between? Then explode() the input on spaces, and build your query from the result of that.

mysql match against multiple words

To match an exact phrase, just use double quotes to surround the phrase to match;

SELECT * 
FROM products 
WHERE MATCH(desc) 
      AGAINST('"iphone 4s"' IN BOOLEAN MODE) 
LIMIT 10

More info at the manual pages.

---

Related Topics