If u try running the Drill Down Wizard, From CF Studio and look @ the code
it make.
Hope this helps
-----Original Message-----
From: morris johnston [mailto:[EMAIL PROTECTED]]
Sent: 26 August 2000 16:51
To: Cf-Talk
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.
