search & show score

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

search & show score

Post by mudkicker »

Do you know how to do the scoring thing on search results. What's the "point" on it? How can we do. What do we compare?..

Is there any article about it or any idea, will be very appreciated...
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

You just need to make a little function that takes the search phrase and then compaires it against the result. So compair the phrase against the title of a result and compair the letters, giving say 1 point if its the same. Then see how many times you can find the search phrase in the results content, and again add points. Then do some more maths on it to get it to like a % or out of 10.

You could also compair it with other things, such as how helpful a user found the search result and stuff, and then factor all that into the equation.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

If you are using MySQL you can use MATCH to get result ranks ready to go in the query result.

SELECT columnA, columnB, etc, MATCH ( columnA ) AGAINST ( 'foo' ) as rank
FROM table
WHERE MATCH ( columnA ) AGAINST ( 'foo' )>0

However, if you want to make boolean options available (using MATCH IN BOOLEAN MODE), you will have to do some post-processing since IN BOOLEAN MODE can't return ranks in the query result.

Have a good read through the fulltext search pages in the mysql manual: MATCH has a few quirks such as the 50% rule, minimum word length etc. It's specifically designed for searching large databases.
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post by mudkicker »

thanks for your advices..

mcgruff is this a common way what you said. imean is this way used mostly?
and does this one gives the score percentage?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

MySQL match calculates relevance according to its own inscrutable rules. They're designed for searching very large databases so that, if a word is very common, it is regarded as not having any relevance at all. The manual will explain more about fulltext searching better than I can.

The above query will give you a "rank" key in the result set with the relevance value - try it out in phpMyAdmin.
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post by mudkicker »

thanx you helped a lot! i will look @it.
Post Reply