how to feed jQuery UI’s Autocomplete with a database-generated dataset

October 13th, 2010
tutorials
62

Dirty Smile by Aukon on deviantART

Recently, I had to use jQuery UI’s Autocomplete widget. Its use is straightforward when you have a static datasource. When the datasource is larger though (from a few thousand to several million records) and you need to feed the Autocomplete widget with a remote dataset (provided in the example by a php script which queries a database), this is becoming a bit more complex because the documentation is a little vague on the matter and it lacks of a proper example.

Have a look at the demo page (opens in a new tab). You can also download the tutorial’s source code.

We’re going to use a text field with the id “auto”:

1	<input type="text" id="auto" />

The JavaScript code is quite straightforward. Inside the document.ready function, we bind the Autocomplete widget to the text field with the id “auto”. As a source for the Autocomplete widget we specify the php script “search.php”. We also set the minimum length of characters (3) that the Autocomplete widget needs before it activates itself.

$(document).ready(function()

{

$('#auto').autocomplete(

{

source: "search.php",

minLength: 3

});

The php script is where all the magic happens. The most important piece of information is that the Autocomplete widget sends the content of the text field to the php script via the GET method in a variable called “term”.

$mysqli = new mysqli('localhost', 'yourUserName', 'yourPassWord', 'yourDatabase');

$text = $mysqli->real_escape_string($_GET['term']);

$query = "SELECT name FROM bands WHERE name LIKE '%$text%' ORDER BY name ASC";

$result = $mysqli->query($query);

$json = '[';

$first = true;

while($row = $result->fetch_assoc())

{

if (!$first) { $json .= ','; } else { $first = false; }

$json .= '{"value":"'.$row['name'].'"}';

}

$json .= ']';

echo $json;

The above script will connect to the database, perform a query based on the term written by the user in the text field and will echo the results (to be used by the Autocomplete widget) in the following JSON format:

1	[{"value":"Nirvana"},{"value":"Pink Floyd"}]

If you haven’t done it already, have a look at the demo page (opens in a new tab). You can also download the tutorial’s source code.

Photo by ~Aukon

Ton

Great tutorial, forst one that really works for me! Now trying to add the option of sending the id from the database along with the ‘name’.
http://os-cms.net dennis

Yep. Nice tutorial. The more simple the better, if you don’t know as lot about the issue. Thx :)
Jon L

Very helpful.
Rick

Yours is the only tutorial that has worked for me. After hours of searching. Thank you!
Mike

Nice work

How can I setup an scroll bar in the results?
Stathis

Thank you all :)

@Mike: From what I can read from a quick search (sorry, no time to test it right now) you can use the following into its options:

overflow-y: scroll;
gregor

somehow this one worked for me after 1day of trying to figure it out
thank you, very simple, very good
http://lohsharon.wordpress.com Sharon Loh

Great one! Thanks for sharing, Stathis. It works for me! :D
karolinkaa

Great, Its working!! Thanks alot
Fatih

Hi!
firstly thanks for the great tutorial.I was wondering about is there any way search for non latin characters? for example korean characters?
I tried but it returns nothing…
Stathis

@Fatih: I haven’t used it with Korean characters specifically, but the following works perfectly with Greek (UTF-8) characters:

Make sure you’re using the latest jQuery and UI versions, set the encoding of your database table to “utf8_general_ci”, and then add the following after the 1st line of the search.php file (right after you connect to your database):

$mysqli->query(“SET NAMES ‘utf8′”);

@gregor, @Sharon Loh & @karolinkaa: Thanks :)
- Marina
  
  I tried to do this to display special characters such as “ãáô” but didn’t worked. I checked my DB and it is already configured as utf8_general_ci.
  
  Is there anything I can do to solve this?
  Thank you very much for the great tutorial!
  - Stathis
    
    If you use the following right after the connection to the db, it should fix your problem.
    
    $mysqli->query("SET NAMES 'utf8'");
http://jobberies.com/ Muhaimin

is there any php script that optimize for large data set to serve autocomplete
Stathis

@Muhaimin: What do you mean? Explain in more detail please.
vicky

thanx
Great Work
Clarice

Fantastic read! I’ve added your rss feeds to my Google reader.
- Stathis
  
  Thanks :)
Umair

