PHP search with jQuery Auto complete issue

[NiTx]

Hi,

 

I'm trying to build a search function for my website with a jQuery auto-complete feature implemented. 

 

I have three columns I need to search for in my database, dev_model, dev_version and dev_model_number. Right now I'm just focusing on getting the query to work with the first two columns. The auto-complete works, but its far from perfect and gives undesired results at times. I've tried multiple different approaches to building my search query. So far, doing a full text search is working the best but its far from perfect.

 

Here's my MYSQL query:

SELECT id, brand_id, dev_model, dev_version, 
MATCH (dev_model,dev_version) AGAINST ('+" . $database->escape_values($parts[$i]) . "*' IN BOOLEAN MODE) AS SCORE FROM devices 
WHERE MATCH (dev_model,dev_version) AGAINST ('+" . $database->escape_values($parts[$i]) . "*' IN BOOLEAN MODE) 
ORDER BY SCORE DESC LIMIT 4

My search page:  http://www.fonefox.com/fonefox/public/index1.php

 

If you search for example 'Galaxy S4', as soon as you start typing the 'S4' (dev_version), the autocomplete hides the Galaxy S4 phone from the search. No idea why, but if the 'dev_version' string is greater then 4 characters it WILL work. Another issue is, you could search 'Galaxy S4 Lumia 1020', and it will return results for a Lumia 1020 which has no relevance to "Galaxy" or "S4". I'm guessing that I need to somehow concatenate dev_model and dev_version before comparing my search terms to it. I'm just not sure how to approach this.

 

Any help would be greatly appreciated.

[winningdave]

Maybe try

 

SELECT id, brand_id, dev_model, dev_version, FROM devices

WHERE (dev_model LIKE '%".$database->escape_values($parts[$i]) ."%' OR dev_version LIKE '%".$database->escape_values($parts[$i]) ."%')

ORDER BY SCORE DESC LIMIT 4