Building a search application from scratch is painful and frustrating, even if you have a database with fulltext index.

Instead I'd recommend to use a module that handles at least some of the gory details for you, for example KinoSearch or Plucene (both at least inspired by Lucene)

What it does for you is building the index, scoring and sorting the result and make them easily accessible for you, and more importantly things like stemming (if the user searches for "batteries" she should also find a page on which only "battery" appears), query parsing (for example separating terms with AND does what you mean) and so on.

What it does not do for you is joining the tables; I don't know enough about the relation between a "resource" and a "product" to meaningfully answer that question for you - maybe when a "resource" matches, it should also display links to all associated products?

In reply to Re: Relevant Searching and Consistent Results Problem by moritz
in thread 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.