Using AJAX To Data Bind A Child Drop Down List Based On The Selected Option Of A Parent Select Control

Recently asked on Yahoo! Answers:

How to generate a submenu on the basis of value in a menu without refreshing the page?
I want to use if else construct to generate a submenu on the basis of value from a textfield in the same form. You can get it in the way that when someone selects a country in a field…the states menu of the form changes according to the country selected.

This question, rephrased: You have two HTML select lists; one contains parent values — for example, a list of states — and the other will contain child values — say, a list of cities in the selected state. Every time the value in the parent drop down list is changed, the values in the child drop down list are also changed. (I’ll be using the terms select list and drop down list interchangeably.)

This is a fairly common programming problem, and there are lots of examples out on the Web on how to solve it. So why write another one?

Primarily, because this question is so common. Most of the questioners on Yahoo! Answers are beginners, and as a result, a significant number don’t necessarily know how to phrase a Web search to find a tutorial. Far easier, then, to demonstrate the technique here and simply refer Yahoo! Answers users to this blog entry.

The executive summary: We’ll use AJAX to query a database seamlessly in the background every time the value of the parent select list is changed. More specifically, we’ll use the jQuery library to handle the AJAX requests, bind the child list and make sure the form can’t be processed until the request is complete.

Before We Start: You Need Relational Data

In order to have parent and child drop down lists, you must have parent and child data that is relational. In other words, whatever is going to appear as an option in the child drop down list has to be determined by some value in the parent list.

I know this sounds obvious, but you need to plan your data properly for a parent / child select list scheme to work. For example, suppose you have a parent select list which contains all the world’s manufacturers of automobiles. You want the child drop down’s options to be all the car models made by the manufacturer selected in the parent drop down list.

To do that, you have to have data that keys car models to their manufacturers. You can do that a couple of ways:

  1. You might have just a table of car models, which contains a column of standardized values for each manufacturer (in other words, for all cars made by Ford, “Ford” is the only value in the manufacturer column of your car models table).
  2. You might have two tables, one of manufacturers and another of models, where the model table has a foreign key to the manufacturer table.
  3. You have some other structured data source, such as an array or a class, that groups firmly together the manufacturers and models of cars they make.

Once more: You must have a way to take a value from the parent list and equate it to some subset of child records. And that, in short, means relational data.

Step 1: Get jQuery

If you don’t already have a copy of jQuery on your Web site, you’ll need to download it. It’s free from http://docs.jquery.com/Downloading_jQuery. Either the “minified” or uncompressed version is fine; elegance dictates downloading the “minified” version.

Once you’ve downloaded the file, upload it to your Web site. Then, create a script tag in your Web page’s head section that imports the jQuery library:

<script type="text/javascript" src="jquery-1.3.2.min.js"></script>

Step 2: Create The Select Lists

With the jQuery library on the page, we can now create the parent select list and its child.

<form id="form1" name="form1" method="post" action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
    <label>
        Select a state:&nbsp;
        <select id="state" name="state" onchange="bindCity()">
            <!-- php code to get state list goes here -->
        </select>
    </label>
    <br />
    <label>
        Select a city:&nbsp;
        <select id="city" name="city" disabled="disabled">
            <option>Select a state ...</option>
        </select>
    </label>
    <br />
    <input id="submitform" name="submitform" type="submit" value="Submit" />
</form>

We will create the values for the parent select list using a normal PHP script on the page. That’s because no matter what, we need to get the values for the parent drop down list every time the page is loaded; and the values in the parent select list will never change.

I’m going to use states and cities for my data. Specifically, I will be getting the parent values — states — and child values — all the cities in the selected state — from the ZIP Codes table I created a few weeks ago, for the blog entry titled, “Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via PHP And MySQL.”

I’m also going to use a variation on another previous post, “Revisited: Adding Non-Selectable ListItem Controls To An ASP.NET DataBound List Control“, to help break up these long lists.

