How to Create a jquery-ui Autocomplete Step by Step

Autocomplete is one of the most important parts of modern rich web interface. Actually, you can create an autocomplete, using an AJAX call to build a list (server side) and afterwards to display the list using javascript (usually below autocomplete input). However, there are many tools to create it the easy way.

The most robust and efficient tool to create an autocomplete is jquery and jquery-ui. Autocomplete widget of jquery-ui is available here.

Of course, there are many other open source or commercial tools available. Twitter typeahead is probably the most important open source alternative.

In this tutorial I will create an autocomplete from the post titles of this blog. When user types some characters in the relevant input, a list with titles matching given term automatically appears. This autocomplete widget has the following properties:

  • full working demo is available (see after next paragraph)
  • php and mysqli are used as server side technologies
  • ability to search for more than one terms separated by space (so if you give the term “php mysql” it will return the post titles containing both “php” and “mysql”)
  • highlight results (given term is highlighted inside blog post title)
  • scroll results
  • display an animated image as ajax indicator (just while server side database search occurs, afterwards it will disappear)
  • it accepts unicode letter and digit, underscore and dash. Other characters are excluded from user input to avoid possible SQL injection or XSS attacks
  • prepared statements will be used to increase security (a version withour prepared statements is also provided)

How it works

term in query string

When user types a string in autocomplete input, an AJAX call is created to source property (in our example autocomplete.php) with a parameter named “term” in query string. For example if you type “mysql” the following AJAX call is fired:

So, autocomplete.php will create and return as JSON the data required for the autocomplete list.

autocomplete list attributes

Displayed autocomplete list supports various attributes, the most important of them are

  • id is the id of the li element
  • value is the value of the autocomplete input (moving from each li items to another using mouse or keyboard arrows)
  • label is what li item displays (html is not supported by default. You need an extension for this)

JSON data

So, when user types “mysql” in autocomplete input, the AJAX call autocomplete.php?term=mysql creates and returns the following JSON data

[
    {
        "id": "mysql-in-a-nutshell",
        "value": "MySQL in a Nutshell",
        "label": "<span class=\"hl_results\">MySQL</span> in a Nutshell"
    },
    {
        "id": "how-to-use-php-improved-mysqli-extension-and-why-you-should",
        "value": "How to Use PHP Improved MySQLi extension (and Why You Should)",
        "label": "How to Use PHP Improved <span class=\"hl_results\">MySQL</span>i extension (and Why You Should)"
    },
    {
        "id": "is-it-time-to-remove-mysql-in-favor-of-mariadb-in-production-servers",
        "value": "Is it time to Remove MySQL in favor of MariaDB in Production Servers?",
        "label": "Is it time to Remove <span class=\"hl_results\">MySQL</span> in favor of MariaDB in Production Servers?"
    }
]

In this example I am using the post URL as “id”, so after selection (select method) a redirect to selected URL occurs. Of course, you may use “id” (or other attributes) in any way, according to your needs. A common practice is to store the id value to a hidden input (for later use) when user selects a list item from autocomplete.

jquery-ui autocomplete API is documented here.

The HTML part

page HEAD

Of course, you will need jquery and jquery-ui. jquery-ui autocomplete does not support html by default. If you want to use html option to highlight results, get jquery.ui.autocomplete.html.js from here.

autocomplete.css is a local css file to customize display.

autocomplete.js is the javascript file which contains the javascript code to turn an input field to autocomplete widget.

<link rel="stylesheet" type="text/css" href="/path/to/jquery-ui.css">

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

<script type="text/javascript" src="/path/to/jquery.js"></script>

<script type="text/javascript" src="/path/to/jquery-ui.min.js" type="text/javascript"></script>

<script type="text/javascript" src="/path/to/jquery.ui.autocomplete.html.js"></script>

<script type="text/javascript" src="/path/to/local/autocomplete.js"></script>

page BODY

Simply add an input field (and give it an ID value) as follows:

