Link to home
Start Free TrialLog in
Avatar of Coast Line
Coast LineFlag for Canada

asked on

Dynamically change state and city drop-down boxes using coldfusion and ajax

Hi i want that i select one city and in another drop down the cities of that state should be populated automatically.

please also guide me how i make the database of city and state and link them in my users table.

Please Guide me
ASKER CERTIFIED SOLUTION
Avatar of galadore
galadore
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Coast Line

ASKER

Thanks but can u guide me with the database what tables and fields i should do.

i shall be very thankful to you
Only need one table that has the city and states all listed.  I've attached an Access DB called freezip that has a complete US city/state/zip/lat/lon list that anyone can use.  It's old, but it's not like new cities pop up all the time...

Now, follow the directions that are in the HTML file I attached earlier.
FreeZip.mdb
I found the TwoSelectsRelated custom tag on my machine.  Attached below...
<!--- REMOVE THIS AND FIRST LINE IF NOT USING CF3.1 OR LATER --->
<CFSETTING ENABLECFOUTPUTONLY="YES">
 
<!--- TAG PARAMETERS --->
<CFPARAM NAME="Attributes.Query">  
<CFPARAM NAME="Attributes.Display1">
<CFPARAM NAME="Attributes.Display2">
<CFPARAM NAME="Attributes.Value1" DEFAULT="#Attributes.Display1#">
<CFPARAM NAME="Attributes.Value2" DEFAULT="#Attributes.Display2#">
<CFPARAM NAME="Attributes.Name1" DEFAULT="TwoSelectsRelated1">
<CFPARAM NAME="Attributes.Name2" DEFAULT="TwoSelectsRelated2">
<CFPARAM NAME="Attributes.Size1" DEFAULT="1">
<CFPARAM NAME="Attributes.Size2" DEFAULT="1">
<CFPARAM NAME="Attributes.Width1" DEFAULT="">
<CFPARAM NAME="Attributes.Width2" DEFAULT="">
<CFPARAM NAME="Attributes.selected1" DEFAULT="">
<CFPARAM NAME="Attributes.selected2" DEFAULT="">
<CFPARAM NAME="Attributes.ForceWidth1" DEFAULT="">
<CFPARAM NAME="Attributes.ForceWidth2" DEFAULT="">
<CFPARAM NAME="Attributes.ForceWidthChar" DEFAULT="&nbsp;">
<CFPARAM NAME="Attributes.EmptyText1" DEFAULT="">
<CFPARAM NAME="Attributes.EmptyText2" DEFAULT="">
<CFPARAM NAME="Attributes.Message1" DEFAULT="You must choose an option for #Attributes.Name1#.">
<CFPARAM NAME="Attributes.Message2" DEFAULT="You must choose an option for #Attributes.Name2#.">
<CFPARAM NAME="Attributes.FormName" DEFAULT="forms[0]">
<CFPARAM NAME="Attributes.HTMLBetween" DEFAULT="">
<CFPARAM NAME="Attributes.OnChange" DEFAULT="">
<CFPARAM NAME="Attributes.AutoSelectFirst" DEFAULT="Yes">
 
 
<CFSET FunctionName = ReplaceList(Attributes.FormName, "[,]", ",") & "ChangeMenu()">
 
 
<!--- "MAGIC" SHORTCUTS FOR THE ONCHANGE HANDLER --->
<CFIF Attributes.OnChange is "Jump!">
  <CFSET Attributes.OnChange = "document.location = this.options[selectedIndex].value;">
<CFELSEIF Attributes.OnChange is "Submit!">
  <CFSET Attributes.OnChange = "this.form.submit();">
</CFIF>
 
 
 
