ColdFusion is tailor-made to interact with databases, particularly one that has state and city data. A 30,000-row recordset is a drop in the bucket to the ColdFusion server.
The first section of code is comprised of two queries (Listing A). The first query goes to the table(s) in the database to get every distinct city-state combination. The second query is merely a subset of data from the first query to find all the distinct states.
The form with drop-downs
Next, we take a look at the form (Listing B). This form is a simple form comprised of just the two drop-down boxes in question: one for state and the other for city.
Note that you utilize the allStates query declared in Listing A to create the <select> box options for the state drop-down. There’s no sense in typing it manually when ColdFusion let’s you create it so easily. The city drop-down box is purposefully left empty because when you first load the page, there is no state selected, which means there is no city list to show.
In this first line, you figure out how many states you have in the drop-down.
var CityStateArray=new Array(NumberOfStates)
CityStateArray=new Option(“Select a city…”,””)
Here you create a new array with as many elements as you have states. Next, you hit the array logic. The first logical element is actually at the zero index of the array.
Next, the second line above takes the first element in the CityStateArray and creates a new array. The third line then creates an array within an array. Going back to the arm analogy, you can think of the “arms” array as having two elements, arms and arms. Now, arms is an array element but it has five fingers. Therefore, you could reference each finger as arms, arms, and so on until arms. In plain speak, the third line above states, since the first element in the drop-down for state says, “Select a state…”, then the first option in city should say, “Select a city….”
Lastly, we come to the code in Listing E for the FindCities function. The first line sets up a temp variable to house the city drop-down object. Next is the function declaration for the FindCities function, which you call every time someone changes the state drop-down box. The first for loop goes through the current city drop-down options and nulls them out. The second for loop goes through your CityStateArray and creates a new option in the city drop-down for each city for the picked state. The last line merely makes the “Select a city…” the chosen option.
Click the Builder downloads link to see the final output of either two versions.
Now, the only bad thing about this bit of code is the footprint. To make this work, you need to send about two megabytes worth of data to the client. Some of you will vehemently oppose such a dastardly deed. I agree that clearly this is not an option for you to use if you have predominantly AOL dial-up users. However, two megabytes is nothing over a corporate network for an internal application or if your users are predominantly broadband users. It’s all a tradeoff. One long initial download is the cost for insuring that every city you get will be one you recognize. The choice of whether it’s worth it is up to you. Also, don’t forget that if you don’t want to list every available city in the nation, you can utilize just a subset of data to limit the choices and page size.