dougv.com | The Web home of Doug Vanderweide » Blog Archive » Populating A List Box From Choices In Another List Box Via AJAX, PHP And MySQL

Populating A List Box From Choices In Another List Box Via AJAX, PHP And MySQL

February 23, 2007, 7:48 AM

Recently, the following comment was left on a previous blog entry, JavaScript: Handling Multiple Selections In A SELECT Box:

Hi. This Multiple Demo Select is cool. I was actually how I could fill in a list box B, where the values of B depend on value selected in list box A. More like a choose category, then the sub categories from db fill in the list box B. I want to do it without refreshing the page. Is it possible?

You can do that quite easily with AJAX. I sort of touched on it in another article, Dynamically Populating A Listbox From A Textbox Via JavaScript / DOM.

However, since this is a fairly common request in Yahoo! Answers, I figured it was about time to go ahead and make an example. You’ll be able to download source code for this example at the end of the discussion.

Step 1: Create Your Data

The first step is to have some dynamic data that you want to work with. For this example, I am going to use a list of animals and some attributes they might share with other animals, or that might be unique to the animal.

I’m going to put all of these characteristics into a database, in a 1-1 relationship table. That is, each animal will have multiple listings in the table, one for each attribute it has. For example, an elephant is heavy, tall and gray; therefore, elephant will have three entries in the table, like so:

animal	attribute
elephant	heavy
elephant	gray
elephant	tall

Step 2: Create Your Base HTTP Request Object

The essence of AJAX is making HTTP requests back to your server without reloading your page. For that, you need to create an HTTP Request object.

There’s no need to reinvent the wheel here. I lifted my HTTP request code directly from Mozilla.org’s AJAX section, and tampered with it just a bit by changing the response event handler.

OK, let’s take a quick step back. When you use AJAX to make a HTTP request, you’re doing so asynchronously — that’s the first A in AJAX — meaning you don’t have to wait for a response before your page can move on to the next thing.

You do, however, need to listen for a response, so that when the Web server returns the data you want, you can do something with it. Thankfully, both the Internet Explorer and Mozilla implementations of HTTP Request allow you to create what is called an response event listener.

Basically, an event listener sits silently, waiting for something to take place. In the case of HTTP requests, we need an event handler to listen for the Web server’s response to our request.

In the Mozilla sample, the listener simply waits to hear back from the server that all went well, and pops up an alert box when it happens. Our function will, instead, populate one of our two listboxes. The first listbox will be populated on page load; the second, any time a new selection is made in the first listbox.

Our function to make HTTP requests will take two actions: a URL we want to call, and an “action” attribute that we’re passing through to the response event listener. More on that shortly.

function makeHTTPRequest(url, action) {

Next, we need to figure out if the Web browser uses the Internet Explorer or Mozilla methods for creating HTTP Request objects. That’s all the following block of code does; it tries to create a Mozilla version, and if it can’t, it tries to make an IE version. If it can’t make either, it gives up.

	var httpRequest;
	if (window.XMLHttpRequest) { 
		httpRequest = new XMLHttpRequest();
	} 
	else if (window.ActiveXObject) {
		try {
			httpRequest = new ActiveXObject("Msxml2.XMLHTTP");
		} 
		catch (e) {
			try {
				httpRequest = new ActiveXObject("Microsoft.XMLHTTP");
			} 
			catch (e) {}
		}
	}
 
	if (!httpRequest) {
		alert('Cannot create an XMLHTTP instance');
		return false;
	}

With the request object created, we can now specify how we want to handle the onreadystatechange event. In our case, we’ll pass the text we get back from the server, plus the action we passed to the main function, to another function called, cleverly enough, eventListener().

	httpRequest.onreadystatechange = function() { 
			eventListener(httpRequest, action); 
		};
	httpRequest.open('GET', url, true);
	httpRequest.send(null);
}

Because we’re going to recycle the makeHTTPRequest function — to save on coding — we need to tell the onreadystatechange event handler what to do if the Web server responds positively to our request (that is, it returns an HTTP status code of 200). We’ve passed through the name of the listbox we want to update; the function populateList will bind the proper records to the proper listboxes for us, which you’ll see shortly.

If the ready state of the server never changes to complete (4), our script keeps listening for that event. If it changes to 4, we get the status code: any status code other than 200 is considered an error. (Technically, other HTTP status codes could be sent that would yield workable data. But this isn’t a dissertation on HTTPD, so let’s move on.)

function eventListener(httpRequest, action) {
	if (httpRequest.readyState == 4) {
		if (httpRequest.status == 200) {
			var theResponse = httpRequest.responseText;
			switch(action) {
				case 'attributes': 
					populateList(theResponse, 'attribs');
					break;
				case 'animal':
					populateList(theResponse, 'animals');
					break;
			}
		} 
		else {
			alert('Server error attempting to get list');
		}
	}
}

Step 3: Create The populateList() Function

The response we are going to get back from the PHP pages that will handle our DB query is going to be a JavaScript array. To treat it as such, we’ll use eval(), a handy function that basically tells JavaScript, “Take the following string and treat it as though it were JavaScript.”

Now that our array of list items is available thanks to eval(), we remove whatever options are currently in the listbox we are populating. Then, we iterate through the array, creating new Options out of its values and assigning those options back to the listbox.

Notice that there is an actual object in the DOM named Option, and that’s what populates listboxes and drop-down lists (single-select).

function populateList(response, listID) {
	var theList = document.getElementById(listID);
	eval(response);
 
	for(var i = 0; i < theList.length; i++) {
		theList.options[i] = null;
	}
 
	for(i = 0; i < attrs.length; i++) {
		var theOption = new Option();
		theOption.text = attrs[i];
		theOption.value = attrs[i];
		theList.options[i] = theOption;
	}
}

