SUBSCRIBE TO RSS FEEDShow 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 are 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”:
<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.
$(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 the tutorial’s source code.
Great tutorial, forst one that really works for me! Now trying to add the option of sending the id from the database along with the ‘name’.
Yep. Nice tutorial. The more simple the better, if you don’t know as lot about the issue. Thx
[...] exemplo que eu me guiei para conseguir aprender a usar esta funcionalidade foi o do burnmind.com que também disponibiliza o fonte para download e um demo. Obs.: o demo deles está configurado [...]
Very helpful.
Yours is the only tutorial that has worked for me. After hours of searching. Thank you!
Nice work
How can I setup an scroll bar in the results?
Thank you all
@Mike: From what I can read from a quick search (sorry, no time to test it right now) you can use the following into its options:
overflow-y: scroll;
somehow this one worked for me after 1day of trying to figure it out
thank you, very simple, very good
Great one! Thanks for sharing, Stathis. It works for me!
Great, Its working!! Thanks alot
Hi!
firstly thanks for the great tutorial.I was wondering about is there any way search for non latin characters? for example korean characters?
I tried but it returns nothing…
@Fatih: I haven’t used it with Korean characters specifically, but the following works perfectly with Greek (UTF-8) characters:
Make sure you’re using the latest jQuery and UI versions, set the encoding of your database table to “utf8_general_ci”, and then add the following after the 1st line of the search.php file (right after you connect to your database):
$mysqli->query(“SET NAMES ‘utf8′”);
@gregor, @Sharon Loh & @karolinkaa: Thanks
is there any php script that optimize for large data set to serve autocomplete