Flames Flames - 2 years ago 102
SQL Question

SQL full text query only working for first keyword

I'm not an expert on FULL TEXT search so I kind of googled around for an SQL query SORTED BY RELEVANCY and came up with this which is somewhat working:

SELECT *, MATCH(fulltext_tags) AGAINST ('+blue' '+red') AS relevance FROM `products` WHERE MATCH(fulltext_tags) AGAINST ('+blue' '+red' IN BOOLEAN MODE) ORDER BY relevance DESC

The problem is, only the first keyword is taken into account.
Say I have this Product table

| name | fulltext_tags |
| Product1 | Blue |
| Product2 | Red |
| Product3 | Blue Red |
| Product4 | Yellow |

The above query only returns product 1 and 3, it is completely ignoring the second keyword '+red'. Even adding +Yellow as third keyword still ignores it, so it ignores anything past the first keyword...

How do I make it so a query with +blue and +red returns Product1 Product2 and Product3 since at least one or both keywords are figuring in the fulltext_tags field ?


Answer Source

You should use REGEXP '[[:<:]]Red[[:>:]]' for find exact word, so try below query hope it will work and fulfill your requirement.

SELECT * FROM `products` 
  WHERE fulltext_tags REGEXP '[[:<:]]Red[[:>:]]' 
  OR fulltext_tags REGEXP '[[:<:]]Blue[[:>:]]';

You can see DEMO Here.

Update 1

Try This Query, As I can observe you want to do something like this,

      MATCH(fulltext_tags) AGAINST ('+blue' '+red')     
      AS relevance FROM `products` 
  WHERE MATCH(fulltext_tags) AGAINST('Red') 
      OR MATCH(fulltext_tags) AGAINST('Blue')
  ORDER BY relevance DESC


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download