The Latest...

I recently was working on a web application that included a form with two <select> elements.  One for a category, and the second for a subcategory.   I needed the options in the second element to change depending on what was chosen in the first element.  Some people call these "linked" or "dependent" form fields.

Getting the first element populated was easy.  I just used a query.

<cfquery name="GetCats" datasource="mydsn">
SELECT cat_id, cat_name
FROM categories
ORDER BY cat_name
</cfquery>

Getting the second one populated was of course a little trickier, and is generally done using JavaScript.  But just as there's always "an app for that" with smartphones, there's always "a function for that" in ColdFusion.  In this case, it was the bind attribute of <cfselect>.  Basically you write a component that contains the code you want executed, then refer to that component in the bind attribute.  Like with most things, it's easier to show by example.

First, the form code.

<cfform name="myform" action="#CGI.SCRIPT_NAME#" method="post">
<p>
<label for="cat">Category:</label></br>
<cfselect
        name="cat"
        id="cat"
        query="GetCat"
        value="cat_id"
        display="cat_name"
        selected="#FORM.cat#"
        queryPosition="below">
<option value="0"<cfif FORM.cat eq 0> selected="selected"</cfif>>All</option>
</cfselect></p>

<p><label for="subcat">Sub Category:</label></br>
<cfselect
        name="subcat"
        id="subcat"
        bind="cfc:path.to.my.component.fetchcats.getsubcat({cat})"
        bindonload="True"
        display="subcat_name"
        value="subcat_id"
        selected="#FORM.subcat#">
<option value=""></option>
</cfselect></p>
<p><input type="submit" name="sendcats" value="Submit" /></p>
</cfform>

Here we're binding our second select element to the first using the bind attribute.  The bind attribute calls a CFC that contains a query that gets our subcatrgories based on what's been selected in the first select element.  The CFC contains this:

fetchcats.cfc
<cfcomponent>
    <cffunction name="getsubcat" access="remote" securejson="false" returnformat="json">
    <cfargument name="cat_id" required="true" default="0" />
        <cfquery name="subs" datasource="mydsn">
        SELECT subcat_id, subcat_name
        FROM subcategories
        WHERE parent_id = <cfqueryparam value="#arguments.cat_id#" cfsqltype="cf_sql_numeric">
        ORDER BY subcat_name
        </cfquery>  
    <cfreturn subs>
    </cffunction>
</cfcomponent>

Cool eh?  Well, it falls flat in a couple of spots.  For one, unless you're running CF9 or above, there's no good way to retain the selected option in the second list.  THAT'S annoying!  Second, there is no obvious way to have a "select all" option in the second element, because as soon as you select your category in the first element, the second is overwritten with the options from the query in the CFC.  To me the best way around this was to add an "All" option to the query itself.  It works quite well.

fetchcats.cfc
<cfcomponent>
    <cffunction name="getsubcat" access="remote" securejson="false" returnformat="json">
    <cfargument name="cat_id" required="true" default="0" />
      <cfquery name="subs" datasource="mydsn">
      SELECT subcat_id, subcat_name, 1 AS selectorder
      FROM subcategories
      WHERE parent_id = <cfqueryparam value="#arguments.cat_id#" cfsqltype="cf_sql_numeric">
      UNION
      SELECT 0 AS subcat_id
            , 'All' AS subcat_name
            , 0 AS selectorder
      FROM subcategories
      ORDER BY selectorder, subcat_name
     </cfquery>  
    <cfreturn subs>
    </cffunction>
</cfcomponent>

What I basically did here was to add some fake entries to the result set via a UNION, and a fake column called "selectorder" to ensure that the "All" option always appears first, even if it's not always alphabetically first.

Some people might think that linking two selects is easier to do with JavaScript, but for me it's easier to use CF's built-in toys.  Yeah, cfform isn't the greatest, but sometimes it makes for fast coding.