29
Using jQuery Autocomplete to Populate Another Autocomplete – ASP.NET, ColdFusion, and PHP Examples
.net, ColdFusion, jquery, php
Tags: .net, ColdFusion, jquery, php
Share
As requested, this post covers using one jquery autocomplete to populate another jquery autocomplete on the same page. This example will use a jquery autocomplete to choose a state then, based on the state, another jquery autocomplete will be populated with zip codes for that state. Basically, the state chosen gets used as a filter in the query for the second autocomplete.
Form
The form for the examples is the same with fields for the autocompletes (state and zip_code) plus input fields for the values returned by the autocompletes.
<form action="YOUR-FILE-HERE" method="post"> <fieldset> <legend>jQuery UI Multi-Autocomplete Example</legend> <p>Start typing the name of a state or territory of the United States</p> <p><label for="state">State (abbreviation in separate field): </label> <input type="text" id="state" name="state" /> <input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></p> <input type="hidden" id="state_id" name="state_id" /> <p><label for="zip_code">Zip (only Zips from state selected above): </label> <input type="text" id="zip_code" name="zip_code" maxlength="5" size="15" /></p> <p class="ui-widget"><label for="city">City:</label> <input readonly="readonly" type="text" id="city" name="city" /></p> <p><input type="submit" name="submit" value="Submit" /></p> </fieldset> </form>
jQuery
The jquery is also the same for each example. Notice that the zip_code field is initially disabled to prevent entry before the results have been filtered.
Also, note that the source and secondary URL extension will have to be modified depending on the language you are using.
$(function() {
//clear values on refresh
$('#abbrev').val("");
$('#city').val("");
$("#zip_code").attr('disabled', true);
$("#state").autocomplete({
source: "states.[cfm|aspx|php]",
minLength: 2,
select: function(event, ui) {
$('#state_id').val(ui.item.id);
$('#abbrev').val(ui.item.abbrev);
$("#zip_code").attr('disabled', false);
},
change: function(event, ui){
secondary_url = "zips.[cfm|aspx|php]?filter=" + ui.item.abbrev;
$("#zip_code").autocomplete("option", "source", secondary_url);
}
});
$("#zip_code").autocomplete({
source: "",
minLength: 2,
select: function(event,ui){
$('#city').val(ui.item.city);
}
});
});
Processing
Each programming language differs slightly in the processing. And, since there are hundreds of zip codes per state, the results are limited to 20. If the zip code desired is not returned in the top 20 after entering the minimum 2 characters, typing more characters will yield more precise results.
ASP.NET
Refer to the complete example of using the jquery autocomplete with ASP.NET for more information.
<%@ Page Language="VB" Debug="false" %>
<%@ Import Namespace="System.Web.Script.Serialization" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Dim serializer As JavaScriptSerializer
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
serializer = New JavaScriptSerializer()
Response.Write(JSONData(Request.QueryString("Term")))
End Sub
Public Class Zip
Public zipcode As String
Public label As String
Public value As String
Public city As String
End Class
Private Function JSONData(ByVal term As String) As String
Dim zipArray As New ArrayList
Dim index As Integer = 0
Dim mySql As String
Dim objConn As New SqlConnection("Server=YOUR-SERVER;Database=YOUR-DATABASE;User ID=YOUR-USERID;Password=YOUR-PASSWORD")
Dim myds As New DataSet("Zips")
mySql = "SELECT TOP 20 zip, city FROM zips WHERE abbrev = '" + Request.QueryString("filter") + "' AND zip like '" + term + "%'"
objConn.Open()
Dim adapter As New SqlClient.SqlDataAdapter(mySql, objConn)
adapter.Fill(myds, "Zips")
For Each dr As DataRow In myds.Tables(0).Rows
Dim zp As New Zip()
zp.label = dr("zip").ToString() & " " & dr("city").ToString()
zp.value = dr("zip").ToString()
zp.city = dr("city").ToString()
zipArray.Add(zp)
Next
objConn.Close()
Return serializer.Serialize(zipArray)
End Function
</script>
ColdFusion
Refer to the complete example of using the jquery autocomplete with ColdFusion for more information.
ColdFusion’s serializeJSON function has an odd bug that will not let you send numbers as strings. This turns the zip codes into numbers which we do not want. To workaround this, I added a space in front of the zip code then removed it from the JSON output with the Replace function. Not the best idea, but it works.
<cfset returnArray = ArrayNew(1) />
<cfquery name="qryStates" dataSource="autocomplete">
SELECT TOP 20 zip, city FROM zips WHERE abbrev = '#URL.filter#' AND zip like '#URL.term#%'
</cfquery>
<cfloop query="qryStates">
<cfset zipsStruct = StructNew() />
<cfset zipsStruct["label"] = ToString(zip) & " " & city />
<!---Had to add leading space to prevent serializeJSON from turning zip into number--->
<cfset zipsStruct["value"] = " " & zip />
<cfset zipsStruct["city"] = city />
<cfset ArrayAppend(returnArray,zipsStruct) />
</cfloop>
<cfoutput>
<!---replaced all spaces after quotes with just quotes in JSON to remove leading space applied to zip--->
#Replace(serializeJSON(returnArray),'" ','"',"all")#
</cfoutput>
PHP
Refer to the complete example of using the jquery autocomplete with PHP for more information.
This PHP example using SQL Server as the database. The complete example linked above uses MySQL.
<?php
$dbhost = 'YOUR_SERVER';
$dbuser = 'YOUR_USERNAME';
$dbpass = 'YOUR_PASSWORD';
$dbname = 'YOUR_DATABASE_NAME';
$conn = mssql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mssql');
mssql_select_db($dbname);
$return_arr = array();
if ($conn)
{
$fetch = mssql_query("SELECT TOP 20 zip, city FROM zips WHERE abbrev = '" . $_GET['filter'] . "' AND zip like '" . $_GET['term'] . "%'");
/* Retrieve and store in array the results of the query.*/
while ($row = mssql_fetch_array($fetch)) {
$row_array['label'] = $row['zip'] . " " . $row['city'];
$row_array['value'] = $row['zip'];
$row_array['city'] = $row['city'];
array_push($return_arr,$row_array);
}
}
/* Free connection resources. */
mssql_close($conn);
/* Toss back results as json encoded array. */
echo json_encode($return_arr);
?>
If this post helped you out, please consider donating to help pay the hosting fees. 100% of the donations go to the web host.
Further Reading:
6 Comments for Using jQuery Autocomplete to Populate Another Autocomplete – ASP.NET, ColdFusion, and PHP Examples
hello,
your code is confusing,
i would be thankful if you can show me a way to autocomplete a textbox with jquery in asp.net c# or asp.net vb.net by fetching values from a tiny database with the following structure
Indicators: # column names | the rest are values
#state | #city1 | #city2 | #city3 | #city4 | #city5
abc | NY | GY | LY | ZY | OY
def | MY | OUR | YOU | NO | HO
If possible to be completed by a day
Thanks
@Avishek
This post is a continuation of a previous post on doing what you ask. That post is mentioned in the ASP.NET section above but here is the link:
http://www.jensbits.com/2010/04/14/jquery-ui-autocomplete-widget-with-asp-net/
e-media
June 4, 2010
Fine, very good article again.
Thank you
udien
June 22, 2010
this is very good tutorial..
its works perfectly..
thanks
stantonh
August 7, 2010
To enable zips.php to use _POST vars instead of _GETs, how does the "change function" need to be altered to set the value of filter? Thanks.
zips.php uses _GETS because that is how the jquery ui autocomplete is coded to work. So, I am not sure it is worth the trouble to reprogram the jquery code itself.
Leave a comment
« Generating Signatures in ColdFusion with RSA-SHA1 for Secure AuthSub in Google Analytics | jQuery Modal Dialog Close on Overlay Click »

Avishek Kumar
June 1, 2010