One Search String/Multiple Fields

[KYMatt]

Hello! What I am trying to do is create a query page for my users that allows
them to enter one search string (var=#string#) and then click checkboxes that
correspond to the field values that they want to search for the string in
(var=#paramSelect#). Unfortunately I haven't been able to make this work
correctly and was hoping that someone here might be able to help me with this.
So far I have tried the following methods:

My initial method:
SELECT....
FROM....
WHERE #paramSelect# LIKE '%#String#%'

The problem here is that when the query is executed, it is sent to the server
looking like this:

SELECT....
FROM....
WHERE paramSelect1,paramSelect2,paramSelectn LIKE '%"String"%'

Which causes SQL to throw an error because the values in #paramSelect# are
being returned as a list.
What I want it to look like upon execution is:

....
WHERE paramSelect1 LIKE '%#String#%' OR paramSelect2 LIKE '%#String#%' OR
paramSelect3 LIKE '%#String#%'

I have also tried the following method:
....
<cfif FORM.paramSelect IS NOT ""></cfif>
WHERE 1=1
<cfloop list="#FORM.paramSelect#" delimiters="," index="i">
AND <cfqueryparam cfsqltype="cf_sql_varchar" value="'#i#'">
LIKE '%#FORM.String#%'
</cfloop>

But this method returns no records even when I search on only one field.
Does anyone have any ideas of how to do this other than having to use a lot of
<cfif> tags and separate variables like:

WHERE 0=0
<cfif FORM.Taxon IS NOT "">
AND Taxon LIKE '%#Taxon#%'
</cfif>
<cfif FORM.Family IS NOT "">
AND Family LIKE '%#Family#%'
</cfif>
<cfif FORM.Country IS NOT "">
AND Country LIKE '%#Country#%'
</cfif>
<cfif FORM.State IS NOT "">
AND State LIKE '%#State#%'
</cfif>
<cfif FORM.County IS NOT "">
AND County LIKE '%#County#%'
</cfif>

Thank you for any input!

[Dan Bracuk]

In your last method, change the ands to ors. Add brackets. The final results
should resemble:

where 0 = 0
and
(
field1 like '%#string#%'
or field2 like '%#string#%'
etc
)

Expect it to be slow.

[KYMatt]

Dan, thank you for your reply, a couple of questions though: If I use the
method that you are suggesting won't I be eliminating the capability of users
to define which columns they want to search for the string in though? By
looking at your code example it seems that I am telling SQL to search each
column for '%#String#%', when I want it to search only the user-selected
columns for '%#String#%' which will be different for each user. You are
correct in the fact that I would like to change the ANDs to ORs, but my problem
with this has been that my statement gets rejected when someone chooses say
field3 and field4 to search on, resulting in a query looking like:

WHERE OR field3 LIKE '%#String#%' OR field4 LIKE '%#String#%'

which is causing an error because it should look like:

WHERE field3 LIKE '%#String#%' OR field4 LIKE '%#String#%'

[Dan Bracuk]

What happened to the where 0 = 0 part?

[KYMatt]

Dan, I don't think that I am being clear here (or I have completely
misinterpreted what you are trying to say). The problem that I am trying to
overcome right now is having to put each field into the statement on my results
page. Currently the question of using AND or OR is not a big problem for me.
What I DO want to do is pass the variable #paramSelect# which contains the
values from each of the checked checkboxes from my search page. These values
are the field names in my DB. When a user selects a checkbox, the value of
that checkbox is being passed to my output via #paramSelect#, so if one
checkbox is checked then I get #paramSelect# equal to field1. If two or more
checkboxes are checked by the user then the value of #paramSelect# becomes a
list of the values (i.e. field1,field3,field5) - this is the dynamic part that
I am having trouble with. With your suggestion what I am doing is hard-coding
each of the field values into my results page:

AND
(
form.field1 LIKE '%#String#%'
OR form.field2 ....
)
)

when what I want to do is have the values passed from the #paramSelect#
variable, separated from the list, and dynamically inserted into the code. For
example:

say that #paramSelect# is passed and contains the
values field1,field3,field5
I would like to write a loop that will separate
each value contained in #paramSelect#
i.e.(field1) (field3) (field5)
and then build the WHERE from these, allowing me
to place something like my previous example:

<cfif FORM.paramSelect IS NOT
""></cfif>
WHERE 1=1
<cfloop list="#FORM.paramSelect#"
delimiters="," index="i">
AND <cfqueryparam cfsqltype="cf_sql_varchar"
value="'#i#'">
LIKE '%#FORM.String#%'
</cfloop>

The idea when I built that statement was that the values would be separated
when the delimiter was reached (,) stored in the variable #i# and it would loop
through the list contained within #paramSelect#. Unfortunately it doesn't seem
to be doing this because I get 0 records returned even when I search on just 1
field. My main reason for trying to do it this way is the fact that this DB is
going to grow and the available fields are going to change. If I can store all
of this in a variable and pass that to both the SELECT, WHERE, and a dynamic
output table, then it relieves a lot of headaches and time going through and
changing code in the future. Currently I have the dynamic SELECT and output
tables built, but the WHERE part of the SQL statement is causing problems.

Thanks again for your input!

[Dan Bracuk]

So paramselect is either not defined, or a list?
And you know how to loop through a list?
And you know what you want to do if it's not defined?

If you answer yes to all three questions, what's the part you are having
trouble with? By the way, a list can contain only one element and still be
considered a list.

[KYMatt]

This might help too...
When using:

<cfif FORM.paramSelect IS NOT ""></cfif>
WHERE 1=1
<cfloop list="#FORM.paramSelect#" delimiters="," index="i">
AND <cfqueryparam cfsqltype="cf_sql_varchar" value="'#i#'">
LIKE '%#FORM.String#%'
</cfloop>

and looking at the dumped variables, my statement is being processed as ('ken'
was the search string variable passed):

WHERE 0=0 AND ? LIKE '%ken%' AND ? LIKE '%ken%' AND ? LIKE '%ken%'

and my field values from #paramSelect# are reported as SQLPARAMETERS and
listed in an array

[KYMatt]

Alright....I just finished filling out my application for the "village idiot"
job - I think I have this one locked up! My problem was with this code:

AND <cfqueryparam cfsqltype="cf_sql_varchar" value="'#i#'">

which I changed to this

AND #i#

Sometimes the best answers are the easiest. Thanks for your help on this Dan
and I apologize for not trying something so simple sooner.