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.
Start Free Trial