<input type="text" id="topic_title">

The CSS part

Create a local “autocomplete.css” file (or use any other name you prefer) with the following contents:

/* highlight results */
.ui-autocomplete span.hl_results {
    background-color: #ffff66;
}

/* loading - the AJAX indicator */
.ui-autocomplete-loading {
    background: white url('../img/ui-anim_basic_16x16.gif') right center no-repeat;
}

/* scroll results */
.ui-autocomplete {
    max-height: 250px;
    overflow-y: auto;
    /* prevent horizontal scrollbar */
    overflow-x: hidden;
    /* add padding for vertical scrollbar */
    padding-right: 5px;
}

.ui-autocomplete li {
    font-size: 16px;
}

/* IE 6 doesn't support max-height
* we use height instead, but this forces the menu to always be this tall
*/
* html .ui-autocomplete {
    height: 250px;
}

The JAVASCRIPT part

Create a local “autocomplete.js” file (or use any other name you prefer) with the following contents:

$(function() {

    $("#topic_title").autocomplete({
        source: "/path/to/ajax_autocomplete.php",
        minLength: 2,
        select: function(event, ui) {
            var url = ui.item.id;
            if(url != '#') {
                location.href = '/blog/' + url;
            }
        },

        html: true, // optional (jquery.ui.autocomplete.html.js required)

            // optional (if other layers overlap autocomplete list)
        open: function(event, ui) {
            $(".ui-autocomplete").css("z-index", 1000);
        }
    });

});

The PHP part

Create a local “autocomplete.php” file (or use any other name you prefer) with the following contents:

<?php
// contains utility functions mb_stripos_all() and apply_highlight()
require_once 'local_utils.php';

// prevent direct access
$isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
if(!$isAjax) {
        $user_error = 'Access denied - not an AJAX request...';
        trigger_error($user_error, E_USER_ERROR);
}

