The old way of creating two relational select statements involved JavaScript and the more items that were in the select statements, the longer the arrays and the slower the page. Every possible combination was programmed into the JavaScript array which meant that the client browser had to receive every item from the total sum of both select statements. If you used ColdFusion to shorten the programming for the two selects you only made it easy for yourself, the client browser still had to be stuffed.
With Ajax, we have a better way of conducting this business. The second select statement is only built after the first selection is made. This reduces the amount of information that the client browser must have and simplifies both the content and the ColdFusion source document.
Normally, I would just build an Ajax lookup into the same page (especially if it was the only place I was going to use it), the result would be escaped by placing it at the top of the page and using CFABORT at its conclusion. The two selects would also be populated by two queries, but to give you real data to test this procedure with I am performing a query of queries in the second select.
To start we build our first pseudo query, naturally you wouldn't hardcode into the page like this but instead pull from a datasource. This method is only to give you a working example.
<cfscript>
GetColor=QueryNew("ColorID, ColorName");
QueryAddRow(GetColor, 2);
QuerySetCell(GetColor, "ColorID", 1, 1);
QuerySetCell(GetColor, "ColorName", "Red", 1);
QuerySetCell(GetColor, "ColorID", 2, 2);
QuerySetCell(GetColor, "ColorName", "Blue", 2);
</cfscript>Next wrap the query around the OPTION of a SELECT tag to build your first decision model. As a result of changing the option, a CFDIV will be bound to a page to drive the contents of the second decision model. In this example I am sending it back to the same page using the CGI.SCRIPT_NAME substitution.
<select name="SelectColor">
<cfoutput query="GetColor">
<option value="#GetColor.ColorID#">#GetColor.ColorName#</option>
</cfoutput>
</select>
<cfdiv bind="URL:#CGI.SCRIPT_NAME#?ColorID={SelectColor@change}" />The first decision model presents the viewer with two color choices; red and blue. From that decision, a second decision model is formed with choices of physical objects that possess that color choice. At the top of this same page, check for the existence of the ColorID URL variable as passed by the CFDIV BIND change. In this example we are hardcoding a second query, but for practical use you would be referencing a real data source and not using a query of queries. Using the results of the URL filtered second query you build a second SELECT tag with relational OPTION values based on the first decision model.
<cfif StructKeyExists(URL, "ColorID")>
<cfscript>
GetItem=QueryNew("ItemID, ItemName, ColorID");
QueryAddRow(GetItem, 4);
QuerySetCell(GetItem, "ItemID", 1, 1);
QuerySetCell(GetItem, "ItemName", "Apple", 1);
QuerySetCell(GetItem, "ColorID", 1, 1);
QuerySetCell(GetItem, "ItemID", 2, 2);
QuerySetCell(GetItem, "ItemName", "Rose", 2);
QuerySetCell(GetItem, "ColorID", 1, 2);
QuerySetCell(GetItem, "ItemID", 3, 3);
QuerySetCell(GetItem, "ItemName", "Sky", 3);
QuerySetCell(GetItem, "ColorID", 2, 3);
QuerySetCell(GetItem, "ItemID", 4, 4);
QuerySetCell(GetItem, "ItemName", "Ocean", 4);
QuerySetCell(GetItem, "ColorID", 2, 4);
</cfscript>
<cfquery name="GetItem" dbtype="query">
SELECT ItemID, ItemName
FROM GetItem
WHERE ColorID = <cfqueryparam value="#URL.ColorID#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<select name="SelectItem">
<cfoutput query="GetItem">
<option value="#GetItem.ItemID#">#GetItem.ItemName#</option>
</cfoutput>
</select>
<cfabort>
</cfif>This same model could be chained together to give you an unlimited number of relational selects, not just two. Each successive CFDIV would have to reside in the previous page. So, for a third relational select we would put a CFDIV on the same page as the SELECT that drives the change.
If you find this post useful please leave a comment and let me know how you used the information.
