Hi people,
I reexamined my code and decided to go back to the drawing board and came
up with the code below which seems to be working.
Any other suggestions are still welcomed.
<CFQUERY name="search" datasource="#vacancy#">
SELECT vid, Vaddress, Vcity, Vpostcode, Vcountry,VRestype,
VRmtype,WCType,VRmNoTotal,Prange, Lphone
FROM VacancyDetails, landlrddetails
WHERE VacancyDetails.land_id = landlrddetails.land_id AND 0=0
<!--- 1 For City variable --->
<CFIF #Vcity# IS NOT "">
AND Vcity = '#Vcity#'
</CFIF>
<!--- 2 For postcode variable --->
<CFIF #Vpostcode# IS NOT "">
AND Vpostcode = '#Vpostcode#'
</CFIF>
<!--- 3 For country variable --->
<CFIF #Vcountry# IS NOT "">
AND Vcountry = '#Vcountry#'
</CFIF>
<!--- 4 For Residence type variable --->
<CFIF #VRestype# IS NOT "">
AND VRestype = '#VRestype#'
</CFIF>
<!--- 5 For Room type variable --->
<CFIF #VRmtype# IS NOT "">
AND VRmtype = '#VRmtype#'
</CFIF>
<!--- 6 For WCType variable --->
<CFIF #WCType# IS NOT "">
AND WCType = '#WCType#'
</CFIF>
<!--- 7 For No of rooms available variable --->
<CFIF #VRmNoTotal# IS NOT "">
AND VRmNoTotal = '#VRmNoTotal#'
</CFIF>
<!--- 8 For prange variable --->
<CFIF #Prange# IS NOT "">
AND Prange = '#Prange#'
</CFIF>
<CFSWITCH EXPRESSION=#SOrder#>
<CFCASE VALUE="Prange">
order by Prange
</CFCASE>
<CFCASE VALUE="city">
order by Vcity
</CFCASE>
<CFCASE VALUE="country">
order by Vcountry
</CFCASE>
<CFCASE VALUE="postcode">
order by Vpostcode
</CFCASE>
</CFSWITCH>
</CFQUERY>
------Original Message------
From: morris johnston <[EMAIL PROTECTED]>
To: Cf-Talk <[EMAIL PROTECTED]>
Sent: August 26, 2000 3:50:56 PM GMT
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/[email protected]/
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.
-----------------------------------------------
FREE! The World's Best Email Address @email.com
Reserve your name now at http://www.email.com
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
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.
