2 Replies Latest reply on Mar 1, 2007 2:20 PM by 1CoolDude

    Search Query Help

    1CoolDude
      I'm on CF 5, Windows 2000. I'm trying to build a database search, and I'd love to index the results, but Verity indexing is broken. Our server was hit with a couple of viruses, and the indexing is completely messed up. I think we could simply reinstall the Verity portion, but my boss says that's not going to happen.

      I have a radio button to search by any word, all words or exact phrase. I tried to see what was out there already and follow their advice, but it's not working properly. I'm sure that the queries are not correct.

      A search on any word returns results out of the section. A search on all words brings back nothing. A search on exact term works fine. If you put in multiple search terms, only the last one is returned.

      Any ideas?
        • 1. Re: Search Query Help
          insuractive Level 3
          I think the key here may be your use of parenthesis and the AND/OR keywords:

          SQL evaluates the following WHERE clauses much different:

          EXAMPLE 1
          =================
          WHERE col1='A'
          AND col2 like '%B%'
          OR col3 like '%C%'

          vs

          EXAMPLE 2
          ====================
          WHERE col1='A'
          AND (col2 like '%B%' OR col3 like '%C%')

          Example 2 is probably more like what you want. In addition to that, if you are looping over a number of keywords and you want to match any of them (not all of them) you should probably use the OR keyword inside your loop:

          EXAMPLE 4 (you coldfusion code should output something like this:)
          ===================
          WHERE col1='A'
          AND (
          (col2 like '%iLoopValue1%' OR col3 like '%iLoopValue1%')
          OR (col2 like '%iLoopValue2%' OR col3 like '%iLoopValue2%')
          )

          Hope that helps!
          • 2. Re: Search Query Help
            1CoolDude Level 1
            Thanks, insuractive. You got me thinking about how the queries were written and I fixed it. I still don't know why it was having trouble with a space as a delimiter, but I just replaced the spaces with a pipe like this:
            <CFIF search_type IS NOT "3"><cfset keyword = #Replace(keyword,chr(32),"|","all")# ></CFIF>
            and no problem.