Try this:
<cfquery name="getcurr">
SELECT *
FROM curric_topic, curriculum
WHERE NOT ID_FIELD = 0
<!--- insert various conditional SQL statements here --->
AND #pullcurrtopic.topic_field_code# <> 0 AND
curric_topic.curricula_id = curriculum.curricula_id AND
activity_code <> 0
</cfquery>
=========================================
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
=========================================
-----Original Message-----
From: Janice Kepka [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 27, 2001 2:02 PM
To: CF-Talk
Subject: multiple search mess
Hi all!
I'm allowing users to search for water education programs. They are
allowed to search by as many fields as they want, which could be a lot of
fields. So far, I am looping over the submitted list of fields but what
I'm getting is a list of results based on each individual chosen field, not
one list based on all of the chosen fields. I need to somehow dynamically
create the multiple 'AND' statements in the SQL query to get only the
records the user wants. This all works fine if I hard code in all of the
qualifying fields in the SQL but how do I do it dynamically? It seems like
this shouldn't be that difficult...perhaps I've been looking at this too
long.
Any help would be greatly appreciated. Please be specific about
implementation. Sometimes I have a hard time translating the suggestions I
see on the list into implementation.
Here is the code that generates a list for each option.
<cfloop index="ListElement" list="#form.topic#">
<!--- Pulls the field code name from the form-passed info --->
<cfquery name="pullcurrtopic">
SELECT *
FROM topic_option
WHERE topic_field_code = '#ListElement#'
</cfquery>
<!--- Pulls out all of the curriculum that have the above field code
checked in the database. There has to be a join with the curriculum table
in order to get at the name of the curriculum. --->
<cfquery name="getcurr">
SELECT *
FROM curric_topic, curriculum
WHERE #pullcurrtopic.topic_field_code# <> 0 AND
curric_topic.curricula_id = curriculum.curricula_id AND
activity_code <> 0
</cfquery>
<cfoutput query="getcurr">#curricula_name#<br></cfoutput>
</cfloop>
Thanks!
Janice Kepka
Web Coordinator
303 Hiram Smith Hall
1545 Observatory Drive
Madison, WI 53706
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
