Ajax autocomplete using PHP & MySQL

Post Date: Jun 15 2009
Total Visit: 84523

Today I have decided to implement an Ajax (jQuery) auto complete feature in PHP & MySql. jQuery makes remote scripting a piece of cake and that led me to spend more time coding additional functionalities for the auto-complete field. In this post, I'll explain how to use my autocomplete field and in a following post I'll explain all the code. So let's start!

Before continuing let's see My Demo to how it will work.

Demo | Download

Besides the auto-complete code we need the jQuery library along with its Dimensions plug-in (all files are included in the zip files). Let's include in the head of your page:

<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="dimensions.js"></script>
<script type="text/javascript" src="autocomplete.js"></script>

Now we need to call the function that will bring data to our auto-complete field - setAutoComplete.

<script type="text/javascript">
$(function(){      setAutoComplete("searchField", "results", "autocomplete.php?part=");  });
</script>

The call to setAutoComplete is inside a jQuery special code that is only executed when the DOM is ready, or in other words, when all the code is already loaded. The setAutoComplete function takes 3 parameters:

  • the id of the input field
  • the id of the div that will hold the returned data
  • the URL of the remote script that will process the request

Be aware that the URL should reflect your remote script and that it will be combined with the text typed in the input field.

Now we include our stylesheet to define the look of the elements. We  need to define the styles of the div that will contain the results,  they include two classes for the selected and unselected items. Take a  look at the CSS file linked at the end of this post. There is also a  style for the input field.

<link rel="stylesheet" href="autocomplete.css" type="text/css">

To finish the client side part now we need to define the code of the input field as follows:

<label>Colors: </label><input type="text" id="searchField" name="searchField">

Now that the client side is finished it's time for  the server script. Here is an example of a script that try to match  colors. I'm using PHP but you can use whatever language you prefer for  the job, of course. You just need to return the results as an array  encoded as JSON.

$link = mysql_connect('localhost', 'dbUsername', 'dbPassword');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
if (!mysql_select_db("database")) {
echo "Unable to select mydbname: " . mysql_error();
exit;
}
$result = mysql_query("SELECT name FROM  sks_color");
while ($row = mysql_fetch_assoc($result)) {
$colors[]=$row['name'];
}
mysql_free_result($result);
mysql_close($link);        // check the parameter
if(isset($_GET['part']) and $_GET['part'] != '')
{
// initialize the results array
$results = array();        // search colors
foreach($colors as $color)
{
// if it starts with 'part' add to results
if( strpos($color, $_GET['part']) === 0 ){
$results[] = $color;
}
}         // return the array as json with PHP 5.2
echo json_encode($results);
}

Now every things done! As said before, we return the data as an array encoded as JSON. If you are running PHP >= 5.2.0 or the json extension you simply use json_encode for the job.

You should have a working auto-complete field by now! Note that we don't need to explicit create the result div because it'll be created automatically.

Tags: PHP, Mysql, Ajax, jQuery autocomplete, Ajax autocomplete using PHP & MySQL Bookmark and Share

To do articles more effectively, we need your comments or suggestions on how to improve the articles and make it more useful, or what other articles you would like in the future. Please CLICK HERE and leave your feedback. Thanks!!!