How to Make a More Accurate Live Search in PHP

+1 Mitch Mullvain · February 11, 2015
I've been working on a live search feature for my site. I take a bunch of table values (i.e. bands, genres, albums, etc.) and I put them in their respective tables, as well as a search table for indexing. My main question is how do I make it more accurate.

Here is my mysql query (NOTE: the results are the same with or without Desc):


$search = "
   (SELECT * FROM `search`
   WHERE `search_name` LIKE '%$search_term%'
   ORDER BY '%$search_term%' DESC
   LIMIT 0, 8)";


Here is an example of the database:

Search ID  |  Search Name                       |  Type  |
----------------------------------------------------------
8         | Big Deal (What's He Done Lately?)  | Album  |
12        | Henry's Funeral Shoe               | Band   |


Problem is, is that when I type say  H  into the search parameter, I expect   Henry's Funeral Shoe  to be at the top, but instead I get  Big Deal (What's He Done Lately?)   before it, because it contains H and it is searched before the more appropriate one is.
So my question:  Is there a MySQL function that can sort through the table and find the most relevant results and weigh them against those less relevant?

Post a Reply

Replies

Oldest  Newest  Rating
0 Samuel Oloruntoba · February 14, 2015
cool
0 Mitch Mullvain · February 13, 2015
True, but I quickly expand my knowledge, so by the time I get my site to have thousands of entries (and hopefully that day will come), I will probably have a better grip on search engines, but this works for now. 

In my opinion, make it work before you spruce it up.
0 Samuel Oloruntoba · February 11, 2015
But now the query is getting too complex, run a few thousand of these, you mysql server would crash
+1 Mitch Mullvain · February 11, 2015
I also put my answer on stackoverflow, and I after testing someone's answer, I found that the following code works.


$sql = "
(SELECT * FROM `search`
WHERE `search_name` LIKE '%$searchQuery%'
GROUP BY `search_name`
ORDER BY CASE  
WHEN `search_name` LIKE '$searchQuery%' AND `search_type` = 'Band' THEN 0
WHEN `search_name` LIKE '$searchQuery%' AND `search_type` = 'Album' THEN 1
WHEN `search_name` LIKE '$searchQuery%' AND `search_type` = 'Genre' THEN 2
WHEN `search_name` LIKE '$searchQuery%' AND `search_type` = 'User' THEN 3
WHEN `search_name` LIKE '%$searchQuery%' THEN 4
ELSE 5
END, `search_name`
LIMIT 0, 8)";


It works by setting precedence for bands, then albums, genres, and users. It works by taking the typed text and matching it with the beginning of a field. If the begging of the result doesn't match, then it looks for the next best row match.

I figure I at least post it in case someone else stumbles upon this and wants the answer.
+1 Samuel Oloruntoba · February 11, 2015
If you applied fulltext and indexed the table then this search would be easy, don't know what fulltext is, check here http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html
  • 1

PHP

106,925 followers
About

Server-side, HTML embedded scripting language used to create dynamic Web pages.

Links
Moderators