populate a dropdown list based on another dropdown list

this isn't my code, but i came across it one day when in a situation like yourself.  Start by having all of your states and counties in the two lists, then use the code below.

simply replace the dynamicSelect("selectbox1name", "selectbox2name") with dynamicSelect("qstate", "county")

<script type="text/javascript">
  /*
    UPDATE LIST SCRIPT
    - refreshDynamicSelectOptions
  */
  function dynamicSelect(id1, id2) {
    // Feature test to see if there is enough W3C DOM support
    if (document.getElementById && document.getElementsByTagName) {
      // Obtain references to both select boxes
      var sel1 = document.getElementById(id1);
      var sel2 = document.getElementById(id2);
 
      // Clone the dynamic select box
      var clone2 = sel2.cloneNode(true);
      // Obtain references to all cloned options
      var cloned2Options = clone2.getElementsByTagName("option");
      // Onload init: call a generic function to display the related options in the dynamic select box
      refreshDynamicSelectOptions(sel1, sel2, cloned2Options);
      
           
      // Onchange of the main select box: call a generic function to display the related options in the dynamic select box
      sel1.onchange = function() {
        refreshDynamicSelectOptions(sel1, sel2, cloned2Options);
      };
    }
  }
  function refreshDynamicSelectOptions(sel1, sel2, clonedOptions) {
    // Delete all options of the dynamic select box
    while (sel2.options.length) {
      sel2.remove(0);
    }
    // Create regular expression objects for "select" and the value of the selected option of the main select box as class names
    var pattern1 = /( |^)(Please select...)( |$)/;
    var pattern2 = new RegExp("( |^)(" + sel1.options[sel1.selectedIndex].value + ")( |$)");
    // Iterate through all cloned options
    for (var i = 0; i < clonedOptions.length; i++) {
      // If the classname of a cloned option either equals "select" or equals the value of the selected option of the main select box
      if ( (sel1.options[sel1.selectedIndex].value == "Please select...") || (clonedOptions[i].className.match(pattern1) || clonedOptions[i].className.match(pattern2)) ) {
        // Clone the option from the hidden option pool and append it to the dynamic select box
        selecteditempos = parseInt(clonedOptions[i].value);
		sel2.appendChild(clonedOptions[0].cloneNode(true));
      }
    }
  }
  
  // Attach our behavior onload
  window.onload = function() {
    dynamicSelect("selectbox1name", "selectbox2name");
  }
  
</script>

Select allOpen in new window

Of course, the easiest thing to do would just be to make the form auto-submit when a new state is selected, and then have CF return a new page that has the county list populated (and all the other information filled in as it was before).  Not as the most elegant solution in terms of interface, but simple :)

HI, sbickerstaff:

Thanks very much. But how does the form that has the two dropdown list use this script? Could you elaborate a little?

i'll try and explain it with your example but will have to wait until later this evening when i get home from work.   are you ok to wait until then?

Thanks, sbickerstaff. I can wait.

Thanks so much, sbickerstaff. It is great. But there is still one difficult in my case. The state and county lists are dynamic.  They are from database equery, so the select element will be as follow for the state.
<select id="state" name="state" >
    <option value="" > Select a State</option>
      <CFLOOP QUERY="qStates">
        <option value="#state#" onclick="" >#state#</option>
      </CFLOOP>
    </select>

But for the county, it is a little tough because the selection is based on the state selection.I am kind of thinking nested cfloop as:
   <select id="county" name="county" >
      <option value="" >Selec a County</option>
      <CFLOOP query ="qstates">
            <CFLOOP QUERY="qCounties">
                  <option class=#state# value="#county#" >#county#</option>
            </CFLOOP>
      </CFLOOP>
    </select>
But i cannot make it work. Do you have a solution for the problem?

what is the cfloop? coldfusion or something?  Unfortunately i haven't seen or used the cfloop feature before.

In my experience, I would have used mysql as a database and, using your situation, would have had the following db design:

state (stateid, statename, ......)
county (countyid, countyname, .....)
state_county_link (stateid, countyid)

then a select statemenet like: SELECT scl.stateid, c.countyid, c.countyname FROM state_county_link as scl, county as c WHERE c.countyid=scl.countyid

using a loop, insert the values
<option class="$stateid" id="$countyid">$countyname</option>
(this isn't correct syntax but it'll give you an idea how i would do it.

how do the queries "qstates" and "qCounties" run? does the two cfloop lines call a select statement from somewhere?
i.e.
<cfquery datasource="Entertainment" maxrows="50">
  select * from Movies
</cfquery>

if so, what s your table structure? in your DB, how do you link states and counties?

just reading through your post again.... do u mean that your county list doesn't do anything at all until you select state?  as i said, i'm not familiar with coldfusion, my example would be for a normal webpage where the page is parsed and all lists populated before the page is displayed (afterwhich the list is then filtered based on state selection)

sorry my knowledge in this case (cf, etc) is not very high but if I can help in any way, i will try :)

HI, sbickerstaff:
You are really great. I am so grateful for your help. I was not on computer today, so cannot get back to you earlier. I am using auto-submit for now to get it work:
<select name="state" onchange=submit() >
      <CFLOOP QUERY="qryStates">
             <option value="#state#" >#state#</option>
     </CFLOOP>
   </select>
This will submit the state value. Then I can use the state vlaue to query for counties and use the counties in select element:
<select name="county" >
      <CFLOOP QUERY="qryCounties">
            <option value="#county#" >#county#</option>
      </CFLOOP>
    </select>
It works fine, but the problem is the form actually submitted twice: one for updating the state value and the other is final submit after select a county.

But After I read your comment today. I have some new idea. I think it will work also. I get the state and county values from a users registeration table. I need a query  result for state from the table, and then I can get a state-county link query from that table also. The select element for the county will be:
<select name="county" >
      <CFLOOP QUERY="qryStateCounties">
            <option class="#state#", value="#county#" >#county#</option>
      </CFLOOP>
    </select>
I will try this idea tomorrow.

Thanks again

I got it to work. First I need to do two queries for state and state-county using cfquery or cfstoredproc. The selection statements for qcounty is:
select distinct state from dataTable
and for qstatecounty is
select distinct state, county from dataTable
Then the two select elements are:
     <select id="state" name="state" >
      <CFLOOP QUERY="qStates">
        <option value="#state#" >#state#</option>
      </CFLOOP>
    </select>
   <select id="county" name="county" >
        <CFLOOP QUERY="qryStateCounties">
            <option class="#state#", value="#county#" >#county#</option>
      </CFLOOP>
    </select>

It works.
Thanks, especially to sbickerstaff.

great to hear you got it to work :)