Dependant Queries for List/Menus in a Form

[CFM Enquirer]

I'm using SQL databases to populate List/Menus in a form.

What I need some advice on is as follows.

The user selects an entry from the first List/Menu that is filled from a an SQL database, which will then narrow the selection for the next List/Menu that is filled from a another SQL database.

How do I make the query to fill the second List/Menu automatically follow the outcome of the first List/Menu, and so on?

Any advice would be much appreciated.

[acoder]

Welcome to TSDN.

Depending on how you wish to do this, this may be purely a javascript problem.

Do you want to make requests to the server to get the smaller data set or just load all the data on page load?

[terminon]

I have run into this same issue in some of my programs. My solution may not be terribly elegant, but it works like a charm.

Using a car example, say we have Ford and Toyota vehicles. I run a query that returns all vehicles complete with make and model. I then loop through it, creating a list of arrays with the info I want.

<cfloop query="qryGetModels">
   <cfset formSelectData = ListAppend(formSelectData, "new Array('#make#','#model#','#modelID#')")>
</cfloop>

To dynamically change the values in a list of models based on whether they select Ford or Toyota, I generate some JavaScript on page load that creates a javascript array of arrays with the information from my query and a function that is called by an onChange event associated with the makes select HTML element (<select name="make" onchange="showModels()">)

vehicles = new Array(<cfoutput>#formSelectData#</cfoutput>);
 
    function showModels() {
      var makes = document.forms[0].make;
      var models = document.forms[0].model;
      models.options.length = 0;
      for (i = 0; i < vehicles.length; i++) {
        if (vehicles[i][0] == makes.options[makes.selectedIndex].value) {
          models.options[models.options.length] = new Option(vehicles[i][1],vehicles[i][2]);
        }
      }
    }

Now when a user clicks on "Ford" or "Toyota" in the makes select, the models select cleared and then populated with all Ford or Toyota models.

Here is an example of what my javascript "vehicles" variable/array looks like:

vehicles = new Array(new Array('Ford','Taurus','1'), new Array(new Array('Ford','Explorer','2'), new Array(new Array('Toyota','Avalon','3'), new Array(new Array('Toyota','Yaris','4'));

Like I say, it might not be elegant, but it works!