MySQL like search with non sequence words on a column

Question

I have a table called 'products' where i have product names like 'Iphone5 with 1GB Data', 'Iphone5 with 10GB Data', etc... and so on...

When user use a search box to search this products i make a query like the following...

 select *
 from products
 where product_name like '%USER_SEARCH_KEY%'
 limit 5;

now when i search 'iphone5 with' i get the rows which has 'iphone5 with' word in it. but when i search 'iphone5 1gb' i get no records found... i had to enter 'iphone5 with 1gb' to get that result. Is there any way i get the record on result set when i search for 'iphone5 1gb' keyword also ?

Thanks in advance.

check this out: https://stackoverflow.com/questions/9099469/mysql-select-like-or-regexp-to-match-multiple-words-in-one-record — sailhenz, Oct 03 '18 at 06:12

score 1 · Accepted Answer · edited Oct 03 '18 at 08:00

1

You have 3 way for a solution to this task:

You can create full-text index and search by it
You can break a user search query on words and make database query like the following:

SELECT * FROM products WHERE product_name LIKE '%iphone5%1gb%' limit 5
You can use a search engine, for example, Elastic Search. I think this way is better

edited Oct 03 '18 at 08:00

JIJOMON K.A

1,290
3
12
29

answered Oct 03 '18 at 06:07

Maksym Fedorov

6,383
2
11
31

Zaynul Abadin Tuhin · Answer 2 · 2018-10-03T06:12:19.077

0

you can use just 1gb in search string

 select * from t1 where  item like '%1GB%'

otherwise you have to use fulltext search but for using this to have to implement that in your table

edited Oct 03 '18 at 06:12

answered Oct 03 '18 at 06:06

Zaynul Abadin Tuhin

31,407
5
33
63

MySQL like search with non sequence words on a column

2 Answers2