Big Query Problem

[parsman]

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=&quot;search&quot; datasource=&quot;#DB#&quot;>
SELECT column1,column2,column3,column4,column5column6,column7,column8
FROM table1

<!--- 1 WHERE Field1 is filled and ALL other fields empty --->
<CFIF #field1# IS NOT &quot;&quot; AND #field2# IS &quot;&quot; AND #field3# IS &quot;&quot; AND #field4# IS &quot;&quot; AND #field5# IS &quot;&quot;
AND #field6# IS &quot;&quot; AND #field7# IS &quot;&quot; AND #field8# IS &quot;&quot;>
WHERE field1 = '#column1#'

<!--- 2 WHERE Field1 AND Field2 are filled and ALL other fields empty --->
<CFIF #field1# IS NOT &quot;&quot; AND #field2# IS NOT &quot;&quot; AND #field3# IS &quot;&quot; AND #field4# IS &quot;&quot; AND #field5# IS &quot;&quot;
AND #field6# IS &quot;&quot; AND #field7# IS &quot;&quot; AND #field8# IS &quot;&quot;>
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

 

[mjones]

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=&quot;search&quot; datasource=&quot;#vacancy#&quot;>
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 &quot;&quot;>
AND Vcity = '#Vcity#'
</CFIF>
<!--- 2 For postcode variable --->
<CFIF #Vpostcode# IS NOT &quot;&quot;>
AND Vpostcode = '#Vpostcode#'
</CFIF>
<!--- 3 For country variable --->
<CFIF #Vcountry# IS NOT &quot;&quot;>
AND Vcountry = '#Vcountry#'
</CFIF>
<!--- 4 For Residence type variable --->
<CFIF #VRestype# IS NOT &quot;&quot;>
AND VRestype = '#VRestype#'
</CFIF>
<!--- 5 For Room type variable --->
<CFIF #VRmtype# IS NOT &quot;&quot;>
AND VRmtype = '#VRmtype#'
</CFIF>
<!--- 6 For WCType variable --->
<CFIF #WCType# IS NOT &quot;&quot;>
AND WCType = '#WCType#'
</CFIF>
<!--- 7 For No of rooms available variable --->
<CFIF #VRmNoTotal# IS NOT &quot;&quot;>
AND VRmNoTotal = '#VRmNoTotal#'
</CFIF>
<!--- 8 For prange variable --->
<CFIF #Prange# IS NOT &quot;&quot;>
AND Prange = '#Prange#'
</CFIF>

<CFSWITCH EXPRESSION=#SOrder#>
<CFCASE VALUE=&quot;Prange&quot;>
order by Prange
</CFCASE>
<CFCASE VALUE=&quot;city&quot;>
order by Vcity
</CFCASE>
<CFCASE VALUE=&quot;country&quot;>
order by Vcountry
</CFCASE>
<CFCASE VALUE=&quot;postcode&quot;>
order by Vpostcode
</CFCASE>
</CFSWITCH>


</CFQUERY>