Cold Fusion CFSELECT binding question

I am currently working on developing a photo album for our local volunteer fire department (www.soundbeachfire.org). I am getting hung up on a cfselect form binding on the applications admin page. Essentially, I have two drop down lists.

DROP DOWN LIST 1 (CATEGORIES): This list contains a list of categories and sub-categories for the album. I am limiting the amount of subcategories to 4 levels deep. When the page renders, it displays the categories in a fashion such as:

-- Please Select --
Category 1
Category 2
... Sub Category A
... Sub Category B
...... Sub Category B1
...... Sub Category B2

The Query for the above is as follows:

<cfquery name="getCategories" datasource="DSN">
select L0.catid AS L0_catid
       , L0.title as L0_title
     , L1.catid AS L1_catid
     , L1.title as L1_title
     , L2.catid as L2_catid
     , L2.title as L2_title
     , L3.catid as L3_catid
     , L3.title as L3_title
  from t_media_cats as L0
left outer
  join t_media_cats as L1
    on L1.parent = L0.catid
left outer
  join t_media_cats as L2
    on L2.parent = L1.catid
left outer
  join t_media_cats as L3
    on L3.parent = L2.catid
 where L0.parent is null
order by L0_title
     , L1_title
     , L2_title
     , L3_title
</cfquery>

On the HTML side, the select statement is (I cut down the sub-categories to just 3 levels):

<cfselect name="CatID" id="CatID"">
          <cfoutput query="getCategories" group="L0_title">
            <option value="#getCategories.L0_catid#">#getCategories.L0_title#</option>
            <cfif getCategories.L1_catid NEQ ''>
            <cfoutput>
            <option value="#getCategories.L1_catid#">... #getCategories.L1_title#</option>
            </cfoutput>
            </cfif>
            <cfif getCategories.L2_catid NEQ ''>
            <cfoutput>
            <option value="#getCategories.L2_catid#">... #getCategories.L2_title#</option>
            </cfoutput>
            </cfif>
          </cfoutput>
        </cfselect>

** I removed any of the binding attempts to leave clean code that shows the working model without the binding **

DROP DOWN LIST 2 (ALBUMS): This is where I am now stuck. The second drop down lost contains a list of all of the albums in a specific category. I want to bind this cfselect to the previous list so that only the albums in a selected category appear.

The query for this is:

<cfquery name="getAlbums" datasource="DSN">
SELECT albumid, title
FROM t_media_albums
ORDER BY title ASC
</cfquery>

THE PROBLEM

I am able to bind a simple query (one level of categories) from the first drop down list but can not figure out a way to create the bind using the method of displaying sub-categories as I have designed. I have tried a few different solutions but am unable to come up with a way to fix the problem.

I appreciate any help and feedback.
SiriusPhilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SidFishesCommented:
If I understand what you want, you should try something like




 
<!--- get top level --->
<cfquery name="getL1"...>
	select LID, Ldesc from tbl1 where LID = "L1"
</cfquery>
 
<cfloop query="getL1">
<!--- loop over top level output desc --->
#lDesc#
	
	<!--- get second level values using level one id's --->
	<cfquery name="getL2"...>
		select LID from tbl2 where LID = "#getL1.LID#"
	</cfquery>
 
	<cfloop query="getL2">
		<!--- loop over second  level output desc --->
		- #lDesc#
	</cfloop>
		<!---  rinse - repeat --->
</cfloop>
 
this assumes a table structure like
 
tbl1
 
|LID|LDesc|
 
tbl2
 
|L2ID|L2Desc|LID| (where LID is the FK for tbl1)
 
sample data
tbl1
|1|TLItem1|
|2|TLItem2|
|3|TLItem3|
 
tbl2
 
|1|SLItem1|1|
|2|SLItem2|1|
|3|SLItem3|2|
|4|SLItem1|2|
|5|SLItem2|2|
|6|SLItem3|2|
|7|SLItem1|3|
 
would product a menu
 
TLItem1
    SLItem1
    SLItem2
TLItem2
    SLItem1
    SLItem2
	SLItem3
TLItem3
    SLItem1
	
		Make sense??

Open in new window

0
SiriusPhilAuthor Commented:
SidFishes, I am alble to loop through the values and return the information I need within the first cfselect tag. The problem I am having, however, is binding the second cfselect tag to the first. The second cfselect tag should dynamically change when a value is selected in the first cfselect.
0
SidFishesCommented:
ok, i wasn't clear on what you needed...

to do that I think you will need to use some ajax...what version of cf are you using??
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

