Help with MySQL Search Query.

[joecooper]

Here is my MySQL query I'm using to display search results from a mysql database:

 

$query = "SELECT * FROM urls WHERE MATCH(file) AGAINST('$search_modded' IN BOOLEAN MODE);";

It works well enough, and displays good results, but not a good enough order.

 

 

For example If the search term was "Summer of 69",

 

I would expect to find: "Bryan Adams - Summer of 69.mp3" as the top result.

 

Instead, "Bryan Adams - Summer of 69.mp3" shows up at around 50th position, with these few at the top:

 

Jemini Featuring Eddie Law.._Sweet Sweet Summer.mp3
Motels - Suddenly Last Summer.mp3
Dragonette-Our-Summer-Skeet™-Extended-Version.MP3.mp3
Tiga-What-You-Need-Midnight-Conspiracy-Summer-of-87-Remix.mp3
m-flo ft. ryohei - summer time love.mp3
Theme from A Summer Place.mp3
henry mancini - Theme From A Summer Place.mp3
Summer Nights - John Travolta - 1978.mp3
Prince - Hot Summer.mp3
Sheryl Crow - Summer Day.mp3
Unreasonable Action 038 - Summer Catch-up.mp3
Summer Reading.mp3

 

Is there a way I could modify my MySQL query to display the results with the most matches at the top first?

Edited April 2, 2013 by joecooper

[joecooper]

Another example is "Stand By Me".

 

This is what gets displayed:

 

Tevin Campbell - Stand Out.mp3
Bob Marley - Get Up Stand Up.mp3
Stand Still.mp3
Ben E. King- Stand By Me.mp3

[jcbones]

Since there is way to much to go through about this, I suggest reading and applying the fine tuning guide.  If you still have problems, you can always come back here.

[joecooper]

Thanks, I realised there is a lot to go into about it. I've worked out the basics of what I need.

 

$query = "SELECT * FROM urls WHERE MATCH(file) AGAINST('\"$search_modded\"' IN BOOLEAN MODE);";

It will display the entire word searched at the moment which is enough.

[jcbones]

There are limitations with full text searches.  Like the character limit, default is 4 characters, any word shorter than that, will not be included in the search.  Neither will words that show up in more than 50% of the rows of data searched.  The first reason is why you are getting the results you seen, as "summer" is the only word searched out of that phrase.  I have seen a trick written somewhere, on how to get more of what you are looking for when searching boolean mode.

 

*5 minutes later*

Go here and look at the second user comment posted.  Now it is a bit dated, like 2002, but it still shows up in the new docs, so it may still be relevant. Either way, its worth a shot.

[joecooper]

There are limitations with full text searches.  Like the character limit, default is 4 characters, any word shorter than that, will not be included in the search.  Neither will words that show up in more than 50% of the rows of data searched.  The first reason is why you are getting the results you seen, as "summer" is the only word searched out of that phrase.  I have seen a trick written somewhere, on how to get more of what you are looking for when searching boolean mode.

 

*5 minutes later*

Go here and look at the second user comment posted.  Now it is a bit dated, like 2002, but it still shows up in the new docs, so it may still be relevant. Either way, its worth a shot.

 

Thanks a lot for taking the time to find that post for me. I will have a look into this now. Thanks!!!