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.
Thanks for clearing this up
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)
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…
Thanks a lot! You saved my day
is it term or q?
http://docs.jquery.com/Plugins/Autocomplete/autocomplete#url_or_dataoptions
Interesting. Works for me using term but not if I replace with q.
EDIT: See Matthew’s comment below for clarification.
Thanks so much for sharing this… it was exactly what I was looking for, probably saved me hours of frustration!
The old version of the plugin (pre jquery ui) used to use “q” instead of “term” as the query parameter. Hope this helps.
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
thank you for this!!!!!!!! i was getting frustrated, so lucky i stumbled on your blog site!! thanks! mate cheers!
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!
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.
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 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… 
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”
Nice script! Better than de JQuery docs =)
Simple, fast and efficient! Many thanks for share!
Thank you so much! Why is this not in examples anywhere else on the internet?!
Thanks !!!
You covered exacly the part that I missed, too.
Nice job.
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
Can you send a link? Otherwise it’s just guessing….
Thank you sooooooo much. I’ve been struggling with jQueryUI Autocomplete for hours. You saved my life. Thank you!
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.
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.
Thank you very much Simon, you made me save a lot of time!
hi i cant get data from database to be UTF8 did same one did this and how?
You need to make sure your database is encoded as UTF-8 as well. Have a look at this page for some additional help.
you saved my day! thans a lot
WOW. That was it! Exactly what I needed… Thanx a lot!
Thank you very much, I was banging my head against the desk
Thank you kindly Simon.
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.
Thanks
Nice, thanks a lot, that really helped. Too bad jquery lacks of documentation regarding search.php
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
Hi Phatpeth
If your database is in in UTF-8, can you use
utf8_encode()as suggested in the comment above?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
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.
Thank you so much, I thought I was going crazy…