Multiple Linked Drop-Downs using the XMLHTTP object
See a working example of this code PHP Example Full Code Back to Tutorials
Once in a while you run across a technology that has been available for some time, but you never really had the time or inclination to look into. Then when you do take the time you find it to be something that is extremely helpful.
This recently happened to me and when I came across the xmlhttp object. This handy tool allows you to pull information from another web page into your web page. This can be very useful when you need to get server data into a Javascript, or access data on a web page that is not on your site.
There are several examples on this site showing how to create dynamic drop-downs that change their options based on the selections made in a previous drop-down. The two basic methods used are refreshing the page when a selection is made to load the data for the next drop down, or loading all possible options into a Javascript array that can then be used to refresh the drop-downs. Both of these methods have benefits and drawbacks.
In the first method, the selection made in a drop-down triggers the page to be reloaded with a querystring or form field set that will then be read to determine what data to load into the next drop-down. The benefit of this method is that we only load the data that we need. If, for example, we were doing a simple page and wanted the city drop-down to be filled in when the user chose their state, then if the user selected FL, then we would never pull data for other states from the database. However, this method requires that the page be reloaded. This means all form values need to be loaded back into the form fields and the refresh is usually visible to the user.
In the second method all possible values are loaded into Javascript arrays. When a user makes a selection, the value of that selection is used to pull data from the array to load the next drop-down. The advantage here is that there is no need for a refresh. The data is all already in memory so it is simply a matter of picking the right data to fill in the next drop-down. The down side is that all possible choices must be loaded when the page is loaded. For large record sets this could be both time and memory intensive.
Where does xmlhttp come into this, you ask. XMLhttp provides us with the ability to get the best of both worlds. With XMLhttp we can have a Javascript function gather data from a separate asp page that can be designed to provide only the data requested. Since the data is loaded directly into the Javascript function, there is no need to refresh the page. Also, we can use larger data sets without having to load every possible option when the page is loaded. Sound interesting? Here's how it works.
The Javascript function specifies a page for the XMLhttp object to load. It then has the ability to read the output of the page specified. So when a user selects an option in a drop-down we trigger a function that specifies an ASP page with a querystring of the value from the just selected drop-down. The ASP page then displays a comma delimited string of the results which the Javascript then reads. None of this is visible to the user. It all happens in the background. When the data is read into the Javascript is is then used to update the values of the next drop-down.
So lets look at an example of this. We need two pages. The first page (the form page) can be either a standard html page or an asp page depending on your needs. In our example it will be an ASP page so we can post the results back to the first page.
We will actually use two functions for this example. The first function creates the XMLHttp object. Although this is should be a simple task, multiple versions of the XMLHttp object require being called in different methods, so loading the object becomes its own function.
function getHTTPObject() {
var req = false;
// branch for native XMLHttpRequest object
if(window.XMLHttpRequest) {
try { req = new XMLHttpRequest(); }
catch(e) {req = false;}
// branch for IE/Windows ActiveX version
}
else if(window.ActiveXObject) {
try { req = new ActiveXObject("Msxml2.XMLHTTP"); }
catch(e) {
try {req = new ActiveXObject("Microsoft.XMLHTTP");}
catch(e) {req = false;}
}
}
return req;
}
The second function is called by the onChange event of the drop-downs. This is function is the real work-horse of the system. The function takes the name of the drop-down to be filled and calls the XMLHttp object to get the data to fill it. Note that we call this function from the onChange event of each drop-down other than the last one and also in the body of the form after the select boxes are loaded to fill the first drop-down. We don't use the onLoad event because the page posts back to itself, an error would be generated when submitting the form.
It would probably be more efficient to load the data into the first drop-down with a database call in our first page rather than use the XMLhttp object to do it, but to keep the example clean I chose to use the XMLhttp object for all of the drop-downs on the page.
function fill(sel) {
oHttp = getHTTPObject();
if (oHttp.readyState != 0) {oHttp.abort();}
// set object varialbles for easier reference
var yr_sel = document.getElementById('yr');
var mk_sel = document.getElementById('mk');
var mod_sel = document.getElementById('mod');
var style_sel = document.getElementById('style');
var sel_to_fill = document.getElementById(sel);
//clear all selects from the one we are filling on
//make the vars we need for the argument string
switch(sel){
case 'yr': yr_sel.options.length = 0;
case 'mk': mk_sel.options.length = 0;
case 'mod': mod_sel.options.length = 0;
case 'style': style_sel.options.length = 0;
}
var mod = ""
var mk = ""
var yr = ""
if (yr_sel.options.length > 0) yr = yr_sel.options[yr_sel.selectedIndex].value;
if (mod_sel.options.length > 0) mod = mod_sel.options[mod_sel.selectedIndex].value;
if (mk_sel.options.length > 0) mk = mk_sel.options[mk_sel.selectedIndex].value;
// URL to the file that will access the data
var sURL = "MultiDropDownXMLHttp2.asp"
var sURL += "?year=" + yr + "&make=" + mk + "&model=" + mod
oHttp.open("get", sURL , true);
oHttp.onreadystatechange = function () {
if (oHttp.readyState == 4) {
var options = oHttp.responseText.split(",");
sel_to_fill.options[0] = new Option("Choose One:","");
for (i=1;i<=options.length;i++) {
sel_to_fill.options[i] = new Option(options[i-1],options[i-1]);
}
}
}
oHttp.send(null);
}
So now we simply have to create our form to complete this page. The onchange event will be used for the drop-downs to load the changes. We'll add some formatting to this in the final version, but to make this cleaner I've left out formatting code here.
<form name="form1" method="post" >
Year: <select name="yr" id="yr" onchange="fill('make');"></select>
Make: <select name="make" id="make" onchange="fill('model');"></select>
Model: <select name="model" id="model" onchange="fill('engine');"></select>
Engine: <select name="engine" id="engine"></select>
</form>
<script>fill('yr')</script>
That's pretty much it for our form page. The next page (the data page) will be an ASP page and will select the data from our database and display it. The XMLHttp object is actually just taking the output of this page and storing it to a variable in our script above. This means we need to have the output of the data page to be in a predictable format.
We are using a querystring in the URL of the XMLHttp call to specify what data we need. In this example there are four drop-downs we need to fill. Year, Make, Model and Engine. We send the values for each of these in the URL and select the data for the first field that is empty. So if we need to get the Year from the database the querystring will look like this: ?year=&make=&model= however if we are looking for make the querystring will look like this: ?year=2004&make=&model= and so on. You may note that the final drop-down is not mentioned in the querystring. That is because there is no need to send the value of the final drop-down as it will not be used to select more data.
The ASP code of this page is actually rather simple. Note the more drop-downs you have the longer the SQL section will get, but in the same format. We use the getRows function rather than looping through the dataset as it is more efficient.
<%Option Explicit%>
<!-- #include file="dataopen2.asp" -->
dim sql, rs, aRS, vString, i
if request.querystring("year") = "" then
sql = "Select distinct [year] " &_
"From tbCars " &_
"Order by Year"
elseif request.querystring("make") = "" then ' we need to look up makes
sql = "Select distinct Make " &_
"From tbCars " &_
"Where year = " & request.querystring("Year") & " " &_
"Order by Make"
elseif request.querystring("model") = "" then ' we need to look up models
sql = "Select distinct Model " &_
"From tbCars " &_
"Where year = " & request.querystring("Year") &_
" and make = '" & request.querystring("make") & "' " &_
"Order by model"
else
sql = "Select distinct Engine " &_
"From tbCars " &_
"Where year = " & request.querystring("Year") &_
" and make = '" & request.querystring("make") & "' " &_
" and model = '" & request.querystring("model") & "' " &_
"Order by engine"
end if
set rs = conn.execute(sql)
' use getRows() to convert the Recordset to an array
' then close the recordset and connection
aRS = rs.getRows()
rs.close
set rs=nothing
conn.close
set conn=nothing
' loop through the data
for i = 0 to ubound(aRS,2)
vString = vString & aRS(0,i) & ","
next
' Remove final comma
vString = Left(vString,len(vString)-1)
' Output the string
response.write(vString)
%>
That's all there is to it. It may seem like quite a bit, but if you look at the complete code you will see that it is actually very concise.
Working with other Server Side languages
The ASP code above could be replaced with PHP code or any other server-side code. Below is PHP code to provide the same functionality. You would simply substitute the URL to the PHP for var URL = "MultiDropDownXMLHttp2.asp" in the fill function.
<?php
/* ------------------ */
/* Create SQL Query */
/* ------------------ */
if (!isset($_GET["year"])) {
$sql = "Select distinct [year] From tbCars Order by Year";
}
elseif (!isset($_GET["make"])) {
$sql = "Select distinct Make From tbCars" .
" Where year = " . $_GET["year"] .
" Order by Make";
}
elseif (!isset($_GET["model"])) {
$sql = "Select distinct Model From tbCars" .
" Where year = " . $_GET["year"] .
" and make = '" . $_GET["make"] . "'" .
" Order by model";
}
else {
$sql = "Select distinct Engine From tbCars" .
" Where year = " . $_GET["year"] .
" and make = '" . $_GET["make"] . "'" .
" and model = '" .$_GET["model"] . "'" .
" Order by engine";
}
/* --------------- */
/* Open Database */
/* --------------- */
$db = new COM("ADODB.Connection");
$datapath= substr($_SERVER['SCRIPT_FILENAME'],0,strrpos($_SERVER['SCRIPT_FILENAME'],'\\')) . "\db2.mdb";
$ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" . $datapath;
$db->open($ConnStr);
/* --------------- */
/* Get Records */
/* --------------- */
$rs = $db->execute($sql);
$output = "";
while (!$rs->EOF) {
if (!$output == "") $output = $output . ",";
$output = $output . $rs->fields[0];
$rs->movenext;
}
/* ----------------- */
/* Close Database */
/* ----------------- */
$rs->close;
$db->close;
/* ------------------ */
/* Display Output */
/* ------------------ */
echo $output
?>
I hope you have found this information useful
Neal Schafer