I'm looking for a little nudge in a new direction on this one. The issue involves creating a web based search form that returns results a user would expect and find useful.

In a nutshell I have three tables "products", "resources" and a table which joins the two. This allows me to associate a resource to several products.

So, for example, the product table has a name and description fields, the resource table has a name, description and content fields (all text fields). If the user searches for the word "wigit", I would want to search the resource name, resource description, product name, product description, and product content. Then return some relevant, consistent results. Sounds simple enough. But my solutions tend to bloat into messiness and I am just not happy with the end results.

I have used MySQL's fulltext indexing with some success. But, it has it's limitations, one being it's not good with single word queries. So, I have created a hybrid method. If the query consists of several words, I use fulltext, if not, I use wild cards. The problem with this is inconsistent (displayed) results, with fulltext I can use the score to sort by relevance, with wild cards of course I cannot (I end up sorting by product or resource name).

Another issue is deciding how to join the tables and how they should be grouped. For example, if "wigit" matched a resource name, I would want to join the products on the resource table (to show which products are associated to that resource).

I added an autocomplete function to the searching that has helped a bit. But, since adding that I have dropped fulltext completely.

Hopefully, I have explained the problem enough. I am mainly looking for ideas or nudges to get me looking in different directions and other options to consider. Maybe ways you have approached this problem. I am open to any feed back.

Thanks for reading and offering any ideas/thoughts you have.

In reply to Relevant Searching and Consistent Results Problem by Rodster001

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.