Using jQuery Autocomplete to Populate Another Autocomplete – ASP.NET, ColdFusion, and PHP Examples

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.

PHP

Refer to the example for using the jquery autocomplete with PHP. The code below only shows the zip select database processing.

PDO version

/* Connection vars here for example only. Consider a more secure method. */
$dbhost = 'YOUR_SERVER';
$dbuser = 'YOUR_USERNAME';
$dbpass = 'YOUR_PASSWORD';
$dbname = 'YOUR_DATABASE_NAME';

try {
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}
catch(PDOException $e) {
    echo $e->getMessage();
}

$return_arr = array();

if ($conn)
{
	$ac_filter = $_GET['filter'];
	$ac_term = $_GET['term']."%";
	$query = "SELECT zip, city FROM zips WHERE abbrev = :filter AND zip like :term  LIMIT 20";
	$result = $conn->prepare($query);
	$result->bindValue(":filter",$ac_filter);
	$result->bindValue(":term",$ac_term); 
	$result->execute(); 
	
	/* Retrieve and store in array the results of the query.*/
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
		$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. */
$conn = null; 

/* Toss back results as json encoded array. */
echo json_encode($return_arr);

Non-PDO version

<?php
$dbhost = 'YOUR_SERVER';
$dbuser = 'YOUR_USERNAME';
$dbpass = 'YOUR_PASSWORD';
$dbname = 'YOUR_DATABASE_NAME';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);

$return_arr = array();

if ($conn)
{
	$fetch = mysql_query("SELECT zip, city FROM zips WHERE abbrev = '" . mysql_real_escape_string($_GET['filter']) . "' AND zip like '" . mysql_real_escape_string($_GET['term']) . "%' LIMIT 20"); 
	
	/* Retrieve and store in array the results of the query.*/
	
	while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
		$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. */
mysql_close($conn);

/* Toss back results as json encoded array. */
echo json_encode($return_arr);
?>

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 objConn As New SqlConnection("Server=YOUR-SERVER;Database=YOUR-DATABASE;User ID=YOUR-USERID;Password=YOUR-PASSWORD")
        Dim myds As New DataSet("Zips")
        
        objConn.Open()

        Dim adapter As New SqlDataAdapter("SELECT TOP 20 zip, city FROM zips WHERE abbrev = @ac_filter AND zip like @ac_term + '%'", objConn)
        adapter.SelectCommand.Parameters.Add("@ac_filter", SqlDbType.VarChar)
        adapter.SelectCommand.Parameters("@ac_filter").Value = Request.QueryString("filter")
        adapter.SelectCommand.Parameters.Add("@ac_term", SqlDbType.VarChar)
        adapter.SelectCommand.Parameters("@ac_term").Value = term
        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 = '<cfqueryparam value="#URL.filter#" cfsqltype="cf_sql_varchar">' AND zip like '<cfqueryparam value="#URL.term#" cfsqltype="cf_sql_varchar">%' 
</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>

Very important information below. Please read and understand before expecting the autocomplete to work properly.

Explanation on the ‘label’ and ‘value’ fields from the jQuery UI site:

“The local data can be a simple Array of Strings, or it contains Objects for each item in the array, with either a label or value property or both. The label property is displayed in the suggestion menu. The value will be inserted into the input element after the user selected something from the menu. If just one property is specified, it will be used for both, eg. if you provide only value-properties, the value will also be used as the label.”

Recommended:

Demo using PHP

 

Further Reading:

  1. jQuery.ajax and jQuery.post Form Submit Examples with ColdFusion
  2. jQuery UI Autocomplete Widget with ColdFusion
  3. jQuery.ajax and jQuery.post Form Submit Examples with PHP
  4. jQuery UI Autocomplete Widget with PHP and MySQL
  5. jQuery Autocomplete with HTML in Dropdown Selection Menu
Published May 29, 2010
Posted in .net, ColdFusion, jquery, php. Tags: , , , . Permalink. Both comments and trackbacks are closed.

41 Comments

1 … 3 4 5
  1. jen
    December 23, 2011 at 6:11 pm | Permalink

    @Gily

    If the data coming back from the database is in json format, loop through the json array and append the radio button elements to a div on the page.

1 … 3 4 5