|Pathologically Eclectic Rubbish Lister|
[OT] Weighted Search Resultsby CiceroLove (Monk)
|on May 20, 2004 at 13:13 UTC||Need Help??|
CiceroLove has asked for the wisdom of the Perl Monks concerning the following question:
I am trying to come up with a better search result for an application I build. This application gets resold to clients and they want the option to restrict columns that can be searched on for weighted results. So for instance, let's say my table looks like this (yes, MYSQL):
Obviously, my table is larger than this (by about 85 columns). What I need is to allow the user to provide search terms and then provide a weighted ranking based on how many columns are matched (Thank God, no LIKE operations will be permitted). So if someone wants to search on 'Greg', 32, 'TX', I want it to find all rows that match all three terms and give it 100% weight. Then return all rows with only two of the terms to match and give it 66%, etc. all the way down to a single term.
This application gets sold to clients over and over again so the number of columns that will be searched on is dynamic which means the data types to check are also dynamic. I don't really want to have to retrieve all rows in a long OR operation and process them locally. I am hoping for a more elegant solution (perhaps some hidden/unknown feature in MYSQL?)
All help is of course greatly appreciated
P.S. I searched Google for "Weighted search results MYSQL" and got a bunch of links for search engine rankings. So there's help there possibly but the result set was over 500 pages long. Ack!
Fates! We will know your pleasures: That we shall die, we know; 'Tis but the time, and drawing days out, that men stand upon. - Act III,I, Julius Caesar