Thanks for this gr8 tutorial
- Stathis
  
  Thanks :)
kengky

i’ve got an error message
Fatal error: Call to a member function fetch_assoc() on a non-object in C:\xampp\htdocs\autocomplete\search.php on line 9
- Stathis
  
  I’m assuming you’re referring to the source code available for download. Have you used the .sql file and changed the following with your credentials?
  
  $mysqli = new mysqli('localhost', 'yourUserName', 'yourPassWord', 'yourDatabase');
kengky

sorry thats my fault i resolved it already
i type a wrong column name in the query
thank you very much for your reply
Michael

How do I have to change this code to get it running for an Oracle database?
- Stathis
  
  Yes, the code in search.php is written for MySQL. You’ll have to make a connection to the Oracle db, to use the appropriate SQL query (it’s been several years since I used an Oracle db so I don’t remember if it needs any changes), and finally, encode the outcome as JSON.
  - Michael
    
    After a few hours I figured it out. My example is a little bit different (because it uses first- & lastname), but maybe it is helpful for someone else – I was looking for a while on the internet and did not find an example for Oracle.
    
    getMessage());
    }
    
    // get term string
    $text = “%”.$_GET[‘term’].”%”;
    
    // query DB
    $stmt = $conn->query(“SELECT * FROM YOUR_TABLE WHERE lastname LIKE ‘$text'”);
    
    // get results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $row_array[‘id’] = $row[‘ID’];
    $row_array[‘value’] = $row[‘LASTNAME’];
    $row_array[‘desc’] = $row[‘FIRSTNAME’];
    array_push($return_arr,$row_array);
    }
    echo json_encode($return_arr);
    ?>
  - Michael
    
    After a few hours I figured it out. My example is a little bit different (because it uses first- & lastname), but maybe it is helpful for someone else – I was looking for a while on the internet and did not find an example for Oracle.
    
    Put this all within php tags:
    
    // declare some variables
    $return_arr = array();
    $tns = ”
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your.server.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = dedicated)
    (SID = YOUR_SID)
    )
    )
    “;
    $db_username = “YOUR_USERNAME”;
    $db_password = “YOUR_PASSWORD”;
    
    // connect to DB
    try{
    $conn = new PDO(“oci:dbname=”.$tns,$db_username,$db_password);
    }catch(PDOException $e){
    echo ($e->getMessage());
    }
    
    // get term string
    $text = “%”.$_GET[‘term’].”%”;
    
    // query DB
    $stmt = $conn->query(“SELECT * FROM YOUR_TABLE WHERE lastname LIKE ‘$text'”);
    
    // get results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $row_array[‘id’] = $row[‘ID’];
    $row_array[‘value’] = $row[‘LASTNAME’];
    $row_array[‘desc’] = $row[‘FIRSTNAME’];
    array_push($return_arr,$row_array);
    }
    echo json_encode($return_arr);
- damylola
  
  Nice article
  - Stathis
    
    Thanks :)
Billy

Uhm… your demo doesnt seem to be working and I tried your code and it’s not working either…
- Stathis
  
  I just tried the demo and it works as it should. For example, if you type “red” (without the quotes) it should display “Red Hot Chili Peppers”.
  
  What did you try?
  - Billy
    
    Wow I tried “cre” “ala” “ac” for creed alabama ac/dc lol sorry my mistake
    - Stathis
      
      It wasn’t clear what you had to search for, so I updated the demo adding the list of bands included in the database. :)
      - Billy
        
        Oh yea that’s better lol! I used your code as well, it works great! Great tutorial.
Darshana Umakanth

I’m trying to emulate this using a python script instead of a php script. Any pointers?
- Stathis
  
  Sorry, I don’t have any professional experience in Python so I can give you a proper answer.
  
  But, the process is probably going to be the same:
  
  – Grab the value autocomplete sent (it’s sent via a GET request, and the value is called “term”);
  – Connect to the database & run the appropriate query;
  – Format the query results as json, and return them.
brian

How would I implement this using several different text boxes? I tried a few ways but was not able to make it work. Also is the $_Get[‘term’] required to be term? I tried changing the index to use the txt box with id=”profile” and the function as well, this still worked without changing the GET, but when I changed it to $_Get[‘profile’] it would no longer retrieve the list.

