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. [xml]
jQuery UI Multi-Autocomplete Example

Start typing the name of a state or territory of the United States

[/xml]

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. [js] $(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); } }); }); [/js]

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

[php] /* 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); [/php]

Non-PDO version

[php] Error connecting to mysql