<cfquery name="qryLocationInfo" datasource="#DB#" username="#DB_Username#" password="#DB_Password#">
SELECT distinct Country, State, City
FROM PropertyLocation
group by Country, State, City
ORDER BY Country, State, City
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Multiple three selects related</title>
<script language="JavaScript">
//create the initial array to hold the different records.
var aryLocation = new Array();
<!--- We set a variable for our loop to insert the data. The loop starts at zero because JavaScript arrays start at zero. --->
<cfset Variables.JSLoop = 0>
<cfoutput>
<cfloop query="qryLocationInfo">
aryLocation[#Variables.JSLoop#] = new Array("#qryLocationInfo.Country#", "#qryLocationInfo.State#", "#qryLocationInfo.City#");
<cfset Variables.JSLoop = Variables.JSLoop + 1>
</cfloop>
</cfoutput>
function tsrUpdSelect(frstSel, scndSel, thrdSel, thisSel) {
//local variables
var i; var chkCty = "";
//see if we just changed the first select box (state).
if(thisSel.name == frstSel.name) {
//set the length of the other selects to zero to empty them
scndSel.options.length = 0;
thrdSel.options.length = 0;
//set the first option for each (being messages to do a select).
scndSel.options[scndSel.length] = new Option("Choose State", "");
thrdSel.options[thrdSel.length] = new Option("Choose City", "");
//Now loop through and set the second option list (city).
//If a state was chosen. There is also code to prevent duplicate cities. This could happen if a city had multiple addresses.
if(thisSel.options[thisSel.selectedIndex].value != "") {
for (i = 0; i < aryLocation.length; i++) {
if(aryLocation[i][0] == thisSel.options[thisSel.selectedIndex].value && chkCty.lastIndexOf(aryLocation[i][1] == -1) {
scndSel.options[scndSel.length] = new Option(aryLocation[i][1], aryLocation[i][1]); chkCty = chkCty + "," + aryLocation[i][1];
}
}
}
}
//see if we just changed the second select box(state).
if(thisSel.name == scndSel.name) {
//set the length of the third select to zero.
thrdSel.options.length = 0;
//set the first option.
thrdSel.options[thrdSel.length] = new Option("Choose City", "");
//set the rest of the values if a city was chosen.
for(i=0; i < aryLocation.length; i++) {
if(aryLocation[i][0] == frstSel.options[frstSel.selectedIndex].value && aryLocation[i][1] == scndSel.options[scndSel.selectedIndex].value) { thrdSel.options[thrdSel.length] = new Option(aryLocation[i][2], aryLocation[i][2])
}
}
}
}
</script>
</head>
<body>
<cfoutput>
<form name="testForm" method="post" action="">
<table>
<tr><th>Country</th><th>State</th><th>City</th></tr>
<tr>
<td>
<cfset countryList = "">
<select name="myCountry" onChange="tsrUpdSelect(this, testForm.myState, testForm.myCity, this);">
<option value="" selected>Choose Country</option>
<cfloop query="qryLocationInfo">
<cfif not listFindNoCase(countryList, qryLocationInfo.Country, ",")>
<option value="#qryLocationInfo.Country#"> #qryLocationInfo.Country# </option>
<cfset countryList = listAppend(countryList, qryLocationInfo.Country, ",")>
</cfif>
</cfloop>
</select>
</td>
<td>
<select name="myState" onChange="tsrUpdSelect(testForm.myCountry, this, testForm.myCity, this);">
<option value="">Choose State</option>
</select>
</td>
<td>
<select name="myCity">
<option value="">Choose City</option>
</select>
</td>
</tr>
</table>
</form>
</cfoutput>
<p> </p>
<cfdump var="#qryLocationInfo#">
</body>
</html>
