Need some help?

I'm usually available for small jobs or problem solving with jQuery or css, at reasonable rates. Just get in touch.

Web Hosting

We recommend Clook for web hosting. UK based, great service and great value.

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:

85 responses to “jQuery UI Autocomplete with a remote database and PHP generating JSON data”

  1. Sig says:

    Thanks for clearing this up

  2. Vertongen says:

    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)

  3. Chima says:

    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…

  4. Katerina says:

    Thanks a lot! You saved my day :)

  5. Simon says:

    Interesting. Works for me using term but not if I replace with q.
    EDIT: See Matthew’s comment below for clarification.

  6. Thanks so much for sharing this… it was exactly what I was looking for, probably saved me hours of frustration!

  7. The old version of the plugin (pre jquery ui) used to use “q” instead of “term” as the query parameter. Hope this helps.

  8. norm says:

    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 array

    to return an array of just my search fields

  9. thank you for this!!!!!!!! i was getting frustrated, so lucky i stumbled on your blog site!! thanks! mate cheers!

  10. James says:

    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! :)

  11. Ben says:

    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.

  12. Steve says:

    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

  13. 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… :-)

  14. John Papa says:

    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”

  15. Márcio Simão says:

    Nice script! Better than de JQuery docs =)

    Simple, fast and efficient! Many thanks for share!

  16. Chris says:

    Thank you so much! Why is this not in examples anywhere else on the internet?!

  17. Thorsten says:

    Thanks !!!

    You covered exacly the part that I missed, too.
    Nice job.

  18. dex says:

    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

  19. Simon says:

    Can you send a link? Otherwise it’s just guessing….

  20. Thank you sooooooo much. I’ve been struggling with jQueryUI Autocomplete for hours. You saved my life. Thank you!

  21. Nisar says:

    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.

  22. Simon says:

    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.

  23. Jack says:

    Thank you very much Simon, you made me save a lot of time! ;)

  24. DarMar says:

    hi i cant get data from database to be UTF8 did same one did this and how?

  25. Simon says:

    You need to make sure your database is encoded as UTF-8 as well. Have a look at this page for some additional help.

  26. sami says:

    you saved my day! thans a lot

  27. Joern says:

    WOW. That was it! Exactly what I needed… Thanx a lot!

  28. Luis Franco says:

    Thank you very much, I was banging my head against the desk :-)

  29. Thank you kindly Simon.

  30. AliG says:

    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.

  31. Visithan says:

    Thanks

  32. Simon says:

    Nice, thanks a lot, that really helped. Too bad jquery lacks of documentation regarding search.php

  33. phatpeth says:

    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

  34. Simon says:

    Hi Phatpeth
    If your database is in in UTF-8, can you use utf8_encode() as suggested in the comment above?

  35. HoverFusion says:

    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

  36. Robin says:

    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. :)

  37. Jodi says:

    Thank you so much, I thought I was going crazy…

  38. marcos says:

    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']));

  39. BestBuddy says:

    Thanks……..

  40. Rodrigo says:

    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

  41. Simon says:

    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).

  42. Pivort Alexandre says:

    Very useful.
    Thanks a lot.

  43. Jason says:

    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!

  44. Simon says:

    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.

  45. Mike says:

    Many thanks for this.

  46. Nexxter says:

    THX

  47. pecific says:

    $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?

  48. pecific says:

    $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?

  49. Simon says:

    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, your search.php will be called like as search.php?term=ba.

    $_GET['term'] will retrieve ‘ba’ from this.

  50. Wojtek says:

    Thank you very much, i have already spent 2 days searching for right solution, i appriciate yor work!

  51. Barry says:

    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.

  52. Simon says:

    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.

  53. 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.

  54. rico says:

    thank you so much for this!

  55. Stefan says:

    Thank you! Great Explanation. With this one i got it :-)

  56. Victoria says:

    Thank you for this! Now I can finally get around to fixing search! :D

  57. Leonardo says:

    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.

  58. Simon says:

    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.

  59. osvaldo says:

    thanks for helping me, i was wondering where the error was….

  60. Nebojša says:

    Thank you for this, i’d never figured out alone. You are the world mega king

  61. Lucas says:

    Sweet jesus!!! Thank you man!!

  62. Miles says:

    Thanks so much for this, definitely what the ‘manual’ is missing!

    I was returning names from a database and formatted the PHP like so:

    $firstname = htmlentities(stripslashes($row['firstname']));
    			$lastname = htmlentities(stripslashes($row['lastname']));
    			$arr['value'] = "$firstname $lastname";
    			$row_set[] = $arr;

    And everything works :) Thanks again!

  63. Rik says:

    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.

  64. ndabe says:

    thanks so much for the explanation it really help me a lot

  65. M says:

    Thanks you are a Star

  66. val says:

    Thanks a lot man! You are my hero of the day!

  67. Alano says:

    Been looking for a week to solve this. Thank you.

  68. asdff says:

    looks simple and easy. I hope it works. How about giving custom CSS?

  69. Simon says:

    You can use whatever css you want, it doesn’t affect the jQuery.

  70. Priscilla says:

    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!!

  71. Walter says:

    Thanks, very helpful

  72. sangeet says:

    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

  73. Simon says:

    Yes, you could do this by writing a function for the source which only calls the database if there’s no local copy of the data. It’s described in the UI documentation here.

  74. Cyril says:

    sorry in english : do you know how i can add a parameter to the query ajax made by the autocomplete ? like an hidden field

  75. Simon says:

    You could do this by adding a get to the source option:

    ...source:'/myfile.php?myparam=something'....
  76. kenmasters says:

    I am really glad I found this article,, thanks simon,
    and how can i implement it to wordpress?

  77. Simon says:

    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.

  78. qwertynik says:

    Thanking for taking time and giving the explanation.

  79. Jan-Willem says:

    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.

  80. Simon says:

    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.

  81. Jan-Willem says:

    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?

  82. Simon says:

    How about hiding the submit button and only displaying it on the select event? You could hide it again on focus on the input field if necessary.

  83. Clint says:

    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 :)

  84. imam says:

    Thanks a lot, very useful

Useful? Interesting? Leave me a comment

I've yet to find a way of allowing code snippets to be pasted into Wordpress comments - so if you're trying to do this you'd be better off using the contact form.