Jquery autocomplete and PHP: populating input field with data from mySQL database based on selected option in autocomplete field
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




  
Jquery autocomplete and PHP: populating input field with data from mySQL database based on selected option in autocomplete field
===

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




  