SiriusPhilAuthor Commented:
We are running the latest, 8.
0
chair9designCommented:
Here is a quick example that is probably a bit too simple for your solution but it should work nicely for anyone who is curious on binding two select boxes to each other. In this case, a hypothetical table of categories and a table of albums. Also there is a little javascript function included that selects the correct item in a bound drop down in the event of an edit form or a form validation error. This maintains the user's selection in the bound drop down list. Simply change the FORM.CatID to whatever variable you want to use (beanObj.getCatID(), getCat.CatID etc...) and move the ajax proxy wherever you like to make the selection work for you.

Hope this helps someone out.

tim
<!--- =start boundSelects.cfm --->
    <!--- example javascript function to maintain selection in a bound dropdown using cfajaxproxy--->
    <cfif FORM.Submit>
      	<cfajaxproxy bind="javascript:selectDropDown({CatID},#FORM.CatID#)">
   </cfif>
   <!--- =maintain selection in a bound select box by calling a javascript function and cfajaxproxy --->
   <!--- =duplicat this javascript function and the ajaxproxy to maintain the selection for multiple bound dropdowns --->
    <script type='text/javascript'>
	var hasRun = false;
	function selectDropDown(x,val) {
		if(!hasRun) {
			var dd = document.getElementById('CatID');
			for(var i = 0; i < dd.length; i++){
				if(dd.options[i].value == val){
					dd.selectedIndex = i;
				}
			}
		}
		hasRun = true;
	}		
	</script>
 
<!--- cfc bound to must return a two dimensonal array---> 
<!--- bind path is assuming cfc is in same directory as the form --->
<cfform>
Categories:<cfselect name="CatID" id="CatID" 
          bind="cfc:categories.getCategoriesBindable()" bindonload="true" />
 
<!--- now bind to the above selected value to return only albums based on your categories in the drop down 1 --->
Albums:<cfselect name="Albums" id="Albums" bind="cfc:categories.getAlbumsByCategory({Cat})" />
</cfform>
<!--- =end boundSelects.cfm --->
 
<!--- =start categories.cfc file --->
<!--- =CFC= create the categories.cfc and change cfc paths in form binds depending on where the cfc is located --->
<!--- the cfc for categories might look like this --->
<cfcomponent output="false">
 
<cffunction name="getCategoriesBindable" output="false" access="remote" returntype="array">
	<cfset var q="">
	<cfset var result=ArrayNew(2)>
	<cfset var i=0>
		
	<cfquery name="q" datasource="yourDSNHere">
		SELECT CatID, CatName
		FROM categories
		WHERE 0=0  
	</cfquery>
 
	<!--- Convert results to array --->
	<cfloop index="i" from="1" to="#q.RecordCount#">
		<cfset result[i][1]=q.CatID[i]>
		<cfset result[i][2]=q.CatName[i]>
	</cfloop>
			
	<cfreturn result>
		
</cffunction>
 
<cffunction name="getAlbumsByCategory" output="false" access="remote" returntype="array">
	<cfargument name="CatID" type="numeric" required="true" hint="Limit Albums by CatID argument">
	<cfset var q="">
	<cfset var result=ArrayNew(2)>
	<cfset var i=0>
		
	<cfquery name="q" datasource="yourDSNHere">
		SELECT AlbumID, AlbumName
		FROM albums
		WHERE 0=0
                AND CatID = <cfqueryparam value="#ARGUMENTS.CatID#" cfsqltype="CF_SQL_INTEGER">  
	</cfquery>
 
	<!--- Convert results to array --->
	<cfloop index="i" from="1" to="#q.RecordCount#">
		<cfset result[i][1]=q.AlbumID[i]>
		<cfset result[i][2]=q.AlbumName[i]>
	</cfloop>
			
	<cfreturn result>
		
</cffunction>
</cfcomponent>
<!--- =end boundSelects.cfm --->
 
	

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
raveonCommented:
chair9design your code may be just the thing I'm looking for.... how can a direct a detailed question at you directly? any idea?
0
chair9designCommented:
raveon..sorry i posted and didn't realize you had another question....just post it here and I'll be sure to reply. That javascript function works like a charm. if you  need to maintain selections in more than one drop down simply duplicate the javascript function and name that selectDropDown2 etc... not perfect but It works great for me.
0
chair9designCommented:
raveon..sorry i posted and didn't realize you had another question....just post it here and I'll be sure to reply. That javascript function works like a charm. if you  need to maintain selections in more than one drop down simply duplicate the javascript function and name that selectDropDown2 etc... not perfect but It works great for me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.