Doing that is simple: We just declare a counter variable, increment it by one every time we read a record, and see if the modulus of that counter variable divided by five is zero. If it is, we output a dashed line.

<?php
//error if cannot connect to db server
if(!$link = mysql_connect('server', 'username', 'password')) {
	echo "<option>Cannot connect to database server</option>\n";
}
//error if cannot select database
elseif(!mysql_select_db('database')) {
	echo "<option>Cannot select database</option>\n";
}
else {
	//error if problem parsing query
	if(!$rs = mysql_query("SELECT DISTINCT state, state_code FROM php_zip_code_distance ORDER BY state")) {
		echo "<option>Error getting state values from database</option>\n";
	}
	//error if query returns no records
	elseif(mysql_num_rows($rs) == 0) {
		echo "<option>No records found</option>\n";
	}
	else {
		//ouput records
		$i = 0;
		while($row = mysql_fetch_array($rs)) {
			//create separator every five records
			if($i % 5 == 0) {
				echo "<option value=\"\">--------------------</option>\n";
			}
			echo "<option value=\"$row[state_code]\">$row[state]</option>\n";
			$i++;
		}
	}
}
?>

Step 3: The citylist.php “Helper” Script

We’re going to need a PHP “helper” script, which will receive from our yet-to-be-written AJAX request the name of the selected state, and returns a listing of all the cities in that state.

This is basically just a normal PHP page, except instead of outputting our results as HTML, we’re going to send the results as plain text — or, more specifically, as text we can process as JavaScript.

More on how we do that processing in a bit. Let’s focus now on getting the relevant records. Our helper script does the following:

  • Uses a regular expression to check if there is a GET variable named statecode that consists of two capital letters. If not, the script alerts us to that fact.
  • Connects to the database server and selects the database, or reports errors if either cannot be done.
  • Selects all city records for the given state code (which we ensured was in the proper format with our first step), and reports if there was a database error or no records were found.
  • Adds all the city records to a JavaScript array (we’ll discuss that shortly), with periodic separators as we did for the parent select list.
<?php
//create error if statecode query string is not present or not two capital letters
if(!preg_match('/[A-Z]{2}/', $_GET['statecode'])) {
	$output = "alert('Input pattern incorrect');\n";
}
//error if cannot connect to db server
elseif(!$link = mysql_connect('server', 'username', 'password')) {
	$output = "alert('Could not connect to database');\n";
}
//error if cannot select database
elseif(!mysql_select_db('database')) {
	$output = "alert('Could not select database');\n";
}
else {
	//error if query for child records cannot be parsed
	if(!$rs = mysql_query("SELECT DISTINCT city FROM php_zip_code_distance WHERE state_code = '$_GET[statecode]' AND TRIM(city) != '' ORDER BY city LIMIT 80")) {
		$output = "alert('Error getting city list from database');\n";
	}
	//error if no matching records found for state code
	elseif(mysql_num_rows($rs) == 0) {
		$output = "alert('No records found');\n";
	}
	else {
		//if child records found, output results to JavaScript array of Option objects
		$i = 0;
		while($row = mysql_fetch_array($rs)) {
			//create a non-selectable separator every five entries
			if($i % 5 == 0) {
				$output .= "cityOptions.push(new Option('--------------------', ''));\n";
			}
			$output .= "cityOptions.push(new Option('$row[city]', '$row[city]'));\n";
			$i++;
		}
	}
}
//return results
header('Content-type: text/plain');
echo $output;
?>

Step 4: The bindCity() JavaScript Function

We’re ready to get jQuery to send to the helper page a request for the child records — again, in this example, a listing of all cities within the selected state. Our function will:

  • Disable the child list, so that incorrect / inappropriate selections cannot be made while we wait for new records.
  • Clear the current values of the child list, in preparation of new values.
  • Presents a message to the user that we are getting new child records.
  • Gets the value of the selected item in the parent drop down list. If that value is an empty string (i.e., a separator), the script indicates the option is invalid. Otherwise, it appends the parent select list’s value as the value of the statecode GET variable.
  • The helper script is called, its results evaluated as JavaScript, and a second function — addOptions — is called to populate the child list, provided we got options back from the database.