Step 4: Create An OnChange Event Handler For The First Listbox

We want to be able to populate a second listbox from the first listbox’s items; that’s the ultimate purpose of this exercise. To do that, we need to have an event handler that is fired by the onchange event of the first listbox.

We’ll call that event handler requestAnimals(). It will begin with the base URL for a PHP page, then will look through all the items of the first listbox, finding items that are selected. Each selected item will be pushed onto the end of an array we’ve declared.

Then, we’ll join that array together with comma delimiters, and pass the entire list as a querystring argument to our PHP helper page.

function requestAnimals() {
	var theUrl = 'get_animal_list.php?a=';
	var theList = document.getElementById('attribs');
	var theItems = new Array();
 
	for(var i = 0; i < theList.length; i++) {
		if(theList.options[i].selected) {
			theItems.push(theList.options[i].value);
		}
	}
 
	theUrl = theUrl + encodeURIComponent(theItems.join(','));
	makeHTTPRequest(theUrl, 'animal');
}

Step 5: Create the get_attributes_array.php Page

We now can create a simple PHP helper page that will feed an array to the first listbox for binding. Here’s that code, which is as simple as it comes:

We connect to the database server and select our database.
We ask for all the distinct attributes in the table and list them in alphabetical order.
If there are no results, the script dies. This will cause an HTTP error, which will cause the alert box to show in the AJAX script’s page.
If we have records, we create the array, then add as items to the array each attribute we just got from the recordset. Finally, we close the array.

$host = "hostname";
$user = "username";
$pass = "password";
$dbname = "databasename";
 
$link = mysql_connect($host, $user, $pass) or die('cannot connect to db server');
mysql_select_db($dbname) or die('cannot select db');
 
$sql = "SELECT DISTINCT attribute FROM ajax_listboxes_demo ORDER BY attribute";
$rs = mysql_query($sql) or die('cannot parse query');
$count = mysql_num_rows($rs);
if($count == 0) {
	die('No attributes in list');
}
 
$i = 1;
 
header('Content-type: text/xml');
echo "var attrs = new Array(";
 
while($row = mysql_fetch_array($rs)) {
	echo "'$row[attribute]'";
	if($i < $count) {
		echo ", ";
	}
	$i++;
}
 
echo ");";

Notice that the content type sent is text/xml, not text/plain. The reason for that is because Mozilla warns that some of its implementations require all HTTP Requests to return a content type of text/xml. Since text and XML are fundamentally the same things for most AJAX purposes, there’s no harm in lying to our browser and telling it the page is sending XML when, in fact, it’s plain text.

Step 6: Create the get_animal_list.php Helper Page

The next-to-last step is to write a PHP page that will handle the query string arguments we pass to it and pull, from the database, all the animals that match the selected criteria from the listbox.

We begin by ensuring there is a variable named a in the querystring. If there isn’t, the script dies.
If there is a quertystring variable named a, we explode it into an array, using commas as the delimiter.
We connect to the DB server and choose our database.
We next dynamically create the SQL statement, adding on an OR conditional for each item selected from the first listbox, which we get from the exploded array we made out of the querystring.
We then tidy up the string and try to open a recordset with it.
We output text/xml regardless of whether we have records. If there are no records, we merely echo out a one-cell array that says “No matching animals.” If there are records, we add each item on to the array.
We keep count so that we don’t have a NULL value at the end of the array, and then close the array.

if(!isset($_GET['a'])) {
	die('no attributes given');
}
else {
	$temp = $_GET['a'];
	$temp = explode(",", $temp);
}
 
//db connection info
$host = "hostname";
$user = "username";
$pass = "password";
$dbname = "databasename";
 
$link = mysql_connect($host, $user, $pass) or die('cannot connect to db server');
mysql_select_db($dbname) or die('cannot select db');
 
$sql = "SELECT DISTINCT animal FROM ajax_listboxes_demo WHERE ";
foreach($temp as $att) {
	if(!is_null($att) && $att != '') {
		$sql .= "attribute = '$att' OR ";
	}
}
$x = strlen($sql);
$x -= 3;
$sql = substr($sql, 0, $x);
$sql .= " ORDER BY animal";
 
$rs = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($rs);
 
header('Content-type: text/xml');
echo "var attrs = new Array(";
 
if($count == 0) {
	echo "'-- No Matching Animals! --'";
}
else {
	$i = 1;
	while($row = mysql_fetch_array($rs)) {
		echo "'$row[animal]'";
		if($i < $count) {
			echo ", ";
		}
		$i++;
	}
}
 
echo ");";

Step 7: Create Your XHTML

The last step is to add two listboxes and two event handlers to your HTML. Specifically, we need to add an onload event to the BODY tag, which will populate our first listbox; then, we need to add an onchange event to the first listbox, which will populate the second listbox.

<body onload="makeHTTPRequest('get_attributes_array.php', 'attributes')">
	<h1>
		Populating A List Box From Choices<br />
		In Another List Box Via AJAX, PHP, And MySQL
	</h1>
	<form id="frmMain" method="post">
		<table lang="tidy" cellspacing="0">
			<tr>
				<td>Select attributes below:</td>
				<td lang="leftBorder">Animals with at least <br />
				   one of the selected attributes:</td>
			</tr>
			<tr>
				<td>
					<select id="attribs" size="10" multiple="multiple" onchange="requestAnimals()"></select>
				</td>
				<td lang="leftBorder">
					<select id="animals" size="10" multiple="multiple"></select>
				</td>
			</tr>
		</table>
	</form>
	<p>&amp;nbsp;</p>
</body>