jQuery UI Autocomplete with a remote database and PHP generating JSON data
I spent some time last night struggling with jQuery UI Autocomplete. I was trying to use a remote data source, and was calling a php script to interrogate the database, no ajax. During the course of getting it to work properly, I discovered a couple of things that aren’t very clear in the documentation.
The basic javascript, lifted straight from the jQuery UI demo is:
$("#autocomplete").autocomplete({
source: "search.php",
minLength: 2,//search after two characters
select: function(event,ui){
//do something
}
});
Fine so far. There was one major thing that fooled me. If, instead of using a php script, you use a local source, something like:
source: [{"value":"Some Name","id":1},{"value":"Some Othername","id":2}]
then this local data is queried each time a character is entered in the required field. I assumed, therefore, that all I had to replicate in my search.php script, was a query to return all the values from my database. Wrong! I need to pass a search term to my php script in order to return only the correct subset of data. I further discovered that jQuery UI Autocomplete passes this data as a GET entitled ‘term’ (didn’t see this anywhere in the examples). So, armed with this knowledge, my php script looks like this:
//connect to your database $term = trim(strip_tags($_GET['term']));//retrieve the search term that autocomplete sends $qstring = "SELECT description as value,id FROM test WHERE description LIKE '%".$term."%'"; $result = mysql_query($qstring);//query the database for entries containing the term while ($row = mysql_fetch_array($result,MYSQL_ASSOC))//loop through the retrieved values { $row['value']=htmlentities(stripslashes($row['value'])); $row['id']=(int)$row['id']; $row_set[] = $row;//build an array } echo json_encode($row_set);//format the array into json data
Hope this explanation is useful. No demo this time, because it doesn’t seem very interesting. You can have a look at an integration of jQuery UI Autocomplete with a vertical slider here and see a demo here.
Tags: jQuery
Superb coding
I just wanted to point out that ‘q’ nor ‘term’ worked for me. I’m using a jquery bootstrap implementation, and after some debugging, determined that my distribution (pulled off github) uses ‘query’. I also didn’t see this documented in any of the examples. Hope this helps someone.
Depending on your form, something like:
I wasted so much time on this. Your demo worked perfectly. I can’t thank you enough.
Like “Rik”, now i need a way to store that ID in a hidden tag too.
You can view the javascript on this example.
can you please specify the js files required. ?
thanks in advance..
Thanks a lot, very useful
Thank you for this, you have been a big help. I really hate how jquery team didn’t give more details regarding this. You’re a hero ๐
How about hiding the submit button and only displaying it on the
selectevent? You could hide it again onfocuson the input field if necessary.It’s not exactly what I am looking for. You can still enter something random which won’t give a hit in the remote datasource, but still submit the form and post the entry even though it wasn’t selected from a list.
Is there a possibility, or is the only way to check on post if there’s a result for the entered input?
I’m not sure I quite understand. If you set things up like this example then the user can only click on option from the list.
I was wondering if it’s possible to check the data the user input against the remote source on the close event, to force the users to chose an option from the list in stead of typing something of their own and ignoring the list.
Thanking for taking time and giving the explanation.
Nothing extra special is needed to do this in WordPress. You can use wp_enqueue_script to add your javascript and wp_ajax to create your lookup function.
I am really glad I found this article,, thanks simon,
and how can i implement it to wordpress?
You could do this by adding a get to the
sourceoption:sorry in english : do you know how i can add a parameter to the query ajax made by the autocomplete ? like an hidden field
Yes, you could do this by writing a function for the
sourcewhich only calls the database if there’s no local copy of the data. It’s described in the UI documentation here.nice tutorial..
But i want something different..This piece of script is querying database everytime key is pressed which will add load to the server. I can add delay but that is not worth for me as well. can we do something like querying the database once and store json on client browser and then in every key press it searches the record matching from that json?
thanks in advance
Thanks, very helpful
Oh, I could give you a big hug right now!! I spent whole night yesterday trying to figure out why my script doesn’t work. thank you!!
You can use whatever css you want, it doesn’t affect the jQuery.
looks simple and easy. I hope it works. How about giving custom CSS?
Been looking for a week to solve this. Thank you.
Thanks a lot man! You are my hero of the day!
Thanks you are a Star
thanks so much for the explanation it really help me a lot
Thank you. I’ve wasted more than 4 hours on this.. Got it finally working! Thanks again!
Now i need a way to store that ID in a hidden form.
Thanks so much for this, definitely what the ‘manual’ is missing!
I was returning names from a database and formatted the PHP like so:
And everything works ๐ Thanks again!
Sweet jesus!!! Thank you man!!
Thank you for this, i’d never figured out alone. You are the world mega king
thanks for helping me, i was wondering where the error was….
That sounds like just an issue with the css affecting the displayed results. If you view the page with Firebug, you should be able to see if the results are returned from the database ajax call (using the Firebug console) and also the generated html produced as a result.
Hi, I appreciate your work here, it’s really useful and helpful. Let me tell you that in my case it’s almost working but instead of showing the results under the text box it shows a thin line whenever the string matches and if it does not match it shows nothing. Any thoughts? Thanks in advance.
Thank you for this! Now I can finally get around to fixing search! ๐
Thank you! Great Explanation. With this one i got it ๐
thank you so much for this!
Consider adding “SELECT TOP 10 description ….” to avoid the list beeing to long.
Also to speed up the query devide the data into multiple tables.
eg. create the alphabet in tables, so querys starting with “a” selects from table “a” and so querys starting with “b” selects from table “b” so on.
Barry
Can’t see anything obviously wrong. The first thing to do would be to check the response from your PHP code using, for example, the Firebug console. This may give an indication of the problem.
I am still having trouble with this. I basically used your code above, and it is still not working for me. For instance, I am making a country dropdown that pulls countries from the database. When I change the “source:” to [“Algeria”,”Australia”,”Belize”], etc., etc., it works fine. When I change the “source:” to “/path/to/php.php”, it stops working. So it has to be something with my PHP script.
Here is my php code: http://pastebin.com/JDhZLUd7. Any help would be appreciated.
Thank you very much, i have already spent 2 days searching for right solution, i appriciate yor work!
As explained on the page, UI Autocomplete passes the seacrh term as a GET entitled
term. Effectively, if a search term of ‘ba’ is entered in the autocomplete field, yoursearch.phpwill be called like assearch.php?term=ba.$_GET['term']will retrieve ‘ba’ from this.$term = trim(strip_tags($_GET[‘term’]));//retrieve the search term that autocomplete sends
plz,can anybody tell me what is $_get[‘term’]?Is it the name og input field?
$term = trim(strip_tags($_GET[‘term’]));//retrieve the search term that autocomplete sends
plz,can anybody tell me what is $_get[‘term’].Is it the name og input field?
THX
Many thanks for this.
Hi Jason
This should work fine whatever the name of the php file called – not sure why you are experiencing problems with this.
If you view the console in Firebug with Firefox (or developer tools in Chrome or Opera) that should tell you whether you are cnnecting correctly to the php file.
This works perfectly, unless I change the name “search.php” to anything else. If I change the search.php file to another name and update the source: “.php” line to match, it doesn’t seem to find any results. When I change them both back to “search.php” it works. Is this a limitation or bug or something I’m missing?
Thanks!
Very useful.
Thanks a lot.
Hi Rodrigo
Easiest to debug by viewing the ajax response from your php file via the console window in Firebug, Chrome(Network tab) or Opera Dragonfly (Network tab).
THAKS A LOT!!!! but have a question: I want to debug the array into source.php, I just want to simple print_r an array . How can I archive this. thanx again
Thanks……..
my autocomplete now is working!!
If the server that you are using have a php < 5 the json_encode doesn't work. you can use another function that makes the same work of the "json_encode", like this http://forum.imasters.com.br/topic/162967-json-php/
My case (Brazil) we have special characters. I solved this using $term = trim(utf8_decode($_GET['term']));
Thank you so much, I thought I was going crazy…
Nice tutorial. Perhaps someone needs it, you can access the request-data throug callback while selection:
select: function (event, ui) {
console.log(ui.item.id);
}
ui.item gots the needed data. ๐
For people having trouble with characters utf8 encoding, try setting before db query:
mysql_query(“SET CHARACTER SET utf8”);
mysql_query(“SET NAMES ‘utf8′”);
that solved the problem for me
Hi Phatpeth
If your database is in in UTF-8, can you use
utf8_encode()as suggested in the comment above?your article really help a lot
but I got a problem because my database is not in English, in UTF8 can return a result correctly but if I use Json, it always return something weird. I try to do everything but useless
Could you give me some advise? please
Nice, thanks a lot, that really helped. Too bad jquery lacks of documentation regarding search.php
Thanks
To ensure the ‘value’ appears in UTF-8 in the autocomplete widget, you need to use utf8_encode
Change this line:
$row[‘value’]=htmlentities(stripslashes($row[‘value’]));
to:
$row[‘value’]=utf8_encode(stripslashes($row[‘value’]));
Your database does need to be UTF-8 in the first place of course. Also, consider using the autocomplete ‘accent folding’ example in conjunction.
Thank you kindly Simon.
Thank you very much, I was banging my head against the desk ๐
WOW. That was it! Exactly what I needed… Thanx a lot!
you saved my day! thans a lot
You need to make sure your database is encoded as UTF-8 as well. Have a look at this page for some additional help.
hi i cant get data from database to be UTF8 did same one did this and how?
Thank you very much Simon, you made me save a lot of time! ๐
Thanks Nisar. The data returned with ajax is displayed in a ul element with class ‘ui-autocomplete’ – so you should be able to use this to style your results as required. I’ve done this on the example on my page to display the results with a grey background. jQuery UI also uses a class of ‘ui-state-hover’ to a list element within the ul which is hovered or in focus – so this can be used to change the styling of list elements.
Thanks a million for the explanation. Like you, I was trying to fetch data from database and failed miserably. Having no knowledge about JSON made matters worse.
However, I do have one more doubt. It’s regarding the css of the returned data. From where is it taking the colors and font size on hover? There is ‘jquery-ui-1.8.17.custom.css’ but even on emptying the whole file, the css is being applied to the returned data. I removed the reference of that css file and there was no longer any css being applied. This makes me wonder from where is it taking the css rules to be applied to the returned. I want to change the hover color and reduce the font size and padding a bit.
Thank you sooooooo much. I’ve been struggling with jQueryUI Autocomplete for hours. You saved my life. Thank you!
Can you send a link? Otherwise it’s just guessing….
hi there i made this work on local setting but when I tried using using this on wordpress i cant figure itout can anyone help me? thanks
Thanks !!!
You covered exacly the part that I missed, too.
Nice job.
Thank you so much! Why is this not in examples anywhere else on the internet?!
Nice script! Better than de JQuery docs =)
Simple, fast and efficient! Many thanks for share!
I echo the thanks and appreciation expressed in other comments. UI is often derelict in not giving the complete specs. “Left as an exercise for the reader”
I can’t say how thankful I am to you. The “documentation” does not touch on this subject, or if it touches I didn’t see it now at 2:48 AM here in Rio de Janeiro ๐ Since I’m using .NET it was a simple matter of writing the pertinent LINQ on my server side array to return the filtered data… Thanks a million! I really thought it was broken since the filtering was not working at all and the View Source on the jQuery UI website does not show the “remote” script source… ๐
Wicked stuff. Thanks for posting this. Seems i had exactly same problem and this has cleared things up nicely. My autocompelte is now working a treat
Cheers
I agree, the Jquery site lacked sufficient documentation in regards to search.php. Big up for sharing your find, it’s certainly a time-saver.
Can’t thank you enough for this missing piece of the puzzle. You’ve helped wrap up what has been a frustrating 2 days trying to figure out why the PHP script works independently but NOT when combined with the jQuery UI autocomplete functionality.
A big, big thank you for this! ๐
thank you for this!!!!!!!! i was getting frustrated, so lucky i stumbled on your blog site!! thanks! mate cheers!
Thank you so much for thisโฆ it was exactly what I was looking for, after lots of hours of frustration. In my case I just had to modify the array I was returning JSON:
$row['city']=htmlentities(stripslashes($row['city'])); $row_set[] = $row['city'];//build an arrayto return an array of just my search fields
The old version of the plugin (pre jquery ui) used to use “q” instead of “term” as the query parameter. Hope this helps.
Thanks so much for sharing this… it was exactly what I was looking for, probably saved me hours of frustration!
Interesting. Works for me using term but not if I replace with q.
EDIT: See Matthew’s comment below for clarification.
is it term or q?
http://docs.jquery.com/Plugins/Autocomplete/autocomplete#url_or_dataoptions
Thanks a lot! You saved my day ๐
This DEFINITELY gets a thumbs up from me too – many thanks for taking the time to write this up. Big gap in the Jquery “how” to section for this one if you ask me…
Thank you for pointing this out! JQuery UI should consider giving more info / an example on the soo called ‘datasource’… I hope they update that some day.
About the GET: you could use $_REQUEST[‘term’] just to make sure you will always be able to catch the param. (http://php.net/manual/en/reserved.variables.request.php)
Thanks for clearing this up