Solved

populate a dropdown list based on another dropdown list

Posted on 2008-10-20
12
1,447 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:yxz117
  • 6
  • 5
12 Comments
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 22762545
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
 
LVL 7

Expert Comment

by:mltsy
ID: 22762875
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
 

Author Comment

by:yxz117
ID: 22763753
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
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 22766349
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
 

Author Comment

by:yxz117
ID: 22767261
Thanks, sbickerstaff. I can wait.
0
 
LVL 3

Accepted Solution

by:
sbickerstaff earned 125 total points
ID: 22770478
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:yxz117
ID: 22773119
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
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 22774131
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
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 22774163
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
 

Author Comment

by:yxz117
ID: 22782908
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
 

Author Comment

by:yxz117
ID: 22792239
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
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 22794108
great to hear you got it to work :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now