CODEWITHSUNDEEP
phpmysql
cmplieger
cmplieger

Reputation: 7351

search for multiple words using mysql MATCH AGAINST

I am using this basic mySQL query which works great: 

$sql = "SELECT * FROM `clients` WHERE 
        MATCH(`LNAME`) AGAINST('$c')  OR
        MATCH(`FNAME`) AGAINST('$c')  OR
        MATCH(`MAIL`) AGAINST('$c')  OR
        MATCH(`TEL`) AGAINST('$c')  "

where $c is the search query. Now this works with all single words/numbers but whenever I add 2 words no results are returned.

For example, if my database has aaaa bbbb in LNAME and I search for "aaaa bbbb" I get nothing back, however when I search for "aaaa" or "bbbb" it does work. I tried adding IN BOOLEAN MODE but it doesn't make a difference.

Could ayone explain to me how this works? $c is composed of letters, numbers and/or a @

thanks a lot.

Upvotes: 0

Views: 2350

Answers (1)

Ofir Baruch
Ofir Baruch

Reputation: 10346

First , you should use MATCH AGAINST like this:

$sql = "SELECT * FROM `clients` WHERE MATCH(`LNAME`,`FNAME`,`MAIL`,`TEL`) AGAINST('$c')"

Please notice:

Short words are ignored, the default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len

and:

If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones.

You can use LIKE and it probably will have better results. Example of usage:

$sql = "SELECT * FROM `clients` WHERE `LNAME` LIKE '%$c%' OR `FNAME` LIKE '%$c%' OR ..."

Upvotes: 2

Related Questions