Ajax-jQuery Autocomplete with PHP and MySQL

Auto-complete is plugin for jQuery that allows for a textbox to have that drop down suggestion effect like what Google is using currently. First download the plugin for jquery to do all the auto-complete stuff, you can find that here. Also make sure in include the jquery library’s too. Thus, your includes should look a little like this:

<link rel="stylesheet" type="text/css" href="jquery.autocomplete.css" />
<script type="text/JavaScript" src="jquery-1.4.2.js"></script>
<script type="text/JavaScript" src="jquery.autocomplete.js"></script>

Yup, you see correct there is a css file included there. You are welcome to edit that if you like. I didn’t.

The auto-complete works for text form input objects. Make sure to also add an id to the input field because this is how javascript is going to identify them. Here’s a sample of what I used:

<form action="main.php" method="get">
First Name: <input type="text" name="fname" id="firstname" />
Last Name: <input type="text" name="lname" id="lastname" />
<input type="submit" />
</form>

I used a simple get method form action with id’s of firstname and lastname for their fields.

The javascript call when a user starts typing in the text box form, it will dynamically call the $(“”).autocomplete() script. That code looks like this:

<script type="text/JavaScript">
$(document).ready(function()
{
    $("#firstname").autocomplete("autocomplete.php?arg=1");

    $("#lastname").autocomplete("autocomplete.php?arg=2");
});
</script>

Therefore, if you know javascript you can see that the “#firstname” in the id that I was referring to that the javascript will “watch” for dynamic changes.

Finally, the last step in this process is looking at “autocomplete.php”. Here is the code:

<?php

// Datebase varibles
$db_host = "localhost";
$db_user = "username";
$db_pass = "password";

// Esstablish connect to MySQL database
$con = mysql_connect( $db_host, $db_user, $db_pass);
if(!$con)
	    die('Could not connect: ' . mysql_error() );

mysql_select_db("bucket_db", $con);

if($_GET["arg"] == 1)
    $query = "SELECT * FROM Persons WHERE FirstName LIKE "" . $_GET["q"] . "%" LIMIT 10;";
else
    $query = "SELECT * FROM Persons WHERE LastName LIKE "" . $_GET["q"] . "%" LIMIT 10;";

$result = mysql_query($query);

$output_items = array();

while($row = mysql_fetch_array($result))
{
    $output_items[] = $row["FirstName"];
} // while

print(implode("n", $output_items));

mysql_close($con);

?>

This is a simple connection to a MySQL database with the command “mysql_connect()“. Make sure to check if you have a good connection, that’s what the die line is for. I wrote my query in a variable then queried the database with “mysql_query()“. I then stored the results of the query in an array so I can easily print them out. I suppose you could skip that step and just go to printing them out. The key “trick” however is knowing where the string that was being typed in is located at. The auto-complete plugin store that string in a variable called q. To access that variable just use the call $_GET[“q”]. There is also a limit that is passed, called $_GET[“limit”]. I didn’t use it in this example, but you could if you’d like.

There are also many more options to customize the (“”).autocomplete() call. I haven’t played with any of them. But here’s a list of them here.

I hope this helps anyone.