This seems to be a very common requirement for me so I'm sure someone can
tell me how to do this most efficiently.
I have pages that can be in one or more sections. I have a Pages table and I
have a Sections table. They are joined with a join table called
Page_Sections. I want to enable a HTML SELECT element with multiple
selections enabled to update the page assignments. What I have been doing is
shown below, and it seems messy. Basically aside from the Query grabbing the
individual page info, I do separate queries to populate the dropdown and a
query of the join table to see which section names the page is in so I can
pre-select them.
Trouble is now i have an edit form with several of these many-to-many
relationships and I'd have a whole bunch of these little queries if I kept
doing it this way. Anyone have a better suggestion?
Thanks
John Venable
<!--- Get all sections --->
<cfquery name="qSections" datasource="content" dbtype="OLEDB">
SELECT section_id, Section_Name
FROM ap_Sections
ORDER BY section_name
</cfquery>
<!--- Get all sections for this page --->
<cfif NOT newrecord>
<cfquery name="qPageSections" datasource="content" dbtype="OLEDB">
SELECT section_id
FROM Page_Sections
WHERE page_id = #pageid#
</cfquery>
<cfset sectionsForThisPage = ValueList(qPageSections.section_id)>
</cfif>
<!--- Build a select element of all sections, pre-selecting the sections for
this page --->
<cfif NOT newrecord>
<select name="section" size="25" multiple>
<cfoutput query="qSections">
<option value="#section_id#" <cfif
ListFind(sectionsForThisPage,section_id)>selected</cfif>>#section_name#</opt
ion>
</cfoutput>
</select>
<cfelse>
<select name="section" size="25" multiple>
<cfoutput query="qSections">
<option value="#section_id#">#section_name#</option>
</cfoutput>
</select>
</cfif>
---
John Venable
Director of Web Architecture
Epilepsy Foundation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
