You're doing too much coding. Let ColdFusion do the work for you. Try
this:
<CFQUERY NAME="Search" DATABASE="myDataBase">
SELECT *
FROM myTable
WHERE 0 = 0
<CFLOOP INDEX=ThisField LIST="#FORM.FIELDNAMES#">
<CFIF #Evaluate(ThisField)# IS NOT "">
AND #ThisField# = #Evaluate(ThisField)#
</CFIF>
</CFLOOP>
</CFQUERY>
This is untested, just off the top of my head, but should be close.
Dave
----- Original Message -----
From: morris johnston <[EMAIL PROTECTED]>
To: Cf-Talk <[EMAIL PROTECTED]>
Sent: Saturday, August 26, 2000 11:50 AM
Subject: Big Search Query problem!
> Hi people,
>
> I attempting to build a search facility on my site to search a database.
> At the moment I have a 'search' form consisting of 8 fields
> (6 select drop down menu choice + 2 text input).
> The user is allowed to select / input any number of fields for the search
> criteria.
> The values are passed to a action template where I have a query to the
> database with the following format:
>
> <CFQUERY name="search" datasource="#DB#">
> SELECT column1,column2,column3,column4,column5column6,column7,column8
> FROM table1
>
> <!--- 1 WHERE Field1 is filled and ALL other fields empty --->
> <CFIF #field1# IS NOT "" AND #field2# IS "" AND #field3# IS "" AND
#field4#
> IS "" AND #field5# IS ""
> AND #field6# IS "" AND #field7# IS "" AND #field8# IS "">
> WHERE field1 = '#column1#'
>
> <!--- 2 WHERE Field1 AND Field2 are filled and ALL other fields empty --->
> <CFIF #field1# IS NOT "" AND #field2# IS NOT "" AND #field3# IS "" AND
> #field4# IS "" AND #field5# IS ""
> AND #field6# IS "" AND #field7# IS "" AND #field8# IS "">
> WHERE field1 = '#column1#' AND field2 = '#column2#'
>
> etc...
>
> I have tried to code as many CFELSEIF statements for all the possibilities
> of field combinations that a user may select and the related fields which
> they don't select.
> This is obviously very inefficient coding and I continue to find bugs
> whenever I choose a combination of fields that I have not taken account of
> in the above CFESLEIF statements.
>
> Are there any suggestions of an alternative to the above CFELSEIF method
to
> deal with the possibility of a search criteria of anything between 1 to 8
> fields??? perhaps by putting the field values selected into a List/ Array
> and looping through it with a SQL statement to look for records where
Column
> name(s) = field name(s) (both have identical names) ignoring columns that
> have not been selected in the
> search form?????
>
> If all this doesn't make sense let me know and I'll try to explain it
better
> :)
>
> Regards.
>
> Parsman
>
>
> -----------------------------------------------
> FREE! The World's Best Email Address @email.com
> Reserve your name now at http://www.email.com
>
>
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