// get what user typed in autocomplete input
$term = trim($_GET['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);

// replace multiple spaces with one
$term = preg_replace('/\s+/', ' ', $term);

// SECURITY HOLE ***************************************************************
// allow space, any unicode letter and digit, underscore and dash
if(preg_match("/[^\040\pL\pN_-]/u", $term)) {
        print $json_invalid;
        exit;
}
// *****************************************************************************

// database connection
$conn = new mysqli("db_server", "db_user", "db_passwd", "db_name");

if($conn->connect_error) {
        echo 'Database connection failed...' . 'Error: ' . $conn->connect_errno . ' ' . $conn->connect_error;
        exit;
} else {
        $conn->set_charset('utf8');
}

$parts = explode(' ', $term);
$p = count($parts);

/**
 * Create SQL
 */
$sql = 'SELECT url, post_title FROM posts WHERE date_published is not null ';
for($i = 0; $i < $p; $i++) {
        $sql .= ' AND post_title LIKE ' . "'%" . $conn->real_escape_string($parts[$i]) . "%'";
}

$rs = $conn->query($sql);
if($rs === false) {
        $user_error = 'Wrong SQL: ' . $sql . 'Error: ' . $conn->errno . ' ' . $conn->error;
        trigger_error($user_error, E_USER_ERROR);
}

while($row = $rs->fetch_assoc()) {
        $a_json_row["id"] = $row['url'];
        $a_json_row["value"] = $row['post_title'];
        $a_json_row["label"] = $row['post_title'];
        array_push($a_json, $a_json_row);
}

// highlight search results
$a_json = apply_highlight($a_json, $parts);

$json = json_encode($a_json);
print $json;
?>

The PHP part (using Prepared Statements) – (recommended)

Simply replace lines 45 – 64 of the above php script with the following code:

/**
 * $stmt->bind_param('s', $param); does not accept params array
 * and if call_user_func_array will be used, array params need to passed by reference
 */
$a_part_to_search = array();
$a_parts = array();

for($i = 0; $i < $p; $i++) {
        $part_type .= 's';
}
$a_parts[] = & $part_type;

foreach($parts as $part) {
        array_push($a_part_to_search, '%' . $part . '%');
}
for($i = 0; $i < $p; $i++) {
        $a_parts[] = & $a_part_to_search[$i];
}

$sql = 'SELECT url, post_title FROM posts WHERE date_published is not null ';
for($i = 0; $i < $p; $i++) {
        $sql .= ' AND post_title LIKE ?';
}

/* Prepare statement */
$stmt = $conn->prepare($sql);
if($stmt === false) {
        $user_error = 'Wrong SQL: ' . $sql . '' . 'Error: ' . $conn->errno . ' ' . $conn->error;
        trigger_error($user_error, E_USER_ERROR);
}

/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */
//$stmt->bind_param('s', $param); does not accept params array
call_user_func_array(array($stmt, 'bind_param'), $a_parts);

/* Execute statement */
$stmt->execute();

$stmt->bind_result($url, $post_title);

while($stmt->fetch()) {
        $a_json_row["id"] = $url;
        $a_json_row["value"] = $post_title;
        $a_json_row["label"] = $post_title;
        array_push($a_json, $a_json_row);
}

PHP functions to highlight results (optional)

If you want to highlight results, create (and include) a local “local_utils.php” file (or use any other name you prefer) with the following contents. It contains two functions:

  • mb_stripos_all() it is variation of mb_stripos() which finds all occurrences of a needle in a haystack (not only the first one)
  • apply_highlight() it is a custom function which wraps any string to be highlighted
    with <span class="hl_results"> and </span>
/**
 * mb_stripos all occurences
 * based on http://www.php.net/manual/en/function.strpos.php#87061
 *
 * Find all occurrences of a needle in a haystack
 *
 * @param string $haystack
 * @param string $needle
 * @return array or false
 */
function mb_stripos_all($haystack, $needle) {

        $s = 0;
        $i = 0;

        while(is_integer($i)) {

                $i = mb_stripos($haystack, $needle, $s);

                if(is_integer($i)) {
                        $aStrPos[] = $i;
                        $s = $i + mb_strlen($needle);
                }
        }

        if(isset($aStrPos)) {
                return $aStrPos;
        } else {
                return false;
        }
}

/**
 * Apply highlight to row label
 *
 * @param string $a_json json data
 * @param array $parts strings to search
 * @return array
 */
function apply_highlight($a_json, $parts) {

        $p = count($parts);
        $rows = count($a_json);

        for($row = 0; $row < $rows; $row++) {

                $label = $a_json[$row]["label"];
                $a_label_match = array();

                for($i = 0; $i < $p; $i++) {

                        $part_len = mb_strlen($parts[$i]);
                        $a_match_start = mb_stripos_all($label, $parts[$i]);

                        foreach($a_match_start as $part_pos) {

                                $overlap = false;
                                foreach($a_label_match as $pos => $len) {
                                        if($part_pos - $pos >= 0 && $part_pos - $pos < $len) {
                                                $overlap = true;
                                                break;
                                        }
                                }
                                if(!$overlap) {
                                        $a_label_match[$part_pos] = $part_len;
                                }

                        }

                }

                if(count($a_label_match) > 0) {
                        ksort($a_label_match);

                        $label_highlight = '';
                        $start = 0;
                        $label_len = mb_strlen($label);

                        foreach($a_label_match as $pos => $len) {
                                if($pos - $start > 0) {
                                        $no_highlight = mb_substr($label, $start, $pos - $start);
                                        $label_highlight .= $no_highlight;
                                }
                                $highlight = '' . mb_substr($label, $pos, $len) . '';
                                $label_highlight .= $highlight;
                                $start = $pos + $len;
                        }

                        if($label_len - $start > 0) {
                                $no_highlight = mb_substr($label, $start);
                                $label_highlight .= $no_highlight;
                        }

                        $a_json[$row]["label"] = $label_highlight;
                }

        }

        return $a_json;

}