In my application I trying to create a passdown system that is searchable by equipment, technician, zone, …. so each field should autocomplete from their individual tables. I would like to do it all in one search.php file instead of having 5 different files.
- Stathis
  
  Although I don’t know exactly what you’re after, my best guess is that you’ll probably need to bind different instances of autocomplete to each of the text inputs.
  
  Autocomplete plugin sends the value of the text input to the PHP script via the GET method in a variable called “term”, by default.
  
  This article is 3 years old now and I haven’t used the specific solution for a while, so I’m not sure if it is possible to change the name of the variable. Have a look here, you might find it useful.
http://www.collinscreative.co.uk Steven Collins

Excellent tutorial. I was pulling my hair out trying to get .live() working and came across this JQUERY UI. So simple to connect up to a MYsql data source and does everything needed for me ‘out of the box’
- Stathis
  
  Thanks Steven :)
Pavan

Hi
I have 7 million records as a response to mysql query. How do i process that.
- Stathis
  
  Hello Pavan,
  
  The number alone means nothing without context. Having said that, you’ll have to decide if an autocomplete solution suits your needs. It might do in theory, but if you have an average of 7 million records per response, then it obviously doesn’t. I don’t think that an autocomplete solution (at least the one presented in this tutorial) that returns more than a hundred results (even that is a high number) will be usable to the end-user.
  
  If you really want to use an autocomplete solution, you’ll probably have to do something to narrow down the results, but the “correct” answer depends on the kind of data you’re searching into.
  
  For example, if your data is of a relatively long length, consider increasing the minimum length of characters before sending a request. If the results belong to different “categories” then you can make the user select a category before searching for results in the selected category. You can also add some kind of delay in key presses to make sure that the user stopped writing before you send a request, you can cache queries to improve performance, etc.
Barry

I have been desperately searching for a combination tutorial of Perl and Oracle. MySQL is simialr enough in syntax, and I can figure that difference out. But I think part of the issue with Perl is the S and # characters in the jquery autocomplete function that are reserved in Perl for ccomment and variables. But even still, I think escape characters can get around this. But all this said, I’ve yet to see an example there working and all of my efforts to do it have failed.
But I guess I have this question that can be answered no matter what is used. Can the page the references a link in the autocomplete function be a static HTML page? Or does it have to be dynamic, with an extension of say, .asp, .cgi, .php, etc.?
- Stathis
  
  As far as I know, the extension is irrelevant, so it should work with an html extension. If you have a static data source in that page though, the autocomplete widget won’t filter the returned json result, since it assumes that your page already did that.
Barry

No, I don’t have static content. It’s pulled from Oracle. Oddly, I have somewhat found the problem, or created another issue. Part of this is with the perl logic itself. If I use a fetch_hashref class reference, the JSON returns properly but the dropdown menu does not get populated. If I change it from hashref to array, the JSON — to me — doesn’t look correct, but the dropdown menu population works correctly. I’m trying to tweak that now and see what the differences are, and why they’re returning such different results.
- Stathis
  
  Sorry, I have no experience with Perl so I can’t provide any suggestions. I hope you managed to solve the issue by now. :)
Barry

No problem, Stathis. Right after this, about a day or so, I got it solved. I was not manipulating the JSON array correctly, by assigning a parameter to the array.
So I’ve got that fixed. In fact, I have ALL of this fixed except for something that seems like it should be fairly easy. That is, basically displaying concatenated values in the dropdown menu via a js array.
I’ve got all the data returning, and can populate my city, state and zip fields properly. Problem is, in the autocomplete dropdown I’m only getting city to show, so you can’t see accompanying values with it. So a user would have to guess on say, Kansas City in order to determine what zip would show and whether the state would populate as KS or MO.
Once I get that figured out, I’m golden!
Thanks for the help!
- Stathis
  
  You can manipulate the autocomplete drop-down using the _renderItem method. Have a look at the source code of this example, which uses that method. Hopefully that will solve your problem. :)
Barry

Wow. So close. That is helping, and getting it there. I wish I could launch a screen shot for you on here.
I added the _renderItem extension in there, sandwiched within the select: function.
The behavior is a tad odd. Initially, I only get the city in the dropdown list, and then subsequent requests in the autocomplete text box show the city (the main variable here) as undefined, but do concatenate the string variables of the state and zip code.
I’m not sure how and why they wouldn’t all show initially, or some do and then some don’t. I thought I needed to sandwich the block of code in between the existing select: function parameters.

