Intelligent Search Using Mysql and PHP

Question

I have an SQL table called products_list containing details of products, i could list out two of the very important fields here, based on which i need to integrate the INTELLECT SEARCH RESULT.

product_id
product_title

Example of content within

Lenovo K3 Note
Lenovo ZUK
Apple Iphone 6s
Lenovo A6000

and so on.

What i require?

if user enters lenov, it should result with 'lenovo k3 Note, lenovo zuk, lenovo a6000'

if user enters le, it should result with 'lenovo k3 Note, lenovo zuk, lenovo a6000, apple iphone 6s'

if user enters 6 iphon, it should result with 'apple iphone 6s'

NOTE: very importantly i need most matching product to list first, Eg, if user enters lenov zu, the result should sort by most matched string here like, 'lenovo zuk first, lenovo a6000 and lenovo k3 in any order'

What i tried?

i tried using 'like', which almost worked but failed to sort with most matched string. select * from product_list where product_title like '%$searchString%' order by product_title asc

i tried using 'MATCH AGAINST' logic, this worked but failed to sort again.

select *, MATCH(product_title) AGAINST ('$searchString*') AS RELEVANCE from product_list where MATCH(product_title) AGAINST ('$searchString*' IN BOOLEAN MODE) order by RELEVANCE DESC

RELEVANCE is always 0

NOTE: i have used MyISAM as Storage Engine, and added FULLTEXT INDEX to the field product_title

What does it mean? `this worked but failed to sort again.`. `MATCH AGAINST` is the proper method for this purpose — Marcos Pérez Gude, Mar 04 '16 at 11:47
yup i tried it @MarcosPérezGude, but didn't get the proper result here, i tried searching 'lenov zu', it listed lenovo k3 first. — ameenulla0007, Mar 04 '16 at 11:49
What if you print `RELEVANT` variable to see how punctuation gives each result? Maybe can be useful for us (to help you) — Marcos Pérez Gude, Mar 04 '16 at 11:51
And why it's searching `IN BOOLEAN MODE` ? And for what is the `*` in the variable? `('$searchString*')`. I don't understand that statement. I use match against without boolean mode and without wildcard and it was working fine always. — Marcos Pérez Gude, Mar 04 '16 at 12:01
`*` is to search substrings within the full text, Eg, field value is 'lenovo k3', you can get result by only entering 'leno'. — ameenulla0007, Mar 04 '16 at 12:40
If you remove `*` and search `leno` it must shows `lenovo k3` too — Marcos Pérez Gude, Mar 04 '16 at 12:42
This might will help you. It is best explained here. http://stackoverflow.com/a/6305108/4819200 — Mayur Chauhan, Mar 04 '16 at 12:47
Will you please guide me through, by adding up an SQL string, which I can try out. `*` is used here, because `match against` requires more than 4 chars as input. If * is used it accepts even 1 char. — ameenulla0007, Mar 04 '16 at 13:12

Intelligent Search Using Mysql and PHP

0 Answers0