Multiple selections from a listbox into a query

[Speckly]

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??

 

[webmigit]

First...

'form.which_fields'

shouldn't work.. you need to take the form. off there.

And this

= '#form.which_fields#'

should probably be

in (#preservesinglequotes(listqualify(form.which_fields,"'"))#)

assuming the values are like the other post where Ecobb and I commented..

If they are numeric values:

in (#form.which_fields#)

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[Speckly]

Thanks for your reply

...but this doesn't entirely work because I don't know which fields the user wants in the query until they select them from the list box. I.e. in the list box is an option to view 'all fields' of the database in the records that they want or to select multiple field names that they want to view in the records of the database.

If I am the user and select the annual return field this query code works fine and gives the correct output:

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

But...I don't know that they want the AnnualIncome field (they may want another field) until they complete the form on-line. I have so many fields in the database that to write everyone in like this in the query is very repetative and suggests that there must be a bettter way of doing it....hence why I tried to use:

WHERE ManagingAssoc = '#CLIENT.id#' AND 'form.which_fields' = '#form.which_fields#'

in the query.

Can you help?? or is what I'm wanting not poss?

Thanks

 

[webmigit]

Lets get you a list of fields...

<cfquery name="excols" datasource="supporting_peopleTESTDB">
  SELECT [b]top 1[/b] * FROM GENERAL_NEEDS_CLEAN_test
</cfquery>

<cfoutput><cfloop list="#excols.columnList#" index="c">
  #c# <input type="radio" name="whichcol" value="#c#"><br>
</cfloop></cfoutput>

The above will generate a radio button list of the fileds in your table.. You may need to edit the list to remove certain fields.. And of course paste the radio buttons into your form... Here's how you would do it as a select list:

<select name="whichcol"><cfoutput><cfloop list="#excols.columnList#" index="c">
  <option value="#c#">#c#</option>
</cfloop></cfoutput></select>

You may also need to take the bold top 1 out of the statement but that's ok.. because you only need to run the query once to get the list.

Then your problem query would look like this:

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

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.