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.c...blic/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.