Jquery自动完成和PHP：根据自动完成字段中的选定选项，使用mySQL数据库中的数据填充输入字段

Question

I am trying to populate a Postcode (ie zipcode) input field with data from a mySQL database, based on the user's selected option of Suburbs from a jQuery autocomplete field.

The autocomplete works fine - the filtered Suburbs list is retrieved based on the input terms from the user. The source reference is a PHP file. But I can't figure out how to then use the user's selected option to call back to the database to retrieve the postcode. Possibly the postcode can be retrieved in the first call, at the same time as the suburbs: Except I don't want all of the postcodes, just the one that the user ends up selecting.

My jQuery is as follows: (The "$('#postcodes')" line doesn't work as yet...)

  <script type="text/javascript" src="js/jquery-1.6.2.min.js"></script>
  <script type="text/javascript" src="js/jquery-ui-1.8.15.custom.min.js"></script>
  <script>
  // autocomplete
  $(function() {
  $( "#suburbs" ).autocomplete({
  source: "allSuburbs.php",
  minLength: 3,
  select: function( event, ui ) {
  $('#postcodes').val(ui.item.postcode);
  },
  });
  });
  </script>

Relevant html:

  <p>Suburb</p><input class="inputText" type="text" 
  size="50" name="term" id="suburbs" maxlength="60" /></td>
  <td><p>State</p><input class="inputText" type="text" 
  size="5" name="" id="states"  maxlength="4" /></td>
  <td><p>Postcode</p><input class="inputText" type="text" 
  size="5" name="" id="postcodes" maxlength="4" /></td>

PHP (allSuburbs.php):

  <?php
  $con = mysql_connect("***","***","***");
  if (!$con) { die('Could not connect: ' . mysql_error()); }
  $dbname = 'suburb_state';
  mysql_select_db($dbname);
  $query = "SELECT name FROM suburbs";
  $result = mysql_query($query);
  if (!$result) die ("Database access failed:" . mysql_error());
  //retrieving the search term that autocomplete sends
  $qstring = "SELECT name FROM suburbs WHERE name LIKE '%".$term."%'";
  //query the database for entries containing the term
  $result = mysql_query($qstring);
  //loop through the retrieved values
  while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
  { $row['name']=htmlentities(stripslashes($row['name']));
  $row['postcode']=htmlentities(stripslashes($row['postcode']));
  $row_set[] = $row['name'];//build an array
  }
  echo json_encode($row_set);//format the array into json data
  mysql_close($con);
  ?>

I've found thse links possibly the most helpful:

http://www.simonbattersby.com/blog/jquery-ui-autocomplete-with-a-remote-database-and-php/ (This helped me initially)

http://www.jensbits.com/2010/05/29/using-jquery-autocomplete-to-populate-another-autocomplete-asp-net-coldfusion-and-php-examples/ (this is the closest to my problem, although it populates the zipcode or postcode field with a range of zipcodes based on a state selection, rather than a single zipcode based on one suburb/city).

Any help appreciated. Thank you kindly, Andrew

Answer 1

I have built exactly this function into an app of mine. There is an additional layer of complexity here, in that there are two suburb lookups (home and work addresses), each populating matching state and postcode fields. The back-end is perl rather than PHP, but that's not relevant to the client-side handling. Ultimately the back-end is returning a JSON structure with an array of hashes like this:

[ { "id":"...", "value":"...", "state":"...", "pcode":"..." }, ... ]

The id key contains the suburb name, and the value key contains strings like "JOLIET IL 60403", so the correct set of data is chosen once, solving the problem of multiple towns/suburbs with the same name in different places, and making call-backs to resolve that.

Once selected, the suburb (id), state and pcode values are injected into the matching parameters.

The following code also caches previous results (and the cache is shared between the home and work lookups).

$('#hm_suburb').addClass('suburb_search').attr(
         {suburb: '#hm_suburb', pcode: '#hm_pcode', state: '#hm_state'});
$('#wk_suburb').addClass('suburb_search').attr(
         {suburb: '#wk_suburb', pcode: '#wk_pcode', state: '#wk_state'});
var sub_cache = {};
$(".suburb_search").autocomplete({
    source: function(request, response) {
        if (request.term in sub_cache) {
                response($.map(sub_cache[request.term], function(item) {
                    return { value: item.value, id: item.id,
                             state: item.state, pcode: item.pcode }
                }))
            return;
        }
        $.ajax({
            url: suburb_url,
            data: "term=" + request.term,
            dataType: "json",
            type: "GET",
            contentType: "application/json; charset=utf-8",
            dataFilter: function(data) { return data; },
            success: function(data) {
                sub_cache[request.term] = data;
                response($.map(data, function(item) {
                    return {
                        value: item.value,
                        id: item.id,
                        state: item.state,
                        pcode: item.pcode
                    }
                }))
            } //,
            //error: HandleAjaxError  // custom method
        });
    },
    minLength: 3,
    select: function(event, ui) {
        if (ui.item) {
            $this = $(this);
            //alert("this suburb field = " + $this.attr('suburb'));
            $($this.attr('suburb')).val(ui.item.id);
            $($this.attr('pcode')).val(ui.item.pcode);
            $($this.attr('state')).val(ui.item.state);
            event.preventDefault();
        }
    }
});