multiple selections from listbox into a query

Hi all,

Am getting stuck on how to code the query using multiple selections from a listbox (named which_fields). Have read various threads and can't get what others say to work.

The scenario for this prob is: I want the user to be able to select from a listbox (which contains a list of all the fields in the GENERAL_NEEDS_CLEAN_test table in the database) which fields they want to process in the query. Multiple fields selections are needed. So I have created a listbox (which allows multiple selections) using the following code (note: only the first part of the cfform code shown here but the CFForm tag works fine):

<CFFORM name="DateForm" ACTION="Download.cfm" METHOD="post">

<tr><td><B>Step 1. Select fields to download:</B>
<cfselect name="which_fields" display= "label" required="yes" multiple="YES" size="5">
<option value="All FIELDS" selected="selected">All Fields</option>
<cfoutput><cfloop list="#database_fields.ColumnList#" index="MyColumnName">
<option value="#MyColumnName#">#MyColumnName#</option></cfloop></cfoutput></cfselect></td><td></td></tr>


Then I send the form variables through to the action page using CFForm and use the following query....but when I run the query I get no results.


<cfquery name="export_selected_all_records" datasource="supporting_peopleTESTDB">
SELECT *
FROM GENERAL_NEEDS_CLEAN_test
WHERE ManagingAssoc = '#CLIENT.id#' AND 'form.which_fields' = '#form.which_fields#'
</cfquery>


What am I getting wrong?? It is possible to do what I'm asking in ColdFusion. All the other posts I've read have done slightly different things.

Can anyone advise me??

Of course this is possible. The problem is in your SELECT statement. You probably want to use the IN operator, but I can't tell from your query what database field you want to limit the results by. You probably want something like this:

<cfquery name="export_selected_all_records" datasource="supporting_peopleTESTDB">
SELECT *
FROM GENERAL_NEEDS_CLEAN_test
WHERE ManagingAssoc = '#CLIENT.id#' AND someDatabaseColumn IN (#quotedValueList( form.myMultipleSelectField )#)
</cfquery>

Thanks for your reply.

I'm not sure what you mean by this..."but I can't tell from your query what database field you want to limit the results by"....

I don't know what database fields the user wants from the database. They have to choose it from the list box. The list box allows them to select the 'all fields' option to get all fields from the database or multiple selected fields of their choice. So I don't know what the 'someDatabaseColumn' is (and there may be many requested) until the user is on-line completing the form. I have so many fields in the database that to write each field out in the query seems repetative and therefore there must be a better solution surely??

WHERE ManagingAssoc = '#CLIENT.id#' AND someDatabaseColumn IN (#quotedValueList( form.myMultipleSelectField )#)


Thanks for any help.

So if the user is picking which fields they want to search for, how do you know what value in each field to limit the query by?

Or are you trying to say that you want the result set to only return the columns that the user picked, like this:

<cfquery name="export_selected_all_records" datasource="supporting_peopleTESTDB">
SELECT #form.which_fields#
FROM GENERAL_NEEDS_CLEAN_test
WHERE ManagingAssoc = '#CLIENT.id#'
</cfquery>