Parent – Child Select Lists Revisited: Multiple Parent – Child Select Lists Via PHP, MySQL And jQuery

A while ago, I promised to answer Brian’s request for a demonstration of how to make multiple parent / child select lists — in other words, starting from one drop down / select list, having two or more child lists, each of which, in turn, may act as a parent to another list.

Multiple parent-child select lists are considerably more complicated to program than a single parent-child relationship. Not only do we have additional data relationships to consider (that is, how we’re going to tie child list values to the selected parent values), we now need to plan for what to do if a “middle” relationship is changed (more on this shortly).

Fortunately, we have a starting point in my original parent-child select list post. We don’t need to reinvent the wheel, therefore, so much as we need to upgrade from a horse cart to a Lamborghini.

Overview Of The Approach

As was the case when we had a single child drop-down list, we must begin with relational data for each select list. Apologies to those who consider that obvious, but for n00bs, what I mean is, if you want to select a value from List A and have List B populated with new values, then the values you intend to have in List B must somehow be keyed (linked) to the selection made in List A.

This multiple parent-child select list approach will work for as many parent-nee-child lists you want. If you want to populate 10 or 100 or 1,000 lists, you won’t need to change a single line of JavaScript; however, your PHP “helper” page will need some modification to accommodate all the queries you’ll need, and the more lists you have, the more code you’ll have to put into your HTML (more on this shortly).

For the purposes of this demo, I am going to use the geocoded ZIP Code data I picked up for my post titled, “Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via PHP And MySQL.” I will therefore have four select lists: A parent list of states; a parent-nee-child list of cities within that state; a child list of ZIP Codes for the selected city; and a fourth list that reflects the coordinates of the ZIP Code.

In truth, there will never be more than one set of coordinates for any given ZIP Code, so this isn’t technically a four-list parent-child chain. But I wanted to demonstrate how this solution works for any number of lists you may choose to have, and I’m a bit too lazy to find, upload and clean up new data for this posting alone.

In the case of the data I am using, state, city and ZIP Code are all columns in the same table; the coordinates are a concatenation of longitude and latitude columns. Therefore, this discussion will focus primarily on data in which your select lists represent columns in the same table / view / query results set.

If you have truly relational data — that is, your select list values exist in unique tables / views that are keyed to each other — this approach will still work, but the narrative on programming the PHP “helper page” will be somewhat irrelevant. If there is interest expressed in approaching this problem with truly relational data, I may expand on this post.

Otherwise, the basic ideas here are exactly the same as those I presented in my original parent-child select list post and my follow-up post on validation. This article effectively summarizes those posts and ties them all together into a single solution.

HTML Modifications

To accommodate the hybrid parent-child select lists, we need to add each select list that will be part of our parent-child chain to a common class. We do this not for the purposes of CSS, which is why most people assign an element to a class, but rather to help jQuery pick out of our form all the select lists in the parent-child chain.

In theory, we could have jQuery simply get all select lists, but your form may have select lists that aren’t part of your parent-child chain. Using a class selector allows us to ensure we only deal with drop down lists that are in the chain.

Each parent select list is going to take on a modified onchange event handler. In this case, the handler will take as its first argument the ID of the target select list — that is, the select we intend to populate with new options. The second argument will be the ID of the invoking select list — that is, the immediate parent of the list that is being populated. Those arguments will be followed by the IDs of every other select that is a parent to the target list.

It is important to get the first two arguments in the correct order, for reasons that will be explained shortly. In other words, the first argument to the bindList() function must always be the ID of the target list, and the second argument must always be the ID of the list sending the request. The remaining parent list arguments can be in any order you like.

<select id="state" name="state" class="ms" onchange="bindList('city', 'state')">
<select id="city" name="city" class="ms" disabled="disabled" onchange="bindList('zip', 'city', 'state')">
<select id="zip" name="zip" class="ms" disabled="disabled" onchange="bindList('lat', 'zip', 'city', 'state')">
<select id="lat" name="lat" class="ms" disabled="disabled">

Let’s take a closer look at how we are invoking bindList():

Line 1, the select list declaration for our “master parent” list, passes to the bindList() function as its first argument the ID of the list it is populating — namely, city — and its second argument is its own ID.

Line 2, a parent-nee-child list, uses as its first argument the ID of the select it will populate (zip), then its own ID (city), and then the name of its parent (state).

Line 3, another parent-nee-child list, first uses the ID of the list it will populate, lat; then its own ID; then all the IDs of its parent and grandparents (city and state).

Line 4, a child list, needs no event handler, because it does not populate any other select.

Some people don’t like to write event handlers directly into HTML code; you may have team development or abstraction concerns. You could, if you wanted, wire up these handlers using jQuery’s $(document).ready() method; I’ve chosen to do it in HTML in order to make things simpler for newer programmers. I discuss wiring up event handlers in jQuery in another post.

All four select lists bear the class ms. You can use this class to apply CSS styling if you want; or, you can make it one of several css class declarations for the select lists in your chain. If you use multiple classes, it doesn’t matter which order they appear, at least not for this solution.

