Hello all,

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):


name varchar(50)
age int
city varchar(50)
state char(2)
lastmoved date

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!



CiceroLove
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

In reply to [OT] Weighted Search Results by CiceroLove

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.