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 addedFULLTEXT INDEX
to the fieldproduct_title