It looks like this. If you see something out of place, let me know, Stathis.

———
$(function() {
$(‘#abbrev’).val(“”);
$(‘#zip’).val(“”);
$(“#city”).autocomplete({
source: “cgi-bin/url.cgi”,
minLength: 4,
select: function(event, ui) {
$.ui.autocomplete.prototype._renderItem = function(ul, item) {
return $(“”)
.data(“item.autocomplete”, item)
.append(‘‘ + item.city + ‘-‘+ item.abbrev + ‘-‘ + item.zip + ‘‘)
.appendTo( ul );};
}
});
});

———
- Stathis
  
  Try using the following syntax:
  
  $(“#city”).autocomplete({ /* ... */ }).data( "ui-autocomplete" )._renderItem = function( ul, item ) { /* ... */ };
  
  If don’t manage to make it work, send me an email with a link to a demo and/or your code to have a look. :)
Barry

Ok, thanks, Stathis, let me try this.
- Barry
  
  Well, Stathis, that’s not working. I’ll try sending you an email with my code. Thanks!
Barry

I CANNOT believe it! I GOT IT!!
I can email you the complete block of code if you’d like it, if it will help someone else!

It was somewhere in between what you sent me and what I had!!
Unbelievable! Thank you for your help, Stathis!
- Stathis
  
  That’s great. :) It’s such a good feeling when you manage to complete something you were working on for a while!
  
  Rather than sending me the code, I would suggest to post it somewhere public (e.g. in a gist) and post the link here so (as you suggested) anyone else with the same problem can have a look at your solution.
Barry

Stathis, I have one other, maybe final, question on this. If I was to attempt to make this jsonp, and callback to a different server, how would I alter that existing code above? Any thoughts?
I’m not having much luck with that. I’ve got primary prod server A working, and secondary B (which is really the drop dead prod to use) not working…so trying to battle through that.

Thanks.
- Stathis
  
  I’ve never tried it, but in theory it should work without any modification to your existing code, apart from having to point the source to the other host’s URL.
  
  If that doesn’t work, consider checking if the other server is blocking any requests.
  
  Another option that I can think of, is to deal with it completely on the server side, without changing anything to your JavaScript code. For example, if your current source script is called “search.php”, keep that as a source and have that script make the request to the other server and serve the returned data to autocomplete itself (the autocomplete will still think that the data is served by a local script). I hope that makes sense.
Barry

Thanks, Stathis, that does make sense, and I’ve actually been trying that, to no avail.
I was trying to give my source an absolute (with http ref) rather than local reference.
I was also trying to pass through the jsonp parameter in there to do this.
Like so..
—
source: function( request, response ) {
$.ajax({
dataType: “jsonp”,
type: “GET”,
url: “http://servername/fullpath/bin/states.cgi”,
data: {term: request.term},
success: function(data) {
…more code block cont’d//
Not having a lot of luck though at the moment. I’ll keep trying it though. It’s weird, and frustrating in that regard!
Thank you though!
Barry

Hi Stathis, I thought of one other thing.

I’m now trying a similar one but would like to display a message if no results match or are found.
Can that be easily sandwiched in there? If so, can you please point me to a spot on your thread or site that has an example?
Thanks!

Barry
- Stathis
  
  Hello Barry, in case you didn’t solve your issue by now, off the top of my head the response event will probably help you accomplish what you’re after.
  - Barry
    
    Thank you, Stathis. I had seen this response argument in some examples, but my “ajax” call didn’t employ a request, response argument like other examples, so I wasn’t getting it to work. I’ll try it another way and see if I get it to work.
    Thanks!
Shalmaneser

I’m a Progress RDBMS & C programmer with a smattering of object oriented concepts. I’m trying to transition rapidly to the browser environment. This is a great example, I’ve already linked in my database to it and it works fine. However, I’m having trouble understanding how locate and store the user input. It would help me greatly if people could post their code that uses this example in different ways. Thanks.
- http://burnmind.com/ StathisG
  
  I’m not sure if I understard what you’re after, but have a look if this is what you’re looking for. If not, please explain in more detail (an example of a use case would be helpful) what you’re trying to accomplish.