Link to home
Start Free TrialLog in
Avatar of SiriusPhil
SiriusPhil

asked on

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.
Avatar of SidFishes
SidFishes
Flag of Canada image

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

Avatar of SiriusPhil
SiriusPhil

ASKER

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.
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??
We are running the latest, 8.
ASKER CERTIFIED SOLUTION
Avatar of chair9design
chair9design

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
chair9design your code may be just the thing I'm looking for.... how can a direct a detailed question at you directly? any idea?
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.
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.