ShowStopper ShowStopper - 5 months ago 27
MySQL Question

MySQL search algorithm for best match

i have a problem . I am creating a product suggestions functionality based on the subcategories of products. Suppose i have subcategories like

Case 1 (if i have 2 words)


  1. TV has 1 products

  2. Samsung TV has 1 products

  3. Samsung refrigerator has 1 product

  4. Samsung fridge has 1 product



What i want is if i enter Samsung TV then i want products from
TV
or
Samsung TV
but not from
Samsung refrigerator
or
Samsung fridge
. what i have tried is

select * from tabel name where title like "%Samsung TV%" or title like "%Samsung" or title like "%TV"


case 1 PROBLEM

it also select the products from Samsung refrigerator and Samsung fridge

Case 2(when words are more than 2)


  1. Samsung washing machine has 1 products

  2. Tata washing machine has 1 products

  3. Samsung washing device has 1 product

  4. washing machine has 1 product



What i want is if i enter Samsung washing machine. the query i wrote is

select * from tabel name where title like "%Samsung washing machine%" or title like "%Samsung washing%" or title like "%washing machine%"


case 2 PROBLEM

Till now no problem but better solution will be appreciated .
enter image description here
huge Thanks in advance.

MKA MKA
Answer

You can try something like this:-

select *  from department  where Name like "%Samsung" or (Name like "%Tv%" and Name like "%Samsung%") UNION select  from department  where Name like "%tv" or (Name like "%Samsung%" and Name like "%TV%")

Hope this will be helpfull

Comments