×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Forms

3 related select box by GUJUm0deL
Posted: 27 Mar 07 (Edited 27 Mar 07)

CODE

<!--- PART 1: QUERY --->
    <!--- QUERY THE dB TO GET BACK RESULTS OF COUNTRY, STATE AND CITY --->
    <cfquery name="qryLocationInfo" datasource="#DB#">
        SELECT     distinct Country, State, City
        FROM     Location
        GROUP BY Country, State, City
        ORDER BY Country, State, City
    </cfquery>
    
    This is what the aboce query will return in my example:
    ID,Country,State,City
    1,Africa,,Johannesburg
    2,Argentina,,Buenos Aires
    3,United States,Arizona,Phoenix
    4,United States,Arizona,Tucson
    5,United States,California,Los Angeles
    6,United States,California,Sacramento
    7,United States,Wisconsin,Madison
    8,United States,Wyoming,Cheyenne
    9,Venezuela,,Maracaibo
    10,Venezuela,,Valencia
    Note: Africa, Argentina, and Venezuela have NO State value.  ONLY United States has State value
    

<!--- PART 2: JS CODE --->
    <!--- 3 related select box --->
    <script language="JavaScript">
        //create the initial array to hold the different records.
        var aryLocation = new Array();
        
        <!--- set a variable for our loop to insert the data. The loop starts at zero because JavaScript arrays start at zero. --->
        <cfset Variables.JSLoop = 0>
        <cfoutput>
            <cfloop query="qryLocationInfo">
                aryLocation[#Variables.JSLoop#] = new Array("#qryLocationInfo.Country#", "<cfif #qryLocationInfo.State# EQ "">Select City<cfelse>#qryLocationInfo.State#</cfif>", "#qryLocationInfo.City#");
                <cfset Variables.JSLoop = Variables.JSLoop + 1>
            </cfloop>
        </cfoutput>
        
        function tsrUpdSelect(frstSel, scndSel, thrdSel, thisSel) {
            //local variables
            var i; var chkCty = "";
        
            //see if we just changed the first select box.
            if(thisSel.name == frstSel.name) {
                //set the length of the other selects to zero to empty them
                scndSel.options.length = 0;
                thrdSel.options.length = 0;
        
                //set the first option for each.
                scndSel.options[scndSel.length] = new Option("Choose State", "");
                thrdSel.options[thrdSel.length] = new Option("Choose City", "");
        
                //Now loop through and set the second option list.
                //If a state was chosen. There is also code to prevent duplicate cities. This could happen if a city had multiple addresses.
                if(thisSel.options[thisSel.selectedIndex].value != "") {
                    for (i = 0; i < aryLocation.length; i++) {
                        if(aryLocation[i][0] == thisSel.options[thisSel.selectedIndex].value && chkCty.lastIndexOf(aryLocation[i][1]) == -1) {
                            scndSel.options[scndSel.length] = new Option(aryLocation[i][1], aryLocation[i][1]); chkCty = chkCty + "," + aryLocation[i][1];
                        }
                    }
                }
            }
        
            //see if we just changed the second select box.
            if(thisSel.name == scndSel.name) {
                //set the length of the third select to zero.
                thrdSel.options.length = 0;
                //set the first option.
                thrdSel.options[thrdSel.length] = new Option("Choose City", "");
                //set the rest of the values if a city was chosen.
                for(i=0; i < aryLocation.length; i++) {
                    if(aryLocation[i][0] == frstSel.options[frstSel.selectedIndex].value && aryLocation[i][1] == scndSel.options[scndSel.selectedIndex].value) { thrdSel.options[thrdSel.length] = new Option(aryLocation[i][2], aryLocation[i][2])
                    }
                }
            }
        }
    </script>

<!--- PART 3: FORM ELEMENT --->
<cfset countryList = "">
<select name="myCountry" onchange="tsrUpdSelect(this, formName.myState, formName.myCity, this);">
    <option value="" selected>Choose Country</option>
    <!--- LOOP OVER THE QUERY RESULTS --->
    <cfloop query="qryLocationInfo">
        <!--- SET UNIQUE 'COUNTRY' NAME TO THE VARIABLE countryList --->
        <cfif not listFindNoCase(countryList, qryLocationInfo.Country, ",")>
            <option value="#qryLocationInfo.Country#"> #qryLocationInfo.Country# </option>
            <cfset countryList = listAppend(countryList, qryLocationInfo.Country, ",")>
        </cfif>
    </cfloop>
</select>
&nbsp;
<select name="myState" onchange="tsrUpdSelect(formName.myCountry, this, formName.myCity, this);">
    <option value="">Choose State</option>
</select>
&nbsp;
<select name="myCity">
    <option value="">Choose City</option>
</select>


NOTES:

CODE

aryLocation[#Variables.JSLoop#] = new Array("#qryLocationInfo.Country#", "<cfif #qryLocationInfo.State# EQ "">Select City<cfelse>#qryLocationInfo.State#</cfif>", "#qryLocationInfo.City#");
The portion in red allows you to notify the user that this field has no selections to make, so they need to select the City (the third drop-down).  This is important for cases where the Country has no State yet you want to alert the user to select the City.  Example:  If the user selects Argentina as the Country then they cannot select a State (see query dump above), the user must select City.

CODE

scndSel.options[scndSel.length] = new Option("Choose State", "");
      thrdSel.options[thrdSel.length] = new Option("Choose City", "");
The portion in blue allows you to define what default value you want to display for the second and thrid drop-down.

The portion in green calls the JS function to populate the drop-down accordingly.

The JS code should stay intact (unless you want to do something special).

If you have any questios, please let me know!


Back to Adobe FAQ Index
Back to Adobe Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close