Order By Search Relevence?
Hi guys,
I'm searching through a mysql table to find products that match a keyword and using the following SQL query
SELECT name FROM `products` WHERE (name LIKE '%keyword%' OR description LIKE '%keyword%' OR longdescription LIKE '%keyword%' OR metakeywords LIKE '%keyword%') AND (status = 'active')'
What I want to do is order them by relevance. Is their anyway to count the number of times the searchterm occurs and order it by that (preferably without having to use any PHP)?
Any help is muchous appreciated.
Thanks
Andy
Suzanne posted this at 16:01 — 13th May 2004.
She has: 5,507 posts
Joined: Feb 2000
(For FULLTEXT only)
<?php$sql = \"SELECT
*, MATCH (name, description, longdescription,metakeywords)
AGAINST
('$keyword')
AS
score
FROM
`products`
AND
(status = 'active')
ORDER BY score DESC\";
?>
Then you can display the score for any particular match.
andy206uk posted this at 10:37 — 15th May 2004.
He has: 1,758 posts
Joined: Jul 2002
Thanks suzanne! That worked a charm. I had to work out how to add a fulltext index, but once I got that sorted it was plain sailing!
TOP MOD!
andy206uk posted this at 12:58 — 15th May 2004.
He has: 1,758 posts
Joined: Jul 2002
Damn... doesnt seem to work as I expected. Apparently if more than half the results contain the search term then mysql doesnt return any results. Do you know if theres anyway round this?
Andy
Suzanne posted this at 17:06 — 15th May 2004.
She has: 5,507 posts
Joined: Feb 2000
whoa, that's some heavy returns... what are the search terms? i.e. can you sort out for us bystanders what sort of results you're expecting and what you're getting and why and such?
andy206uk posted this at 18:45 — 15th May 2004.
He has: 1,758 posts
Joined: Jul 2002
You can see it in action on my site. ipodx.co.uk
Use the search facility on the site and search for "dock". I would expect a product called the sendstation pocketdock to show in the results (the word dock is mentioned several times in the description).
The product does occur but the score is 0 so doesnt show up and I've put on a filter to stop results with 0 relevence showing (afterall, if it's not relevent, what's the point?)
the SQL is:
$sql = "
SELECT *, MATCH (name,description,longdescription,metakeywords,colours)
AGAINST ('$keyword')
AS score
FROM `products`
WHERE (status = 'active')
AND
(MATCH (name,description,longdescription,metakeywords,colours)
AGAINST ('$keyword') > '0')
ORDER BY `score`
DESC
";
I did try doing it like
$sql = "
SELECT *, MATCH (name,description,longdescription,metakeywords,colours)
AGAINST ('$keyword')
AS score
FROM `products`
WHERE (status = 'active')
AND
`score` > '0')
ORDER BY `score`
DESC
";
However, that threw up an error, so i used the longer code which seemed to work. This problem also occurs if you search for iPod (which should, in theory return EVERY product in the site, but instead gives a 0 result).
Any ideas?
ta!
Andy
Andy
Suzanne posted this at 20:15 — 16th May 2004.
She has: 5,507 posts
Joined: Feb 2000
Very nice looking, btw.
Untested, but this is the idea -- select everything and then return only the parts you need. If this doesn't work, then simply do a conditional statement in php -- if ($score != 0) { // go ahead and return results }. I assume you have some php returning the information anyway?
<?php
$sql = \"
SELECT *, MATCH (name,description,longdescription,metakeywords,colours)
AGAINST ('$keyword')
AS score
FROM `products`
WHERE (status = 'active')
AND (score !=0)
ORDER BY `score`
DESC
\";
?>
andy206uk posted this at 08:11 — 17th May 2004.
He has: 1,758 posts
Joined: Jul 2002
Absolutly. I've had it printing out the scores though, and for some bizzare reason, if you search for "dock" the pocketdock is returned with a zero score, even though the word "dock" is mentioned several times in the description (i've even tried putting "dock dock dock dock dock" in the results to give better results.
If I get chance later today, I'll copy the script online and make it print the scores so you can see what I mean.
I'm stumped.
Andy
Suzanne posted this at 15:15 — 17th May 2004.
She has: 5,507 posts
Joined: Feb 2000
I notice that dock and iPod are four letter words. I know the cut off for FULLTEXT is supposed to be four or more characters, but I'm wondering if you either have an issue in the order of your indexed fields and the match statement or if perhaps the four characters aren't being indexed...
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
http://dev.mysql.com/doc/mysql/en/Fulltext_Restrictions.html
But wait! Apparently you can change the minimums and the stop list!
http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html
Let's explore together, eh? ha!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.