There is a much simpler way: Something like:
<cfquery name="search" datasource ="db">
Select ID, Descr from table 1
where 1=1
<cfif colum1 is not '''>AND f1 ='#colum1#' </cfif>
<cfif colum2 is not '''>AND f2 ='#colum2#' </cfif>
<cfif colum3 is not '''>AND f3 ='#colum3#' </cfif>
<cfif colum4 is not '''>AND f4 ='#colum4#' </cfif>
</cfquery>
Al Musella
a1webs.com
><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#'
At 08:04 PM 8/26/2000 +0100, you wrote:
>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
------------------------------------------------------------------------------
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.
