Skip to main content
O
onan_nimok
Participant
February 8, 2007
Answered

Updating multiple select in form

  • February 8, 2007
  • 5 replies
  • 801 views
I am trying to work out how to get my select drop down in my update form to select the existing selected options and also to display the non-selected items in a form to update the relevant tables.

Here is an example of what I am trying to do.

Say I want to catalogue all my music CDs. I have a table for CD information (title, band, release date etc) called CDs a table for Categories (eg pop, rock, jazz, blues etc) and a Lookup table which stores the CD_ID and the Category_ID that were selected when entering the CD details. As each CD could fit multiple categories the select enables multiple selections for the appropriate categories. eg a CD could fit the Jazz, Progressive and Fusion categories.

So when the CD is added to the database the CD info goes into the CDs table and the CD_ID is then used to populate the Lookup table with the various Category_ID's from the Categories table. This is done with a looping insert statement so that the CD_ID is inserted multiple times for each Category_ID so a number of rows are added to the Lookup table depending on how many Categories were selected when the CD info was being added.

Now the question is: I want to build a form to be able to update the CD information. It needs to have a select drop down box that will display the existing selections and also the other selections from the Categories table so you can add new ones or change the existing selections.

I know this must be a common thing to do but I can't get my head around it. There must be a simple elegant way of doing this. I don't want any options duplicated in the drop down select list, just the existing seleted options and all the other options that weren't selected.

Hope this makes sense.

Would really appreciate some solution with this as I am sure it is a common scenario and I will need to know how to deal with it in the future. I can't seem to find any solutions for this example in the ColdFusion books I have read.

Thanks John

This topic has been closed for replies.
Correct answer onan_nimok
OK I finally did get this to work, thanks Dan for putting me on the right track!!

For some reason I was getting an error on the selected = "selected" part of the code so have included my modified code below as the solution that works for me.

Here are my two querys, I have included the code so as to make life a bit easier for us newbies as so often a small part of the puzzle is missing and it just leaves us stumped. So here it is, I know I'll be using this alot in future.

<cfquery name="rs_cdlist" datasource="MyDB">
SELECT * FROM cdlookup, cds, cdcategories
WHERE cdlookup.cdid = #URL.cdid#
AND cdlookup.cdid = cds.cd_id
AND cdlookup.catid = cdcategories.cat_id
</cfquery>


<cfquery name="rs_categories" datasource="MyDB">
SELECT * FROM cdcategories
ORDER BY category asc
</cfquery>

and here is the multiple select

<form>

<select name="catid" size="10" multiple>
<cfoutput query="rs_categories">
<option value="#cat_id#" <cfif listfind(valuelist(rs_cdlist.catid), cat_id)>selected</cfif>>#category#</option>
</cfoutput>
</select>
</form>

And YES it does work in a Flash form just change the select to a cfselect

<cfselect name="catid" size="10" multiple width="150" label="Category">
<cfoutput query="rs_categories">
<option value="#cat_id#" <cfif listfind(valuelist(rs_cdlist.catid), cat_id)>selected</cfif>>#category#</option>
</cfoutput>
</cfselect>

All the best!!

John

5 replies

A
azadisg
Inspiring
February 11, 2007
post your GetCategories and GetRecord queries SQL here to see if anything is wrong with those...
W
WrapperGuy
Known Participant
February 20, 2007
I apologize for not getting back to you sooner. I did get the code working and here is what I wound up with:

<select name="CategoryName" size="10" multiple="yes">
<cfoutput query="GetCategories">
<option
value = "#Category#"
<cfif FindNoCase(#Category#,#CategoryList#) greater than 0> selected</cfif>>#Category#
</option>
</cfoutput>
</select>

Thanks for the help.

I have another problem that I think is going to be much harder to solve.

Let's say the user has selected one or more categories using the above code. How can I display on the same edit form a list of subcategories that correspond to the selected category (catagories) without first processing the form. Is this possible?

Regards,
Warren
D
Dan_Bracuk
Inspiring
February 11, 2007
selected should be selected="selected", but that might not be the problem. In what way does it not work?
W
WrapperGuy
Known Participant
February 11, 2007
I am having problems implementing the code. Here's what I wrote following a <td> in my form.

<select name="CategoryName" size="10" multiple="yes">
<cfoutput query="GetCategories">
<option
value = "#Category#"
<cfif ListFind(valuelist(GetRecord.CategoryName,","),#Category#)> selected</cfif>>#Category#
</option>
</cfoutput>
</select>

Does anyone have any ideas why this doesn't work? I am hosted on a CF MX7 server.

Thanks,
Warren
O
onan_nimokAuthor
Participant
February 8, 2007
Hey Dan thanks for your quick response, very much appreciated!
I will give this a go.

Do you know if this would also work in a Flash form?

Cheers

John
D
Dan_Bracuk
Inspiring
February 8, 2007
I recommend that you use checkboxes instead of a multi-select for the categories. The reason is that it is simply too easy to accidentally un-select all the pre-selected values.

But if you insist.

Run a query that gives you the select category_ids. You now have a valuelist to work with. Then do something like this:
<cfoutput query="allcategories">
<option
<cfif ListFind(ValueList(otherquery, category_id), category_id) selected = "selected">
value = "#category_id#">#category_name#
O
onan_nimokAuthorCorrect answer
Participant
February 9, 2007
OK I finally did get this to work, thanks Dan for putting me on the right track!!

For some reason I was getting an error on the selected = "selected" part of the code so have included my modified code below as the solution that works for me.

Here are my two querys, I have included the code so as to make life a bit easier for us newbies as so often a small part of the puzzle is missing and it just leaves us stumped. So here it is, I know I'll be using this alot in future.

<cfquery name="rs_cdlist" datasource="MyDB">
SELECT * FROM cdlookup, cds, cdcategories
WHERE cdlookup.cdid = #URL.cdid#
AND cdlookup.cdid = cds.cd_id
AND cdlookup.catid = cdcategories.cat_id
</cfquery>


<cfquery name="rs_categories" datasource="MyDB">
SELECT * FROM cdcategories
ORDER BY category asc
</cfquery>

and here is the multiple select

<form>

<select name="catid" size="10" multiple>
<cfoutput query="rs_categories">
<option value="#cat_id#" <cfif listfind(valuelist(rs_cdlist.catid), cat_id)>selected</cfif>>#category#</option>
</cfoutput>
</select>
</form>

And YES it does work in a Flash form just change the select to a cfselect

<cfselect name="catid" size="10" multiple width="150" label="Category">
<cfoutput query="rs_categories">
<option value="#cat_id#" <cfif listfind(valuelist(rs_cdlist.catid), cat_id)>selected</cfif>>#category#</option>
</cfoutput>
</cfselect>

All the best!!

John
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices