How To: jQuery Autocomplete
Basically people keep asking me how to write the query for the jQuery autocomplete plugin by bassistance.de. So I thought I should finally get round [...]
Basically people keep asking me how to write the query for the jQuery autocomplete plugin by bassistance.de. So I thought I should finally get round to showing how to do it.
It’s fairly simple. Once you have setup your autocomplete, which I’m not going to cover since it’s in the documentation at the link above, you just need to make your PHP function. Here is a basic one using mysqli:
$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "SELECT item FROM table WHERE column LIKE 'value%' ORDER BY item ASC";
$result = $mysqli->query($query, MYSQLI_STORE_RESULT);
while($row = $result->fetch_row())
echo $row['item']."\n";
That should echo out each item as needed by the autocomplete. There is another way to echo out your info, but it is depends entirely on how you are providing your information to the autocomplete. For a simple one it would be similar to this:
//Mysql connection & query as before except the query
//does not need the WHERE statement, just retrieve all the rows from the table
$o = 'var data = ['
while($row = $result->fetch_row())
{
$o .= "'{$row['item']}', ";
}
$o = substr($o, 0, -2);
$o .= "];";
header("content-type: text/javascript");
echo $o;
exit();
Now instead of querying that file via AJAX using the autocomplete, attach the file via a script tag to your page above the autocomplete & do this for the autocomplete:
$(function() {
$(".autosearch input#subcategory").autocomplete(data, {
width: 180,
selectFirst: false,
delay: 250
});
})
This will not only reduce strain on your MySQL server, but if your MySQL server sometimes slows down your autocomplete will become slow too. This prevents that as the information is available in the ‘fake’ .js file & is already ready and waiting. Simplez.
Full HTML
As requested by Tim, here is the full HTML for the page:
<!-- Doctype etc -->
<head>
<script type="text/javascript" src="/path/to/jquery.js"></script>
<script type="text/javascript" src="/path/to/autocomplete.js"></script>
<!-- If you are using the "fake" JS method you will need the next line too -->
<script type="text/javascript" src="/path/to/fake/js"></script>
<script type="text/javascript">
$(function() {
$("#autocomplete input#autotext").autocomplete(data, {
width: 180,
selectFirst: false,
delay: 250
});
});
</script>
</head>
<body>
<form id="autocomplete" name="autocomplete">
<input type="text" value="" name="autotext" id="autotext" />
<!-- Add any other fields you want such as a submit button -->
</form>
</body>
<!-- ending HTML stuff -->
I hope that this helps anyone looking to add an autocomplete to their site. I prefer the second method because of the speed it offers, but if your MySQL server can handle it or it has an extremely large amount of results you may want to go for the first method. 
Loading ...
Discussion: 76 Comments
It certainly is a strange one.
I wonder if I can drop in another jquery autocomplete js source file? Most seem to use the same options and database call.
This is so frastrating – I thought this one was in the bag! lol
Yeah, I’ve tried & tried to get the same thing to happen with the data you provided but it just isn’t happening…
I would imagine you should be able to try another jQuery autocomplete script. I can’t see there being much of a difference in the way they are used.
Hi Paul,
can I also use this one in my website? Thanks a lot.
How to show only first letter on available word? like a for ‘apple to apple’ not ‘big apple’. thx.
You might want to try out the new version of the autocomplete. It has now be integrated into the jQuery UI package and the version this tutorial was for has been deprecated. Here is a link to the migration guide for moving from this version to jQuery UI’s version.
thx Paul for your explanation. GBU.
What should I do if I want to save the ID of each item in the autocomplete and use that ID later?
For example, each displayed item in the autocomplete must have an ID that is stored in a hidden field (not displayed in the autocomplete). When the user select an item and click Submit button, the ID of the selected item would be used to retrieve other information from the database.
In ASP’s Ajax, this can be done easily by using an attribute. But in JQuery I haven’t found a way of doing it so far.
Can you help me? Thanks a lot.
I haven’t had much time to check out the new version of the autocomplete since it is now integrated with the jQuery UI pack.
There is however a great example of what I think you’re after here http://jquery.bassistance.de/autocomplete/demo/ the first box above hidden input. If you select the name of a bird then hit get value it gets an id. Not sure it’s exactly what you want but it’s close & I can’t modify it or anything at the minute since I’m usin my iPod and don’t have access to my computer at the minute.
Let ne know how near that gets and I’ll see if I can help you some more tomorrow when I’m back at my computer.
thanks for this consolidated list of all autocomplete codes. Quite useful for everyone.
thanks a lot, this helped!
No problem. Glad it was useful.
thanks for sharing.
nice tut