populate a dropdown list based on another dropdown list

I have a form that has two dropdown list, say state and county. I need to populate county dropdown list after selecting state from state dropdown list. The county dropdown should be disabled before selection of state is made. The state and county dropdown are populated from database query. So after selecting a state, the state will be used as a parameter to call a cfstoredproc to get a query result for populating county dropdown. The idea fo dropdown lists are as follows:
    <select id="state" name="state" >
    <option value="" > Select a State</option>
      <CFLOOP QUERY="qStates">
        <option value="#state#" onclick="" >#state#</option>
      </CFLOOP>
    </select>
   <select id="county" name="county" >
      <option value="" >Selec a County</option>
      <CFLOOP QUERY="qCounties">
        <option value="#county#" >#county#</option>
      </CFLOOP>
    </select>
Thanks in advance.
yxz117Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sbickerstaffCommented:
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>

Open in new window

0
mltsyCommented:
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 :)
0
yxz117Author Commented:
HI, sbickerstaff:

Thanks very much. But how does the form that has the two dropdown list use this script? Could you elaborate a little?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sbickerstaffCommented:
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?
0
yxz117Author Commented:
Thanks, sbickerstaff. I can wait.
0
sbickerstaffCommented:
hi yxz117,

sorry to keep you waiting.  was looking through my own code again (haven't used this for nearly a year) and noticed an erroron 1 line.  also forgot about the structure of your html select elements, appologies for forgetting this.

I've posted a sample for you to try out: http://stephen.in0v8.co.uk/selectsample.html

to begin with you have all elements in both lists.  then for each county, put the value from the associated state as the class name:

<select name="state" id="state">
  <option value="S1">State 1</option>

<select name="county" id="county">
  <option class="S1" value="C1">County 1</option>

when you load the page, the following code calls the script to start the process (names must be in the order stated)

// Attach our behavior onload
window.onload = function() {
  dynamicSelect("state", "county");
}

next....

var clone = sel2.cloneNode(true);
var clonedOptions = clone.getElementsByTagName("option");
refreshDynamicSelectOptions(sel1, sel2, clonedOptions);

these lines take a copy (clone) of all the options in the county list and then calls the refreshDynamicSelectOptions.  this function then clears all items from the 2nd list. it then checks the clone list and if the class name matches what's selected in states, it adds it to the 2nd list, hence you only see a subset of the full list.

the code segment below shows the javascript to add before the </head> and also a sample of the 2 select lists.

hope this makes things a little clearer but any other queries about it, don't hesitate to ask :)

Steve
<script type="text/javascript">
  /*
    UPDATE LIST SCRIPT
    - refreshDynamicSelectOptions = update engineer list
    - refreshDynamicSysSelectOptions = update system list
  */
  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 clone = sel2.cloneNode(true);
      // Obtain references to all cloned options
      var clonedOptions = clone.getElementsByTagName("option");
      // Onload init: call a generic function to display the related options in the dynamic select box
      refreshDynamicSelectOptions(sel1, sel2, clonedOptions);
      
      // 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, clonedOptions);
      };
    }
  }
  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 ( (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
        sel2.appendChild(clonedOptions[i].cloneNode(true));
      }
      else if (i == 0) {
        sel2.appendChild(clonedOptions[0].cloneNode(true));
      }
    }
  }
  
  // Attach our behavior onload
  window.onload = function() {
    dynamicSelect("state", "county");
  }
</script>
 
 
<div> 
  <label for="state">State:</label>
  <select name="state" id="state">
    <option value="Please select...">Please select...</option>
    <option value="S1">State 1</option>
    <option value="S2">State 2</option>
    <option value="S3">State 3</option>
    <option value="S4">State 4</option>
  </select>
</div>
<div> 
  <label for="county">County:</label>
  <select name="county" id="county">
    <option value="Please select...">Please select...</option>
    <option class="S1" value="C1">County 1</option>
    <option class="S1" value="C2">County 2</option>
    <option class="S1" value="C3">County 3</option>
    <option class="S2" value="C4">County 4</option>
    <option class="S3" value="C5">County 5</option>
    <option class="S3" value="C6">County 6</option>
    <option class="S4" value="C7">County 7</option>
    <option class="S4" value="C8">County 8</option>
    <option class="S4" value="C9">County 9</option>
    <option class="S4" value="C10">County 10</option>
  </select>
</div>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yxz117Author Commented:
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?
0
sbickerstaffCommented:
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?
0
sbickerstaffCommented:
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 :)
0
yxz117Author Commented:
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
0
yxz117Author Commented:
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.
0
sbickerstaffCommented:
great to hear you got it to work :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.