mysql FULLTEXT search multiple words

Question

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.

Answer 1

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

Answer 2

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?