CODEWITHSUNDEEP
phpmysqlsearch
Jeff Voss
Jeff Voss

Reputation: 3695

mysql FULLTEXT search multiple words

I've never really heard a straight answer on this one, I just need to FULLTEXT search a couple columns with multiple words "Firstname Lastname"

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

But it fails to run the query if I enter more than one word here.

Upvotes: 16

Views: 29252

Answers (2)

Vineet1982
Vineet1982

Reputation: 7918

$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);");

Upvotes: 28

Jacob
Jacob

Reputation: 43229

SELECT * 
FROM patient_db 
WHERE MATCH ( Name, id_number ) 
      AGAINST ("Firstname Lastname" IN BOOLEAN MODE);

The double-quotes are important. This looks for literally the phrase "Firstname Lastname". You don't need the percentage signs.

If you look for "Firstname blahblahblah Lastname blahblah", the AGAINST() clause has to look like this:

AGAINST ('+Firstname +Lastname' IN BOOLEAN MODE);

Have look at the MySQL docs on full text search for more info.

Another thing: why do you have the id_number column in your match?

Upvotes: 6

Related Questions