16

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.

Vikas Arora
  • 1,666
  • 2
  • 17
  • 38
Jeff Voss
  • 3,637
  • 8
  • 46
  • 71

2 Answers2

27
$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);");
Alive to die - Anant
  • 70,531
  • 10
  • 51
  • 98
Vineet1982
  • 7,730
  • 4
  • 32
  • 67
  • 1
    Can somebody enlighten me if there is also a limit of how many terms you can search in 1 MATCH AGAINST query? In this answer's example, you have third_word which is also working in my own query but when i put the +fourth_word & even if it exist in the columns, it does return empty result. – vincent Dec 05 '14 at 07:33
  • 1
    "exact phrase" does not mean exact phrase without any sign (+/-) it will be treated as an OR. there is no exact phrase in full text search, only all words, some words, none of the words. – Gamesh Jan 22 '16 at 08:19
  • 1
    This are changed over period of 5 years – Vineet1982 Jan 23 '16 at 08:32
  • @Gamesh fulltext search does have "exact phrase" searching as long as you wrap the phrase in double quotes. I'm using it now on one of my projects. – Chris Apr 19 '20 at 07:18
  • @Chris you're right it does, not sure what i was thinking writing that: `A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".` – Gamesh Apr 20 '20 at 14:22
6
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?

Jacob
  • 41,721
  • 6
  • 79
  • 81
  • Hi sorry I posted that before I went to bed last night. This is still not working for me, I only have one search field called $term and both the First and Last names are in the same mySQL column – Jeff Voss Jul 16 '11 at 22:05