function bindCity() {
	//disable child select list
	$("#city").attr("disabled", true);
	//clear child select list's options
	$("#city").html('');

	//querystring value is selected value of parent drop down list
	var qs = $("#state").val();
	//if user selected a separator, show error
	if(qs == '') {
		alert('You cannot select this option. Please make a different selection.');
	}
	else {
		//show message indicating we're getting new values
		$("#city").append(new Option('Getting city list ...'));
		//declare options array and populate
		var cityOptions = new Array();
		$.get("citylist.php?statecode=" + qs, function(data) {
				eval(data);
				if(cityOptions.length > 0) {
					addOptions(cityOptions);
				}
			}
		);
	}
}

The addOptions() function accepts as an argument the list of options we got from the database. It then re-enables the child select list, clears all its current options (namely, the message we sent in bindCity(), saying we were getting the child records) and then adds all the available options.

Unfortunately, due to a limitation in Internet Explorer, we cannot use jQuery notation to add options to the child select list; we have to use old-school DOM scripting. This is especially disappointing as Jim O’Neil of Microsoft has stated on his blog, and in my presence on more than one occasion, that Microsoft is committed to jQuery’s development. A DOM manipulation technique as simple and straightforward as appending options to a select list ought to be supported by IE.

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

And that’s all there is to it.

You can see a working demo here: http://demo.dougv.com/ajax_parent_child_select/

This code on Github: https://github.com/dougvdotcom/ajax_parent_child_select

I distribute code under the GNU GPL. See Copyright & Attribution for details.

All links in this post on delicious: http://delicious.com/dhvrm/using-ajax-to-data-bind-a-child-drop-down-list-based-on-the-selected-option-of-a-parent-select-control

Two Quick Postscripts

Note that this solution requires JavaScript to work. As such, if you are going to send the values of these select lists to a server-side script (e.g., a PHP page), you will want to be extra careful to ensure you get values from the child list, and that any such values are sanitized by your server script — since users can easily amend the values sent to your form via JavaScript.

Also, if you want to use the data for this example, you will need to follow the process noted in Step 1 of the my post titled, “Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via PHP And MySQL.”