<!--- USE PASSED QUERY WITHIN THIS CODE AS "MyQuery" --->
<CFSET MyQuery = Evaluate("Caller.#Attributes.Query#")>
 
 
<!--- BEGIN JAVASCRIPTING --->
<CFOUTPUT>
	<SCRIPT LANGUAGE="JavaScript1.1">
	  // javascript code generated by the CF_TwoSelectsRelated Cold Fusion tag (Nate Weiss, 4/98)
		// portions adapted from Nick Heinle's code at http://webreference.com/javascript/960902/select_boxes.html
	  var maxlength = 10;
	  OneA = new Array;
 
		var trueLength = OneA.length;  
		var lst = OneA.length;              
 
    function require_#Attributes.Name1#() {
		  with (document.#Attributes.FormName#.#Attributes.Name1#) {
			  RetVal = true;
			  if (options[selectedIndex] == null) RetVal = false;
				  else RetVal = !(options[selectedIndex].value == '');
			  if (!RetVal) alert('#Attributes.Message1#');
				return RetVal
			}
		}
 
    function require_#Attributes.Name2#() {
		  with (document.#Attributes.FormName#.#Attributes.Name2#) {
			  RetVal = true;
			  if (options[selectedIndex] == null) RetVal = false;
				  else RetVal = !(options[selectedIndex].value == '');
			  if (!RetVal) alert('#Attributes.Message2#');
				return RetVal
			}
		}
		
		function require_#Attributes.Name1#And#Attributes.Name2#() {
		  return ((require_#Attributes.Name1#()) && (require_#Attributes.Name2#()));
		}
		
				
		function #FunctionName# {
		   OneA.length = 0;
		   menuNum = document.#Attributes.FormName#.#Attributes.Name1#.selectedIndex;
		   if (menuNum == null) return;  
       <!--- this function gets continued in the next CFOUTPUT section --->
</CFOUTPUT>
 
 
<!--- COUNTER VARIABLE WILL HOLD NUMBER OF GROUPS (OPTIONS IN FIRST SELECT) --->
<CFSET Counter = IIF(Attributes.EmptyText1 is not "", 1, 0)>
 
<!--- CREATE AN "IF" STATEMENT THAT COVERS EACH ITEM IN THE FIRST SELECT BOX --->
<!--- WITHIN THE "IF" STATMENT, PRE-POPULATE ARRAY WITH CORRESPONDING ITEMS FOR SECOND SELECT  --->
<CFOUTPUT QUERY="MyQuery" GROUP="#Attributes.Display1#">
	if (menuNum == #Counter#) {
	  NewOpt = new Array;
		NewVal = new Array;
    <CFSET Counter2 = IIF(Attributes.EmptyText2 is not "", 1, 0)>
    <CFIF Attributes.EmptyText2 is not ""><CFOUTPUT>NewOpt[0] = new Option("#Attributes.EmptyText2#");	NewOpt[0].value = '';	</CFOUTPUT></CFIF>
		<CFOUTPUT>NewOpt[#Counter2#] = new Option("#Evaluate(Attributes.Display2)#");	NewOpt[#Counter2#].value = '#Evaluate(Attributes.Value2)#'; <CFSET Counter2 = Counter2 + 1></CFOUTPUT>
	} <CFSET Counter = Counter + 1>
</CFOUTPUT>
 
 
<!--- finish up the ChangeMenu() function --->
<CFOUTPUT>
  tot = NewOpt.length;
	lst = document.#Attributes.FormName#.#Attributes.Name2#.options.length;
	
	for (i = lst; i > 0; i--) {
	  document.#Attributes.FormName#.#Attributes.Name2#.options[i] = null;
	}
  for (i = 0; i < tot; i++) {
	  document.#Attributes.FormName#.#Attributes.Name2#.options[i] = NewOpt[i];
	}
  <CFIF Attributes.AutoSelectFirst is "Yes">
	  document.#Attributes.FormName#.#Attributes.Name2#.options[0].selected = true;
	</CFIF>
}
 
 
 
 
</SCRIPT>
</CFOUTPUT>
<!--- DONE WITH JAVASCRIPTING.  NOW WE JUST HAVE TO DISPLAY THE FORM ELEMENTS --->
 
 
<!--- ALLOW FOR AUTO-SIZING "SHORTCUT" OF SELECT BOXES --->
<CFIF Attributes.Size1 is "Auto">
  <!--- MAKE THE FIRST SELECT BE BIG ENOUGH FOR ALL OF ITS OPTIONS --->
  <CFSET Attributes.Size1 = Counter>
</CFIF>
<CFIF Attributes.Size2 is "Auto">
  <!--- MAKE THE SECOND SELECT BE THE SAME SIZE AS THE FIRST --->
  <CFSET Attributes.Size2 = Attributes.Size1>
</CFIF>
 
 
 
<!--- OUTPUT FIRST SELECT BOX --->
<CFOUTPUT><SELECT NAME="#Attributes.Name1#" onChange="#FunctionName#" SIZE="#Attributes.Size1#" <CFIF Attributes.Width1 is not "">STYLE="width:#Attributes.Width1#"</CFIF> class="dropdown"></CFOUTPUT>
	<!--- SPECIAL FIRST ITEM, IF REQUESTED --->
	<CFIF Attributes.EmptyText1 is not ""><CFOUTPUT><OPTION VALUE="">#Attributes.EmptyText1#</CFOUTPUT></CFIF>
	<!--- GENERATE REMAINING ITEMS FROM QUERY --->
	<CFOUTPUT QUERY="MyQuery" GROUP="#Attributes.Display1#"><OPTION VALUE="#Evaluate(Attributes.Value1)#"<cfif Attributes.selected1 is not ""><cfif Attributes.selected1 is Evaluate(Attributes.Value1)>Selected</cfif></cfif>>#Evaluate(Attributes.Display1)#</CFOUTPUT>
	
  <!--- "FORCE WIDTH" OPTION AT BOTTOM, IF REQUESTED --->
	<CFIF Attributes.ForceWidth1 is not ""><CFOUTPUT><OPTION VALUE="">#RepeatString(Attributes.ForceWidthChar, Attributes.ForceWidth1)#</CFOUTPUT></CFIF>
<CFOUTPUT></SELECT></CFOUTPUT>
 
 
<!--- INSERT ANY REQUESTED HTML BETWEEN THE TWO SELECT BOXES --->
<CFOUTPUT>#Attributes.HTMLBetween#</CFOUTPUT>
 
 
 
<!--- OUTPUT SECOND SELECT BOX --->
<CFOUTPUT><SELECT NAME="#Attributes.Name2#" SIZE="#Attributes.Size2#" <CFIF Attributes.onChange is not "">onChange="#Attributes.OnChange#"</CFIF> <CFIF Attributes.Width2 is not "">STYLE="width:#Attributes.Width2#"</CFIF> class="dropdown"></CFOUTPUT>
	<!--- SPECIAL FIRST ITEM, IF REQUESTED --->
	<CFIF Attributes.EmptyText2 is not ""><CFOUTPUT><OPTION VALUE="">#Attributes.EmptyText2#</CFOUTPUT></CFIF>
	
	<!--- GENERATE REMAINING ITEMS FROM QUERY --->
	<!--- WE ONLY NEED TO OUTPUT THE CHOICES FOR THE FIRST GROUP --->
	<CFSET FirstGroup = Evaluate("MyQuery.#Attributes.Display1#")>
	<CFIF Attributes.EmptyText1 is "">
		<CFLOOP QUERY="MyQuery">
		  <CFIF Evaluate(Attributes.Display1) is FirstGroup>
			  <CFOUTPUT><OPTION VALUE="#Evaluate(Attributes.Value2)#" <cfif Attributes.selected2 is not ""><cfif Attributes.selected2 is Evaluate(Attributes.Value2)>Selected</cfif></cfif>>#Evaluate(Attributes.Display2)#</CFOUTPUT>
			<CFELSE>
			  <CFBREAK>
			</CFIF>
		</CFLOOP>
	</CFIF>	
 
  <!--- "FORCE WIDTH" OPTION AT BOTTOM, IF REQUESTED --->
	<CFIF Attributes.ForceWidth2 is not ""><CFOUTPUT><OPTION VALUE="">#RepeatString(Attributes.ForceWidthChar, Attributes.ForceWidth2)#</CFOUTPUT></CFIF>
<CFOUTPUT></SELECT></CFOUTPUT>
 
<cfif Attributes.selected2 is not "">
<CFOUTPUT>
<script>
#FunctionName#
document.f.#Attributes.Name2#.value='#Attributes.selected2#'
</script>
</cfoutput>
</cfif>
 
 
<!--- REMOVE THIS AND FIRST LINE IF NOT USING CF3.1 OR LATER --->
<CFSETTING ENABLECFOUTPUTONLY="NO">

Open in new window

Avatar of hanno62
hanno62

But two-selectrelated tag is not Ajax, as you will load all in advance from what I see.

Have you check the CF documentation on this same topic for ajax
not yet!, i am looking for something in ajax, but could not find the one, i hope tere is some bind stuff with two cfselect statement but i am really overdue what to do<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
It is not that diffcult once

Put two <cfselect> on your form

The first one

<cfselect name="state">
  <loop on your query for distinct State>, just like you always have done as you need the initial select  for your state (no need for ajax here)
</cfselect>

<cfselect name="city"
   bindOnLoad="yes"      
   bind="cfc:city({state})"/>                        
                        
Now make a cfc called city.cfc
city.cfc

<cfcomponent>

    <cfproperty name="name" type="string">
    <cfset this.name = "mytest">
            
      <cffunction name="CitySelect"
             access="remote"
             returntype="array">

 <cfargument name="State"    type="string" required="true">
.
<cfquery name="myquery">
SELECT CityCode, CityName FROM Table WHERE State = '#state#'
</cfquery>
                    
 <cfset list = arraynew(2)>
                                         
<!--- Convert results to array --->
<cfloop index="i" from="1" to="#myquery.RecordCount#">
          <cfset list[i][1]= myquery.CityCode[i]>
               <cfset list[i][2]= myquery.CityName[i]>
</cfloop>
            
<cfreturn list>
            
</cffunction>
            
</cfcomponent>      

Hanno, Guatemala

I am sure there are plenty of example on the internet.


Your approach quite good but if you can guide me with some things, i shall be able to set up this soon.

i have database table zips which have

zipcode
state
place

but i want to use only state and place.

in my users database i have

state
city as two fields datafield being varchar.

can u please guide me step procedure how i set this up. i am really lost here
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
My code is for selecting a state and then a city.

That you later save the fields into a user database, you can do with a regular submit in which you insert or update a users database. I assume this is not your problem.

Can you confirm that at least you go the input fields working as i outline above ? Then we get it to the next stage.




ok i did something like this. all code will be in attach window
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
query i used to fetch states as i have 29,000 records:
 
SELECT DISTINCT(state) as sstate 
        from zips 
        GROUP by state
 
<tr><td><strong>State</strong></td>
      <td><select name="state" class="textfield_effect">
      	<cfoutput query="getData">
        	<option value="#sstate#" <cfif sstate eq #state#>selected</cfif>>#sstate#</option>
        </cfoutput>
      </select>&nbsp;
      <cfselect name="city" bindOnLoad="yes" bind="cfc:statecity({CitySelect})"/>   
      </td>
      </tr>
 
CFC name is:STATECITY.CFC 
 
<cfcomponent>
<cfproperty name="name" type="string">
<cffunction name="CitySelect" access="remote" returntype="array">
  <cfargument name="State" type="string" required="true">
  <cfquery name="myset" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
	SELECT city,state 
    FROM zips 
    WHERE State = <cfqueryparam cfsqltype="cf_sql_varchar" value="#state#">
  </cfquery>
  <cfset list = arraynew(2)>
  <!--- Convert results to array --->
  <cfloop index="i" from="1" to="#myset.RecordCount#">
    <cfset list[i][1]= myquery.city[i]>
    <cfset list[i][2]= myquery.state[i]>
  </cfloop>
  <cfreturn list>
</cffunction>
</cfcomponent>
 
Error is reieve:
 
The web site you are accessing has experienced an unexpected error.
Please contact the website administrator. 
 
The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
CFC function not found in the bind expression cfc:statecity({CitySelect})
 
 
The error occurred in C:\Inetpub\wwwroot\ibestcity\account.cfm: line 116
Called from C:\Inetpub\wwwroot\ibestcity\allpages.cfm: line 22
Called from C:\Inetpub\wwwroot\ibestcity\index.cfm: line 93
114 :         </cfoutput>
115 :       </select>&nbsp;
116 :       <cfselect name="city" bindOnLoad="yes" bind="cfc:statecity({CitySelect})"/>   
117 :       </td>
118 :       </tr>

Open in new window

The bind should link to the function

so
STATECITY.CitySelect

(I usually use just lower annotation case although CF allows for mixed). Consider this for future development.

Thanks i bind it but it does load the contents. i used the following cfc:


<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<cfcomponent>
<cfproperty name="name" type="string">
<cffunction name="CitySelect" access="remote" returntype="array">
  <cfargument name="State" type="string" required="true">
  <cfquery name="myset" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
	SELECT place,state 
    FROM zips 
    WHERE State = <cfqueryparam cfsqltype="cf_sql_varchar" value="#state#">
  </cfquery>
  <cfset list = arraynew(2)>
  <!--- Convert results to array --->
  <cfloop index="i" from="1" to="#myset.RecordCount#">
    <cfset list[i][1]= myquery.place[i]>
    <cfset list[i][2]= myquery.state[i]>
  </cfloop>
  <cfreturn list>
</cffunction>
</cfcomponent>
 
<cfselect name="city" bindOnLoad="yes" bind="cfc:statecity.CitySelect({State})"/>   
 
i checked firebug and got this response:
 
it just return this :
 
 []
 
without any value in it.

Open in new window

hi experts, any update provide me please how i do this <input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
Although i did not implemented This Way but you provided me a valuable information like table and custom tag So many thanks For this