jQuery autocomplete tutorial with PHP and MySQL
My goal was to make an autocomplete on a site that passed also another value as the selected to the new page.
I started with the documentation of jQuery but there was not much on the topic, so I started to Google to find out that it was hard to find the solution but a lot of people were looking for it.
Thanks to combining the blogs of Christos Pontikis and AF Design I came to my solution.
So, I wanted to create a autcomplete on customername and when a name is selected it should give the customercode to the next page and not the name.
I have a MySQL database with a table called Customer with several fields but the important ones are :
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstName` varchar(50) NOT NULL DEFAULT '',
`lastName` varchar(50) NOT NULL DEFAULT '',
`customerCode` varchar(6) NOT NULL DEFAULT ''
For the HTML I used a simple inputfield (which can be in a form) :
Using jQuery autocomplete one combines the autocomplete to the inputfield so at the page is a piece of Javascript.
For this I inserted the jQuery scripts that you can find here.
Time to add a small piece of Javascript to the page
$(document).ready(function($){
$('#customerAutocomplte').autocomplete({
source:'suggest_name.php',
minLength:2
});
});
What it does is adding the autocomplete functionality to the field customerAutocomplete. After typing at least 2 characters the script will send the entered value to the source, in this case suggest_name.php
For this jQuery uses automatically the variable term, leading to a url like suggest_name.php?term=AB
Knowing this I wrote the PHP page suggest_name.php
<?php
$mysqli = new MySQLi($server,$user,$password,$database);
/* Connect to database and set charset to UTF-8 */
if($mysqli->connect_error) {
echo 'Database connection failed...' . 'Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error;
exit;
} else {
$mysqli->set_charset('utf8');
}
/* retrieve the search term that autocomplete sends */
$term = trim(strip_tags($_GET['term']));
$a_json = array();
$a_json_row = array();
if ($data = $mysqli->query("SELECT * FROM Customer WHERE firstname LIKE '%$term%' OR lastname LIKE '%$term%' ORDER BY firstname , lastname")) {
while($row = mysqli_fetch_array($data)) {
$firstname = htmlentities(stripslashes($row['firstname ']));
$lastname = htmlentities(stripslashes($row['lastname']));
$code = htmlentities(stripslashes($row['customercode']));
$a_json_row["id"] = $code;
$a_json_row["value"] = $firstname.' '.$lastname;
$a_json_row["label"] = $firstname.' '.$lastname;
array_push($a_json, $a_json_row);
}
}
// jQuery wants JSON data
echo json_encode($a_json);
flush();
$mysqli->close();
?>
What it does is reading the table Customer at line 14 looking for the string term in the first- or lastname.
The results are added to a multidimensional array. When using a single array it would only return the value to have a autocomplete.
The label is now used to send for instance extra html like Chris did in his example. I just kept it equal.
The code is returned as well as you can see at line 19.
Now that I have the values I need, I need to expand my script a little.
I have to add the behaviour when one selects a name which is now done at line 5 and I redirect the user to the invoice page with the correct customercode.
$(document).ready(function($){
$('#zoekNaam').autocomplete({
source:'suggest_name.php',
minLength:2,
select: function(event,ui){
var code = ui.item.id;
if(code != '') {
location.href = '/invoice.php?customercode=' + code;
}
},
// optional
html: true,
// optional (if other layers overlap the autocomplete list)
open: function(event, ui) {
$(".ui-autocomplete").css("z-index", 1000);
}
});
});
This should do the trick. (and ofcourse you can send more parameters when needed)
I know the PHP is doing what it should but making it a little more safe is a wise thing to do.
Therefor my suggest_name.php looks like :
<?php
require 'conf.inc.php';
/* prevent direct access to this page */
$isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
if(!$isAjax) {
$user_error = 'Access denied - direct call is not allowed...';
trigger_error($user_error, E_USER_ERROR);
}
ini_set('display_errors',1);
/* if the 'term' variable is not sent with the request, exit */
if ( !isset($_REQUEST['term']) ) {
exit;
}
$mysqli = new MySQLi($server,$user,$password,$database);
/* Connect to database and set charset to UTF-8 */
if($mysqli->connect_error) {
echo 'Database connection failed...' . 'Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error;
exit;
} else {
$mysqli->set_charset('utf8');
}
/* retrieve the search term that autocomplete sends */
$term = trim(strip_tags($_GET['term']));
/* replace multiple spaces with one */
$term = preg_replace('/\s+/', ' ', $term);
$a_json = array();
$a_json_row = array();
$a_json_invalid = array(array("id" => "#", "value" => $term, "label" => "Only letters and digits are permitted..."));
$json_invalid = json_encode($a_json_invalid);
/* SECURITY HOLE *************************************************************** */
/* allow space, any unicode letter and digit, underscore and dash */
if(preg_match("/[^\040\pL\pN_-]/u", $term)) {
print $json_invalid;
exit;
}
/* ***************************************************************************** */
if ($data = $mysqli->query("SELECT * FROM Customer WHERE firstname LIKE '%$term%' OR lastname LIKE '%$term%' ORDER BY firstname , lastname")) {
while($row = mysqli_fetch_array($data)) {
$firstname = htmlentities(stripslashes($row['firstname']));
$lastname = htmlentities(stripslashes($row['lastname']));
$customercode= htmlentities(stripslashes($row['customercode']));
$a_json_row["id"] = $customercode;
$a_json_row["value"] = $firstname.' '.$lastname;
$a_json_row["label"] = $firstname.' '.$lastname;
array_push($a_json, $a_json_row);
}
}
/* jQuery wants JSON data */
echo json_encode($a_json);
flush();
$mysqli->close();