21 Comments

  1. Hello,

    Very thorough job Doug. I’ve been all over the web trying to get one of these city/state mysql dropdown menu tutorials to work, but have failed for one reason or another. However, with yours I think I am getting really close. I have the db linking up sucessfully but when i select a city in the dropdown, i get a popup that says “Input pattern incorrect” Any advice on what my problem could be?

    Thanks in advance,
    Brian

  2. Wow, sorry for being a noob, but I worked out my first issue. I assumed that you renamed the automatically generated primary key column to state_code for some reason. And in fact I should have renamed the column to “id”. Then the column “state _abbreviation” in the CVS file gets changed to “state_code.” So now the drop downs work really slick and I am getting excited. However, how do I pass my values to a result page?

    My goal is to have Dealership names and addresses get displayed once a City gets selected from the dropdown list.

    Thanks again,
    Brian

  3. Brian, when you post your form, whatever values are currently selected in the select lists will post. In other words, you handle form processing as you normally would. Simply direct the action of the form at whatever page will process your results.

  4. Thank so much for your reply. For some reason after I submit, It looks like I am passing the state_code (example, AL instead of Alabama) and city values to my results page.

    What I get in my browser:
    http://localhost/test/ajax/jquery/ResultsAdv.php?state=AL&city=Moody&Submit=Submit

    How can I instead pass on the state (full state name “Alabama” not AL) and city values. Did I do something wrong or is this how it was supposed to be?

    Do you have a paypal donations page?

    Thank you,

    Brian

  5. Brian, I can’t see results running on your localhost.

    You are getting the state codes instead of state names as a result because the values of the parent select list are the state codes, not the state names. If you want to use state names, those need to be the values of your parent select list. And if you want to use full state names as the parent values, then you need to make three changes to the script as provided.

    First, you need to change the PHP that creates the parent list to use the value of the state column as the values of the parent select list’s options:

    while($row = mysql_fetch_array($rs)) {
    	//create separator every five records
    	if($i % 5 == 0) {
    		echo "<option value=\"\">--------------------</option>\n";
    	}
    	echo "<option>$row[state]</option>\n";
    	$i++;
    }
    

    Next, you need to replace the regular expression in the citylist.php “helper” script that checks for proper state code, with one that will ensure the statecode variable at least isn’t an injection attack:

    if(!preg_match('/[A-Za-z]{4,20}/', $_GET['statecode'])) {
    

    Finally, you need to amend the query in citylist.php to select child records by the value of the state column:

    if(!$rs = mysql_query("SELECT DISTINCT city FROM php_zip_code_distance WHERE state = '$_GET[statecode]' AND TRIM(city) != '' ORDER BY city LIMIT 80")) {
    	$output = "alert('Error getting city list from database');\n";
    }
    

    Those three changes will allow you to capture the full name of the state from the parent select list when you post it to a PHP page.

    I’m glad you’ve found this script useful. If you’d like to send a donation for this, simply remit any amount you like via PayPal to paypal@dougv.com.

  6. hi. just want to ask how to append values to dropdown list say from a textbox using ajax. for example i have a dropdown list of cities and i want to insert a new city name into the list using a input from a textbox.

    thanks

  7. Thanks Doug!

    would the process still be the same if the listbox were pre-populated from a database? what i intended was to add items to the listbox but behind the scenes (XHR) it is being saved to the db (INSERT INTO…) and queried back (SELECT…WHERE) to be displayed in the list as a new item.

    i understand the onchange() events that trigger sending of data to and from the db but i don’t know how to append (DOM traversal/javascript code) the values that was just added from the textbox to the current dropdown list.

    Thank you.

  8. I understand what you are asking, Allfo. What you describe is a simple AJAX request. I’d be willing to assist you with your project if you were willing to make a purchase from my wish list, linked under Copyright, Attributions and Donations.

  9. Hi Doug,
    I used that for long…works very well. thanks! But recently I had a request to allow multiple selection in a given list…I was hoping that changing the SQL query from:
    – SELECT DISTINCT city FROM php_zip_code_distance WHERE state = ‘$_GET[statecode]’
    into
    SELECT DISTINCT city FROM php_zip_code_distance WHERE state IN ‘$_GET[statecode]’
    …will do the job…but it seems the js cannot pass more than 1 selected item, per list..
    Any idea…how to do it?
    thanks
    David

  10. @david: I’m not certain what you are asking, but you can’t have multiple master selections in the code I have provided. So, if what you mean is, you want to use a multiselect master list, and populate a child list based on that, no, this won’t work as authored. Yes, it can work like that, but no, I’m not likely to demo that anytime soon. If you like, I could do the work for hire.

  11. Hi Doug,
    I finally hired…my own brain.
    the “SELECT DISTINCT city FROM php_zip_code_distance WHERE state IN ‘$_GET[statecode]‘…” was a good starting point.
    I just added an intermediate step in Mysql Query:
    A str_replace, in order to add the necessary simple quotes, in between “comma separated values” sent to PHP..
    $var = str_replace(“,”,”‘,'”,mysql_real_escape_string($_GET[‘state’]));
    Just posting the trick, Hoping it will help someone…

  12. Hi Doug,
    I found it very helpful and tried it in a wordpress site. I have placed all the files inside my theme directory. But I am not getting the result. When I change the first state dropdown, the second one stays disabled with the text “Getting city list”. I am using wordpress’ tables.

    Could you please help me?

    Thank you

Leave a Reply

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!