Autocomplete with PHP, MySQL and Jquery UI

Autocomplete is very common practice, giving the user a list of matching results as they type can both save the users time and offer them an list of results.

Demo

Download source files

I've included a countries.sql file in the download to recreate the same database table used in my examples

This tutorial will demonstrate how to search a MySQL database for matching records as the user types.
For this example there will be two files, index.php and search.php
First index.php this file will house the form and jquery js and css files.

In the header of the file either download a theme from Jquery UI Theme Roller or use a CDN version such as

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

Next we'll need an input for the user to search, this would be wrapped inside a form tags as normal, in this case We're going to search for countries, the only requirement from the usual fields is either a class or id for jquery to use.

I've chosen a class called auto then this can be used again as needed.

<p><label>Country:</label><input type='text' name='country' value='' class='auto'></p>

Next setup the jQuery, in order to use jQuery and jQuery UI include the js files

<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script type="text/javascript" src="http://code.jquery.com/ui/1.10.1/jquery-ui.min.js"></script>

Then when jQuery has been loaded add a trigger to the class auto adding autocomplete() I've set two options which are the source for the data in this case search.php and minLengh which determines how many letters must be used before a match is returned.

<script type="text/javascript">
$(function() {
    
    //autocomplete
    $(".auto").autocomplete({
        source: "search.php",
        minLength: 1
    });                

});
</script>

That's all that is required for the index page next open search.php

First define your database connection information, change these to match your own.

define('DB_SERVER', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', 'root');
define('DB_NAME', 'demo');

If a search has been performed a GET request would have been made with the name term we only want to do a lookup if there has been a search, this is simple to do by doing an if statement.

if (isset($_GET['term'])){

Then create an empty array, this will be used when the page is returned an a json object.

$return_arr = array();

Next run a try statement and connect to the database, I'm using PDO this can be substituted for MySQLI for example.

try {
   $conn = new PDO("mysql:host=".DB_SERVER.";port=8889;dbname=".DB_NAME, DB_USER, DB_PASSWORD);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Next run a prepared statement and do a LIKE search against the search term, and populate the $return_arr array with results.

$stmt = $conn->prepare('SELECT country FROM countries WHERE country LIKE :term');
$stmt->execute(array('term' => '%'.$_GET['term'].'%'));
        
while($row = $stmt->fetch()) {
  $return_arr[] =  $row['country'];
}

Close the try show any errors if there was any display all results into a json object ready for jQuery to read.

} catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }


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

Putting it all together

index.php:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Demo</title>
  <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.1/themes/base/minified/jquery-ui.min.css" type="text/css" /> 
</head>
<body> 

    <form action='' method='post'>
        <p><label>Country:</label><input type='text' name='country' value='' class='auto'></p>
    </form>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script type="text/javascript" src="http://code.jquery.com/ui/1.10.1/jquery-ui.min.js"></script>    
<script type="text/javascript">
$(function() {
    
    //autocomplete
    $(".auto").autocomplete({
        source: "search.php",
        minLength: 1
    });                

});
</script>
</body>
</html>

Search.php

<?php
define('DB_SERVER', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', 'root');
define('DB_NAME', 'demo');


if (isset($_GET['term'])){
    $return_arr = array();

    try {
        $conn = new PDO("mysql:host=".DB_SERVER.";port=8889;dbname=".DB_NAME, DB_USER, DB_PASSWORD);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        $stmt = $conn->prepare('SELECT country FROM countries WHERE country LIKE :term');
        $stmt->execute(array('term' => '%'.$_GET['term'].'%'));
        
        while($row = $stmt->fetch()) {
            $return_arr[] =  $row['country'];
        }

    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }


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

?>

Not a lot to it, but its very useful. Any questions or concerns please comment below and I'll try my best to answer them.

Autocomplete with PHP, MySQL and Jquery UI

Table of Contents

Putting it all together