Twitter: raymondcamden


Address: Lafayette, LA, USA

Related selects with multiple options

05-30-2014 3,355 views JavaScript, ColdFusion

A reader asked me a simple Ajax-y type question this morning and I thought I'd turn it into a simple blog post. We've all done, or at least seen, related selects before. That widget where you select something in one drop down and it drives the data in the second drop down. What he was asking is how you would support being able to select multiple items in the first drop down. Here is an example of that. Before I go on, let me say that while the back end of this is built in ColdFusion, it absolutely does not matter what back end you use. Oh, and please do not ask me to build you a PHP version! ;)

Let's begin by building a simple version of related selects. Here's the HTML:

<html>
<head>
    <title>My Page</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    	<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
</head>

<body>

	<form>
		<select id="state">
			<option value="0">-- Select a State --</option>
			<option value="1">California</option>
			<option value="2">Louisiana</option>
			<option value="3">Texas</option>
		</select>
		
		<select id="city">
			<option>-- Select a City --</option>
		</select>
	</form>

<script src="app.js"></script>	
</body>
</html>

Nothing too terribly interesting here, but make note of the two drop downs. The first is for states and the second is for cities. This one will be driven by JavaScript. I was lazy and only typed in three states. Now let's look at the JavaScript.

$(document).ready(function() {
	
	$stateDropdown = $("#state");
	$cityDropdown = $("#city");
	
	$stateDropdown.on("change", function(e) {
		var state = $(this).val();
		//always clear
		$cityDropdown.empty();
		//reinject the default
		$cityDropdown.append("<option>-- Select a City --</option>");
		//if blank, do nothing
		if(state === 0) return;
		$.getJSON("service.cfc?method=getCities", {state:state}).done(function(res) {
			for(var i=0, len=res.length; i<len; i++) {
				$cityDropdown.append("<option>"+res[i].CITY+"</option>");
			}
		});
	});
	
});

Again, nothing too complex here if you've built one of these things before. Notice a change - get the value - and update the related select. Finally, let's look at the server side component. Again, this is ColdFusion, but you could, I hope, see how this would be done in your language of choice. Like Node. Or Node. But, yeah, whatever you prefer:


component {

	//nice defaults
	url.returnformat="json";
	url.queryformat="struct";

	//include a file to create a fake query we can select against
	include "fake.cfm";

	remote query function getCities(required numeric state) {
		return queryExecute("select city from cities where state = :state", {state={value:arguments.state, cfsqltype:'cf_sql_integer'}}, {dbtype:'query'});
	}

}

All this component does is define one method, getCities, that runs a query against a query (a ColdFusion feature that lets you treat an existing query like a database table). The data is simply hard-coded in an external file.

<cfscript>
variables.cities = queryNew("state,city", "integer,varchar", [
	{state:1, city:"San Francisco"},
	{state:1, city:"San Mateo"},
	{state:1, city:"Mountain View"},
	{state:2, city:"Lafayette"},
	{state:2, city:"Lake Charles"},
	{state:2, city:"Catahoula"},
	{state:3, city:"Houston"},
	{state:3, city:"Dallas"},
	{state:3, city:"Fort Worth"}]);
</cfscript>

Ok, so that's it. You could make this a bit more intelligent perhaps with some caching on the client-side, but that's really it. So how do we update this for multiple items?

Well, first, obviously, we add "multiple" to the first drop down. I won't reshare the entire HTML file as it is almost the exact same. The JavaScript is a little bit different though.

$(document).ready(function() {
	
	$stateDropdown = $("#state");
	$cityDropdown = $("#city");
	
	$stateDropdown.on("change", function(e) {
		var state = $(this).val();
		//state is an array, convert it to a list
		state = state.join(",");

		//always clear
		$cityDropdown.empty();
		//reinject the default
		$cityDropdown.append("<option>-- Select a City --</option>");
		//if blank, do nothing
		if(state === 0) return;
		$.getJSON("service.cfc?method=getCities", {state:state}).done(function(res) {
			for(var i=0, len=res.length; i<len; i++) {
				$cityDropdown.append("<option>"+res[i].CITY+"</option>");
			}
		});
	});
	
});

So - the only real difference here is handling the fact that jQuery will return the multiple items as an array. We can easily convert that to a list of values using join. This turns the array into a simple string we can pass to our service. Finally, let's look at the change in the server-side code.

component {

	//nice defaults
	url.returnformat="json";
	url.queryformat="struct";

	//include a file to create a fake query we can select against
	include "fake.cfm";

	remote query function getCities(required string state) {
		return queryExecute("select city from cities where state in (:state)", {state={value:arguments.state, cfsqltype:'cf_sql_integer', list:true}}, {dbtype:'query'});
	}

}

So - a few small changes. First, our method signature went from requiring a number to a string. This lets us accept a value like 1, or 5,6. Next, the query was updated to do a "state in ()" type search. Again, this will work with one value or multiple. And... that's it. I've included a zip of both versions. The ColdFusion code requires the latest version if you want to run it as is.

Download attached file