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