Skip to main content
1
1CoolDude
Participant
March 1, 2007
Answered

Search Query Help

  • March 1, 2007
  • 1 reply
  • 247 views
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?
This topic has been closed for replies.
Correct answer 1CoolDude
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.

1 reply

I
insuractive
Inspiring
March 1, 2007
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!
1
1CoolDudeAuthorCorrect answer
Participant
March 1, 2007
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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices