Running a query with multiple lists

[brokerandy25]

Any help would be so appreciated, I have beat my head against the wall on this
one and have scoured the web trying to find an answer. I am trying to build an
email program for a client that will allow them to issue press releases. The
trick is that they need to be able not only to select multiple counties in the
state, but then they need to select what type of media they send it to (also
multiple media oultets). NOTE my state has 95 counties, and there are 3 press
outlets, web, print and radio/ tv. I can use radio buttons for the press
outlets, and build a query using the ifDefined then and use OR in the query,
but how can I pass a list to a query? I think that the query should look
something like this <cfquery name'PressRelease' datasource='name'> SELECT
Name, EmailAddress From PressReleaseEmail WHERE County = XXXXXXXXXXXX AND
MediaType = <cfif isDefined ('Form.Print')> '#Form.Print#' <else> NULL
</cfif> <cfif isDefined ('Form.WEB')> OR '#FORM.WEB#' <esle> NULL </cfif> <cfif
isDefined ('Form.Radio')> OR '#Form.Radio#' <else> NULL </cfif> So the query
can be dynamically built from the radio buttons. but how does do I pass the
list from FORM.County into the query? All help will be wonderful. Andy

[Maneesh Tikkiwal]

Hope it helps. I m pretty sure it will work if u play around a bit, in case of
any errors.:beer;

<!--- assuming u are using multiple select box which names Counties
<cfselect name="Counties" multiple="Yes">
<cfoutput query="allCounties">
<option value="countyname">#countyname#</option>
</cfoutput>
</cfselect>

--->
<!--- when u submit the form above, u will have a values (as list) in
form.Counties
which should look like this a,b,c,d,e,f u can check it by using
<cfoutput>#form.Counties#</cfoutput> --->

<!--- please note the variable below 'CountyName', will become the list
element. so, it will run the query
and compare the value one by one, eg. first a, then b, then c and so on. --->
<cfloop index="CountyName"
list="#form.Counties#">

<cfquery name"PressRelease" datasource="name">
SELECT Name, EmailAddress From PressReleaseEmail WHERE
County = #CountyName# AND MediaType =
<cfif isDefined ('Form.Print')>
'#Form.Print#'
<else>
NULL
</cfif>
<cfif isDefined ('Form.WEB')>
OR '#FORM.WEB#'
<esle>
NULL
</cfif>
<cfif isDefined ('Form.Radio')>
OR '#Form.Radio#'
<else>
NULL
</cfif>
</cfquery>
</cfloop>

[brokerandy25]

I knew I had to loop through the query, just couldn't figure out the syntax,
you are a life saver. Some times the simplest answers are the hardest to find
.. . . . . do I have to use the cfselect tag or can I use the html select tag?

[Maneesh Tikkiwal]

no worries.

it doesn't matter what u use, cfselect or html select. both will give u the desired result.

[Adam]

CFLOOP surrounding the query is kind of inefficent.

What about using SQL's IN statement which repsents an OR list
You could do this

<!---- surrounds each country with single quote for SQL ---->
<cfset lstCountries = chr(34) & Replace(FORM.Countries, ',', chr(34) & ","
chr(34), All) & chr(34) >

<cfquery name"PressRelease" datasource="name">
SELECT Name, EmailAddress From PressReleaseEmail
WHERE County IN(#PreserveSingleQuotes(lstCountries)#)

AND MediaType =
<cfif isDefined ('Form.Print')> '#Form.Print#' <else> NULL </cfif>
<cfif isDefined ('Form.WEB')> OR '#FORM.WEB#' <esle> NULL </cfif>
<cfif isDefined ('Form.Radio')> OR '#Form.Radio#' <else> NULL </cfif>
</cfquery>

I think ASCII code 34 is the ' sign, this works in SQL Server, don't do much
with Access but it should support the IN command and you would of course use
double quotes.

This way you don't potentially open 95 select statements on the server.

"Maneesh Tikkiwal" <webforumsuser@macromedia.com> wrote in message
news:d48hon$i5o$1@forums.macromedia.com...

> no worries.
>
> it doesn't matter what u use, cfselect or html select. both will give u
> the desired result.