Flames Flames - 6 months ago 8
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 ?

Thanks

Answer

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,

SELECT *, 
      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

See here ONLINE DEMO