how to feed jQuery UI’s Autocomplete with a database-generated dataset

Dirty Smile by Aukon on deviantART

Recently, I had to use jQuery UI’s Autocomplete widget. Its use is straightforward when you have a static datasource. When the datasource is larger though (from a few thousand to several million records) and you need to feed the Autocomplete widget with a remote dataset (provided in the example by a php script which queries a database), this is becoming a bit more complex because the documentation is a little vague on the matter and it lacks of a proper example.

Have a look at the demo page (opens in a new tab). You can also [download id=”8″ format=”1″].

We’re going to use a text field with the id “auto”:

$(document).ready(function()
{
    $('#auto').autocomplete(
    {
        source: "search.php",
        minLength: 3
    });
});

The php script is where all the magic happens. The most important piece of information is that the Autocomplete widget sends the content of the text field to the php script via the GET method in a variable called “term”.

$mysqli = new mysqli('localhost', 'yourUserName', 'yourPassWord', 'yourDatabase');
$text = $mysqli->real_escape_string($_GET['term']);

$query = "SELECT name FROM bands WHERE name LIKE '%$text%' ORDER BY name ASC";
$result = $mysqli->query($query);
$json = '[';
$first = true;
while($row = $result->fetch_assoc())
{
    if (!$first) { $json .=  ','; } else { $first = false; }
    $json .= '{"value":"'.$row['name'].'"}';
}
$json .= ']';
echo $json;

The above script will connect to the database, perform a query based on the term written by the user in the text field and will echo the results (to be used by the Autocomplete widget) in the following JSON format:

[{"value":"Nirvana"},{"value":"Pink Floyd"}]

If you haven’t done it already, have a look at the demo page (opens in a new tab). You can also [download id=”8″ format=”1″].

Photo by ~Aukon