0

I am running a SQL Query as follows:

SELECT a.id as id,a.name as name , a.price as price , a.saleprice as saleprice, a.images as images, a.slug as slug,a.hover_name as hover_name, MATCH (a.name) AGAINST ('+"blue cushion"') AS title_relevance FROM gc_products a WHERE ( ( ( MATCH (a.name) AGAINST ('+"blue cushion"') ) OR (a.sku like '%blue cushion%') ) and (a.enabled=1) ) ORDER BY title_relevance DESC

But It is giving me wrong results it is also showing me records where name is like "blue candle" , "red cushion"

I have also tried 

         MATCH (a.name) AGAINST ('blue cushion')

     MATCH (a.name) AGAINST ('+blue cushion')

MATCH (a.name) AGAINST ('+blue +cushion')

It's not necessary that both the words be together.They can be any where in name but it is necessary that both the words must be there in name. '+' sign means 0 or more while I need at least one time.

But same result coming. I want if there are more than one words to match then it should match all the words in name.

 MATCH (a.name) AGAINST ('blue +cushion')
tshepang
  • 12,111
  • 21
  • 91
  • 136
user3110655
  • 121
  • 3
  • 20
  • 1
    The `+`, `*` etc... syntax only works IN BOOLEAN MODE. – Vatev Apr 08 '14 at 13:19
  • what's that? How to use boolean mode.Please give me the answer according to my search results – user3110655 Apr 08 '14 at 13:20
  • http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html http://stackoverflow.com/questions/9410632/mysql-match-against-example http://stackoverflow.com/questions/19004674/mysql-match-against-in-boolean-mode-returns-nothing-on-middle-word – Abhik Chakraborty Apr 08 '14 at 13:21

1 Answers1

0

You have to use the IN BOOLEAN MODE modifier, because by default MySQL performs a natural language search.

With this modifier, certain characters have special meaning at the beginning or end of words in the search string.

Source: Boolean Full-Text Searches

Just rewrite your query like this:

SELECT a.id as id,a.name as name , a.price as price , a.saleprice as saleprice,
    a.images as images, a.slug as slug,a.hover_name as hover_name,
    MATCH (a.name) AGAINST ('+"blue cushion"' IN BOOLEAN MODE) AS title_relevance
    FROM gc_products a
    WHERE ( ( ( MATCH (a.name) AGAINST ('+"blue cushion"' IN BOOLEAN MODE) )
    OR (a.sku like '%blue cushion%') )
    AND (a.enabled=1) )
    ORDER BY title_relevance DESC
citizen404
  • 1,485
  • 1
  • 10
  • 19
  • its not working. It's giving me zero records while I have products with name like "DEAUVILLE red VELVET pleated CUSHION" – user3110655 Apr 09 '14 at 04:16
  • It's not necessary that both the words be together.They can be any where in name but it is necessary that both the words must be there in name. '+' sign means 0 or more while I need at least one time. – user3110655 Apr 09 '14 at 04:24
  • @user3110655 - '+'-sign doesn't mean 0 or more, it means the word **must** be there. If you don't need to have both words together as an exact phrase, but want to have both words present, anyway, omit the " (double quotes) and also add a '+'-sign to the second word. `'+blue +cushion'` – citizen404 Apr 09 '14 at 04:49
  • I tried the same but did not work man. :(. It is giving me results having only suhion e.g "SINTRA purple velvet APPLIQUED CUSHION" – user3110655 Apr 09 '14 at 06:24