how to feed jQuery UI’s Autocomplete with a database-generated dataset
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 the tutorial’s source code.
We’re going to use a text field with the id “auto”:
1 |
<input type="text" id="auto" /> |
The JavaScript code is quite straightforward. Inside the document.ready function, we bind the Autocomplete widget to the text field with the id “auto”. As a source for the Autocomplete widget we specify the php script “search.php”. We also set the minimum length of characters (3) that the Autocomplete widget needs before it activates itself.
1 2 3 4 5 6 7 8 |
$(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”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$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:
1 |
[{"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 the tutorial’s source code.
Photo by ~Aukon