Search code examples
phpmysqlsearchsql-like

Make single query for search full title and its words in PHP & MYSQL?

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?

Solution

  • 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