Coast Line
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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=" ">
<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">
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
Have you check the CF documentation on this same topic for ajax
ASKER
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.
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.
ASKER
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">
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.
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.
ASKER
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">
<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>
<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>
116 : <cfselect name="city" bindOnLoad="yes" bind="cfc:statecity({CitySelect})"/>
117 : </td>
118 : </tr>
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.
so
STATECITY.CitySelect
(I usually use just lower annotation case although CF allows for mixed). Consider this for future development.
ASKER
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">
<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.
ASKER
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">
ASKER
Although i did not implemented This Way but you provided me a valuable information like table and custom tag So many thanks For this
ASKER
i shall be very thankful to you