jensbits

You might also be interested in the Using jQuery Autocomplete to Populate Another Autocomplete post.

As a follow up to the jQuery UI Autocomplete Widget with ColdFusion post, I did one with PHP as the backend.

The jQuery UI folks have released an autocomplete widget that is pretty slick. This example uses the json_encode function in PHP 5. If you have an earlier version of PHP, you will have to roll your own JSON string.

This example will use US states and territories to populate the autocomplete. It will also demonstrate how to fill other fields with data returned from the database. This data can be used to fill a visible text box or a hidden form field. It also demonstrates the basic autocomplete functionality which may be fine for some applications.

Of course, you will need the jQuery core file, the jQuery UI core file, and the jQuery UI style sheet of choice. The style sheet comes from the themes available in the jQuery UI website and can be downloaded with the core file or you can link to the latest versions of both the core files and the css:

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/redmond/jquery-ui.css">

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>

<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>

The HTML is straight forward and stripped down for the example:

<form action="<?php echo $PHP_SELF;?>"  method="post">
<fieldset>
<legend>jQuery UI Autocomplete Example - PHP Backend</legend>

<p>Start typing the name of a state or territory of the United States</p>

<p class="ui-widget">

<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 class="ui-widget">

<label for="state_abbrev">State (replaced with abbreviation): </label>

<input type="text" id="state_abbrev" name="state_abbrev" /></p>

<p><input type="submit" name="submitBtn" value="Submit" /></p>

</fieldset>
</form>

As a bonus, we dump out the form values to see what we have right underneath the form itself:

<?php
if (isset($_POST['submit'])) {
echo "<p>";
	while (list($key,$value) = each($_POST)){
	echo "<strong>" . $key . "</strong> = ".$value."<br />";
	}
echo "</p>";
}
?>

And the jQuery on the page is equally brief:

$(function() {

            $('#abbrev').val("");

            $("#state").autocomplete({
                source: "states.php",
                minLength: 2,
                select: function(event, ui) {
                    $('#state_id').val(ui.item.id);
                    $('#abbrev').val(ui.item.abbrev);
                }
            });

            $("#state_abbrev").autocomplete({
                source: "states_abbrev.php",
                minLength: 2
            });
        });

Notice that there are two autocomplete functions on the page, one for each example in the demo. Each function calls a different PHP file which return slightly different result sets.

The jquery autocomplete will append the text typed into the autocomplete field as the URL parameter ‘term.’ This URL parameter is used to query the database.

From the jquery documentation:

The request parameter “term” gets added to that URL.

Also, the minLength for autocomplete to return results is set to 2 to prevent too many rows from being returned.

Both PHP pages return the data after a few steps:

1. It queries the database
2. Loops an array of the query results adding each row to a return array
3. Outputs the array as JSON data

The states.php file returns the id field, the state field as ‘value’, and the abbrev field. These values are placed in the appropriate text boxes by the autocomplete jQuery function.

A ‘value’ and/or a ‘label’ field is mandatory for the autocomplete to work. See explanation at bottom of post.

And, of course, you will have to make your own connection to your MySQL database before running the query.

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_term = "%".$_GET['term']."%";
	$query = "SELECT * FROM states where state like :term";
	$result = $conn->prepare($query);
	$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['id'] = $row['id'];
		$row_array['value'] = $row['state'];
		$row_array['abbrev'] = $row['abbrev'];

        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:

/* Connection vars here for example only. Consider a more secure method. */
$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 connection to database, run sql statement. */
if ($conn)
{
	$fetch = mysql_query("SELECT * FROM states where state like '%" . mysql_real_escape_string($_GET['term']) . "%'"); 

	/* Retrieve and store in array the results of the query.*/
	while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
		$row_array['id'] = $row['id'];
		$row_array['value'] = $row['state'];
		$row_array['abbrev'] = $row['abbrev'];

        array_push($return_arr,$row_array);
    }
}

/* Free connection resources. */
mysql_close($conn);

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

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

The states_abbrev.php shows the basic functionality of the autocomplete function by just assigning results of the query to the ‘label’ and ‘value’ fields. 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.”

PDO version:

$return_arr = array();

if ($conn)
{
	$ac_term = "%".$_GET['term']."%";
	$query = "SELECT * FROM states where state like :term";
	$result = $conn->prepare($query);
	$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['state'];
		$row_array['value'] = $row['abbrev'];

        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:

$return_arr = array();

/* If connection to database, run sql statement. */
if ($conn)
{
	$fetch = mysql_query("SELECT * FROM states where state like '%" . mysql_real_escape_string($_GET['term']) . "%'"); 

	/* Retrieve and store in array the results of the query.*/
	while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
		$row_array['label'] = $row['state'];
		$row_array['value'] = $row['abbrev'];

        array_push($return_arr,$row_array);
    }
}
/* Free connection resources. */
mysql_close($conn);

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

Usual recommended jQuery and PHP reading:

Demo

Download zip of all files

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:

1. jQuery UI Autocomplete Widget with Perl and MySQL
2. Using jQuery Autocomplete to Populate Another Autocomplete – ASP.NET, ColdFusion, and PHP Examples
3. jQuery UI Autocomplete Widget with ColdFusion
4. jQuery UI Autocomplete Widget with ASP.NET VB