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 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:
|
1 2 3 4 5 6 |
$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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//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:
|
1 2 3 4 5 6 7 |
$(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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<!-- 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. 😉
78 Comments
Chris
Hi Paul,
I’m back again!
Hope you are well?
I’ve been using the plugin for some time now and have noticed a few issues with it.
In the database I’m querying I have (amongst many others) the following courses:
1) Yoga for All – Hatha Raja Introduction
2) Yoga – Iyengar Style. Beginners & Intermediate
3) Yoga – Iyengar. Improvers
4) Yoga – Iyengar. Beginners
5) Yoga for All – Hatha Introduction
6) Yoga for All – Hatha Advanced
7) Yoga for All – Hatha Raja Intermediate
If I search for “yoga” I only get course 5, 6 and 7. Should return all 7.
If I put a space at the beginning, I get all 7 courses, but no highlighting
If I search for “yoga all” I get no results. Should return 4.
The plugin gets in the results from a php page. I’ve tried placing the search terms directly into the query string on the php file and it brings back the correct courses.
So the problem is definately at the plugin side. I’ve tried experimenting with the various options:
http://docs.jquery.com/Plugins/Autocomplete/autocomplete#url_or_dataoptions
but nothing has fixed the problem.
Any idea how to solve this?
Chris
After some additional experimentation: If I type “yoga” (3 results) then add a space at either end ” yoga ” (7 results) then delete the spaces again, I get 7 results. Very odd!
Paul Robinson
Very strange. I haven’t noticed a problem like that before.
As far as I’m aware the plugin doesn’t do anything with the results, it just shows them after getting them via AJAX from your PHP script. So for the results from the plugin to be different from the PHP script is extremely strange.
I honestly have no idea what is going on, I’ll try some testing on my localhost though. As for “yoga all” that won’t return any results (if called on a MySQL database) unless you replace the space with a wildcard since your string is “yoga for all”. Your query would have to be “yoga%all”.
I’ll let you know if I find anything. Oh and I’m doing fine thanks. 😉
Paul Robinson
I can’t seem to get the problem you mentioned to occur on my localhost whether using data in a JS object or via a PHP file using a MySQL database.
My best guess would be either some strange problem with your PHP file (probably not since querying directly works), or a problem with the setting up of the autocomplete. If it’s neither of those I’m afraid you have me stuck. 😆
Chris
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
Paul Robinson
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.
Chicago Limousine Service
Hi Paul,
can I also use this one in my website? Thanks a lot.
ricky
How to show only first letter on available word? like a for ‘apple to apple’ not ‘big apple’. thx.
Paul Robinson
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.
ricky
thx Paul for your explanation. GBU.
SCL
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.
Paul Robinson
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.
Pradeep
thanks for this consolidated list of all autocomplete codes. Quite useful for everyone.
Pushpinder Baggap
thanks a lot, this helped!
Paul Robinson
No problem. Glad it was useful. 🙂
web designing ludhiana
thanks for sharing.
nice tut
Sudhanshu
How do I connect this autocomplete to the data source? By data source I mean the list of items which the autocomplete would search from. There is no url of the source given in the javascript given above.