5

I want to make a search for products based on whole title name and also title words. It's ok to use LIKE for searching product in mysql by using UNION. My database title(charchar(550)) = Colorzone Men's Casual Wear Red Cotton Shirt not a Fulltext -- What is the best search Algorithm for PHP & MYSQL?

But I need to below the search result(First:Cotton Shirt record, Second Cotton record and Third: Shirt record. Should not showing all the records mixed) as single query

MYSQL Query: For Ex: Title = Cotton Shirt

SELECT title FROM zapstore_ecom_products WHERE title LIKE '%Cotton Shirt%' //Get Whole Title products.
UNION
SELECT title FROM zapstore_ecom_products WHERE title LIKE '%Cotton%' //Get list for first word Title products.
UNION
SELECT title FROM zapstore_ecom_products WHERE title LIKE '%Shirt%' //Get list for last word Title products.

Suppose the user title have 4 or more words then it will make 4 or more query by using UNION. We will make this search with single query?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Ramalingam Perumal
  • 1,367
  • 2
  • 17
  • 46

1 Answers1

0

You can use FULLTEXT search on title in boolean mode against ('Cotton','Shirt') and ORDER BY score DESC This should give you your desired output

I didn't try mysql but according to theory explained in MySQL Doc it will work

shahmanthan9
  • 473
  • 1
  • 3
  • 14