The bindList() Function

The first consideration we have when populating multiple select lists is that we need at least two arguments: The ID of the list to be bound, and the ID of the list calling the function. In other words, the minimum number of arguments that must be passed to bindList() is 2, but will often be more.

Again, the first argument must be the ID of the target list, and the second argument must be the ID of the list calling the function. And again, you must pass as arguments to bindList() the IDs of all grandparent lists, too.

function bindList() {
	if(arguments.length < 2) {
		alert('You did not provide enough arguments to the bindList() function');
	}

For more help on using the arguments array, check out this post.

The clearChildLists() Function

Assured that we have at least the minimum number of arguments, we next want to depopulate all child lists of the sending select list. We’ll do that via a function named clearChildLists(), which takes as its arguments whatever arguments were sent to the bindList() function.

clearChildLists.apply(this, arguments);

The clearChildLists function places every list ID in the parent-child chain into an array, using the each() method of jQuery. We have to reassign these elements to a standard JavaScript array because as of this writing, due to the way jQuery works, we can’t call the arguments array side-by-side with jQuery element notation (I’m sure this will be cleared up at some point).

We’ll use nested for loops to first traverse through all the select lists in the parent-child chain, then check their IDs against the ID list sent to this function by bindList(). And select list not in the arguments list will then be appended to a new array, which holds the IDs of child lists. (This is in large part why we need to pass all parent and grandparent ID lists to the bindList() function).

Finally, we will then clear the options in every child list, and set them to have a single, common message, instructing us to select valid parent values.

function clearChildLists() {
	//function clears values of all lists in parent-child chain that are not sent as arguments

	var lists = new Array();
	var children = new Array();
	var s;
	var tmp;

	//add all parent-child chain lists to array
	$(".ms").each(function() {
		lists.push(this.id);
	});

	//check every parent-child chain select ...
	for(var i = 0; i < lists.length; i++) {
		tmp = false;
		//against the invoking select list and its parents ...
		for(var x = 0; x < arguments.length; x++) {
			//if the chain list is the invoking list or its parent(s), stop
			if(arguments[x] == lists[i]) {
				tmp = true;
				break;
			}
		}
		//if the chain list is not the invoking list or its parent(s), it's a child of the invoking list
		if(!tmp) {
			children.push(lists[i]);
		}
	}

	//if there are any child lists to the invoking list, clear them
	if(children.length > 0) {
		for(var a = 0; a < children.length; a++) {
			addParentSelectMessage(children[a]);
		}
	}
}

function addParentSelectMessage(obj) {
	var s = document.getElementById(obj);
	//remove all options from list
	s.options.length = 0;
	//add option indicating a valid parent value is needed
	s.options[0] = new Option('Select a valid parent value ...', '');
	//disable the list
	s.disabled = true;
}

Note that I could have used splice() at line 21, instead of a Boolean and new array, to remove parent select list IDs from the parent-child chain. I have sacrificed some elegance here in order to be more obvious about what is going on inside the function.

We clear the all child list values to protect against a “middle” list change.

For example, suppose a user goes though this demo and correctly selects a state, city, ZIP Code and coordinates. He then changes the value of the state list.

That would cause the city list to repopulate, but the previously selected ZIP Code and coordinates lists would retain their values. Those values will be wrong, and we want to indicate as much to the end user.

Building The Query String

With the children now clear of values, we can begin building the URL to the helper page. And the way we’re going to do that is twofold.

First, we want to ensure we have a correct selected value from the sending select list — if the value isn’t right, we don’t want to invoke an AJAX request that’s going to crap out on us for want of proper values.

If we do have a legitimate sending value, then we’ll simply iterate all the arguments sent to bindList(), getting their selected values from the respective lists. If we don’t have a legitimate sending-select value, we clear the target list’s values and indicate a proper parent value is needed.

var qs = "list.php?";
var ok = true;
var s;

//list to be bound with values
var target = "#" + arguments[0];
//list that is invoking this function
var sender = "#" + arguments[1];

//clear child list values
$(target).attr("disabled", true);
clearMessage(target);

//change target list to indicate values being retrieved
var s = document.getElementById(arguments[0]);
s.options.length = 0;
s.options[0] = new Option('Getting list ...', '');

if(checkValue(sender)) {
	//build querystring
	for(var i = 1; i < arguments.length; i++) {
		qs += "&" + arguments[i] + "=" + escape($("#" + arguments[i]).val());
	}

	//http get call to helper page
	var theOptions = new Array();
	$.get(qs, function(data) {
			eval(data);
			//on success, bind list as options
			if(theOptions.length > 0) {
				addOptions(target, theOptions);
			}
		}
	);
}
else {
	//replace with error message on invalid parent option
	s.options.length = 0;
	s.options[0] = new Option('Select a valid parent value ...', '');
}

The PHP Helper Page

Our PHP helper page checks the query string values it receives to ensure they are in range. And now, because it is going to handle populating multiple lists, we need to determine which arguments the helper page has received, and therefore which SQL command to build.

/variable validation
if(!preg_match('/^[A-Z]{2}$/', urldecode($_GET['state']))) {
	$output = "alert('State value not in range');\n";
}
elseif(isset($_GET['zip']) && !preg_match('/^[0-9]{5}$/', urldecode($_GET['zip']))) {
	$output = "alert('ZIP Code value not in range');\n";
}
elseif(isset($_GET['city']) && !preg_match('/^[\w \']{1,50}$/', urldecode($_GET['city']))) {
	$output = "alert('City value not in range');\n";
}
elseif(!$link = mysql_connect('server-name', 'username', 'password')) {
	$output = "alert('Could not connect to database');\n";
}
elseif(!mysql_select_db('database-name')) {
	$output = "alert('Could not select database');\n";
}
else {
	//inputs OK and connected; let's query
	if(isset($_GET['zip'])) {
		$sql = "SELECT CONCAT(CAST(latitude AS CHAR), ', ', CAST(longitude AS CHAR)) AS latlng FROM php_zip_code_distance WHERE zip_code = '" . mysql_real_escape_string($_GET['zip']) . "' AND state_code = '" . mysql_real_escape_string($_GET['state']) . "' AND city = '" . mysql_real_escape_string($_GET['city']) . "' ORDER BY zip_code";
	}
	elseif(isset($_GET['city'])) {
		$sql = "SELECT zip_code FROM php_zip_code_distance WHERE state_code = '" . mysql_real_escape_string($_GET['state']) . "' AND city = '" . mysql_real_escape_string($_GET['city']) . "' ORDER BY zip_code";
	}
	else {
		$sql = "SELECT DISTINCT city FROM php_zip_code_distance WHERE state_code = '" . mysql_real_escape_string($_GET['state']) . "' ORDER BY city";
	}

With the SQL set to go, we can execute our query, get back the results, and send those along to bindList().

	if(!$rs = mysql_query($sql)) {
		$output = "alert('Error getting list from database');\n";
	}
	elseif(mysql_num_rows($rs) == 0) {
		$output = "alert('No records found');\n";
	}
	else {
		$i = 0;
		while($row = mysql_fetch_array($rs)) {
			if($i % 5 == 0) {
				$output .= "theOptions.push(new Option('--------------------', ''));\n";
			}
			$output .= "theOptions.push(new Option('$row[0]', '$row[0]'));\n";
			$i++;
		}
	}
}

header('Content-type: text/plain');
echo $output;

Note that I could have used JSON here instead of creating a JavaScript array. JSON would have been far more elegant, but again, I am trying to balance the elegance of this solution against the abilities of most people looking for help with this need. Since far more people are familiar with arrays than JSON, I went with the less efficient array method.

The Remaining JavaScript Functions

Once we have values returned from the helper page, addOptions() re-enables the target list, clears it of any current values, then appends the passed options to the target list.

function addOptions(s, cl) {
	//enable child select and clear current child options
	$(s).removeAttr("disabled");
	//repopulate child list with array from helper page
	var list = document.getElementById(s.replace('#', ''));
	list.options.length = 0;
	for(var i = 0; i < cl.length; i++) {
		list.options[i] = new Option(cl[i].text, cl[i].value);
	}
}

The checkValue() function ensures we got a valid selection from the sending list. Note that I need this function because I am using valueless separators in my lists; if you do not use that technique, you can alter the code to remove this function and all calls to it.

function checkValue(obj) {
	//remove current error message
	clearMessage(obj);
	//if there is an invalid choice, place error message, return false
	if($(obj).val() == "") {
		$(obj).css("background-color", "pink");
		$(obj + "msg").html("&laquo; Please make a valid choice");
		return false;
	}
	return true;
}

The clearMessage() function is used to assist with my jQuery-based form validation. If I have an error, I update a span with a warning message for each error, and change the background color of the select list in question; this function “resets” such messages by clearing the inner HTML of the span and setting the select list’s background to white.

function clearMessage(obj) {
	//clear status message for element
	$(obj + "msg").html('');
	$(obj).css("background-color", "white");
}

Finally, checkForm() ensures that I have valid values selected in all of the parent-child chain select lists. If at least one does not contain a valid value, the function returns false and the form is not processed.

function checkForm() {
	var obj;
	var ok = true;

	// for every list in the parent-child chain ...
	$(".ms").each(function() {
		obj = "#" + this.id;
		//if it does not have a valid value, return false
		if(!checkValue(obj)) {
			ok = false;
		}
	});

	return ok;
}

And that completes the code. You can see a working demo here: http://www.dougv.com/demo/ajax_parent_multi_child_select/index.php

You can download the code used in this demo here: Parent – Child Select Lists Revisited: Multiple Parent – Child Select Lists Via PHP, MySQL And jQuery Demo Code

I distribute all code under the GNU GPL version 3.

Leave a Reply