Tuesday, 23 January 2018

mysql match against multiple words

mysql match against multiple words

Below is an example sql I am stuck with, it will not return a product named "iphone 4s", It returns 10 other result. Any help would be great thanks

1st sql example

SELECT * FROM products 
          WHERE match(desc) against('+iphone +4s' IN BOOLEAN MODE) LIMIT 10";
result: contains the words 'iphone' and '4s'

2nd sql example

SELECT * FROM products 
           WHERE match(desc) against('+iphone 4s' IN BOOLEAN MODE) LIMIT 10";
result: contains the words 'iphone', but rank rows higher if they also contain '4s'

3rd sql example

SELECT * FROM products 
           WHERE match(desc) against('iphone 4s' IN BOOLEAN MODE) LIMIT 10";
result: contains the words 'iphone' or '4s'
What I want to search is 'iphone 4s', but it comes with other result, like 'iphone is good, but 4s...', 'new iphone 5 has published...',...

To match an exact phrase, just use double quotes to surround the phrase to match;
SELECT * 
FROM products 
WHERE MATCH(desc) 
      AGAINST('"iphone 4s"' IN BOOLEAN MODE) 
LIMIT 10

No comments:

Post a Comment

Note: only a member of this blog may post a comment.