How To: jQuery Autocomplete

August 19, 2009 / PHP / by Paul Robinson / 78 Comments

This post was published back on August 19, 2009 and may be outdated. Please use caution when following older tutorials or using older code. After reading be sure to check for newer procedures or updates to code.

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:

$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";

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

//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();

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

$(function() {
  $(".autosearch input#subcategory").autocomplete(data, {
  	  width: 180,
	  selectFirst: false,
	  delay: 250
  });
})

$(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:

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

<head>

$(function() {

$("#autocomplete input#autotext").autocomplete(data, {

width: 180,

selectFirst: false,

delay: 250

});

</script>

</head>

<body>

</form>

</body>

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

AJAX Autocomplete jQuery

Paul Robinson

WordPress plugin & theme developer, user of Laravel and learner of React.js. Can usually be found listening to Japanese, and Korean Pop or Vocaloid music while developing.

78 Comments

Thanks, this was just what I was looking for! I also used the extraParams in the JQuery plug-in to set a table and column value. That way I can remotely call the same php function page and pass it the particular table and corresponding column I need for any autofill on my site.

No problem.

It’s a really handy jQuery plugin, kudos to bassistance.de for making it. I just thought this might help anybody who is struggling with any of the options, or with how to set it up without causing major database stress. Glad it helped. 😉

Would it be possible to supply a complete example, from to ?

sorry, it stripped out my code. A full example, from the top of the HTML page to the bottom? Links and all?

author

Yeah WordPress is a pain when posting code in the comments. If you need to post code try pastebin.com & paste the URL here.

As for the HTML it is just a standard HTML page with a form & an input field on it. I’ll add it to the post though so you can see it.

Thanks Paul,

It’s just not working for me. This is tricky, because I can’t tell if the javascript isn’t working, or the SQL isn’t working WITH the javascript. I’ve tested the SQL page and that’s fine on its own (I think!). Also, is the “fake” javascript SQL script supposed to have a .js extension? If so, now we’re messing with mime types, right?

author

Yeah the file would still be .php as your just changing it’s header to make the browser think it’s a js file, it doesn’t actually have to have the .js extension. If you visit the “fake” js file you should get output that looks something like:

var data = ['item1', 'item2', 'item3'];

1	var data = ['item1', 'item2', 'item3'];

If you do it’s working fine, other than that I’ve no idea. You could try using FireBug if you have FireFox that gives some good debug help. Also check your jQuery targeting, I sometimes overlook it. 😆

I have the SQL working. It prints like your example. However, the form is not processing the autocomplete. http://pastebin.com/m65c91184

author

Hmm very bizzare. I’m not quite sure why that isn’t working properly. You can see this “fake” js system running over on http://wallpapers.celeborama.net/ which I built. The autocomplete runs on a slightly more complex manner as I needed to switch out different data sets instead of just one.

You are welcome to see if you can see any differences, but so far I can’t see any. 🙁

Yeah, it’s weird. I can’t get the autocomplete to work at all! Nothing pops up, drops down, jumps out. Nada. Also, I don’t see anything like this on that page

script type=”text/javascript” src=”autocomplete_sql.php”

author

The line is slightly different.

script src="http://wallpapers.celeborama.net/wallpaper/autodata" type="text/javascript"

1	script src="http://wallpapers.celeborama.net/wallpaper/autodata" type="text/javascript"

because I use the PHP framework Kohana it just changes the url structure to a neater version. It still works the same way.

I would normally try to help a little more by helping to debug but I don’t have access to my localhost at the moment. My graphics card died & I’m still waiting on my RMA’d replacement. 🙁

Thanks for your help anyway 🙂

I am trying to get this to work but I don’t seem to have anything popping up. I tried your site in the above comments and I also don’t get anything to come up. I tried Safari and no dice.

Link is: http://the14thgod.com/testing/auto_complete/ I have no JS errors and if I go to the fake js (http://the14thgod.com/testing/auto_complete/scripts/data.php) it renders out the options just fine.

Any ideas?
Thanks for any and all help,
Justin

author

Hey Justin, I can’t get your site to load at the minute (for some reason I get a failed to connect error) so I can’t check out the pages but my best guess (as the js is output ok) is that there is something wrong in the HTML & auto complete setup. I’ll check over the tutorial again to make sure there are no mistakes.

author

Ok so I just noticed a major f*** up in the code, that I completely missed because I automatically assumed I had put it in. No excuse really except me being a dumb shit. 😆

I’ll correct the post in a second, but you need to put the autocomplete jquery code in a on dom ready or it won’t run. Should look like this:

$(function() {
  $("#autocomplete input#autotext").autocomplete(data, {
	  width: 180,
	  selectFirst: false,
  	  delay: 250
  });
})

$(function() {

$("#autocomplete input#autotext").autocomplete(data, {

width: 180,

selectFirst: false,

delay: 250

});

})

Again I’m really sorry about that.

I have it in a $(document).ready(function(){ already. So that should be fine. My site is now back up. It went down about 5 minutes after I put the question on here. I wasn’t too happy lol. It is now back up if you don’t mind checking it out. Thanks!
Justin

Well if that’s the case i’m stumped. I just copied a duplicate of the code into a blank HTML file on my localhost and it worked straight away. You’ve just caught me after I switched my com off (I’m writing this on my iPod touch) so I’ll chrch out your pages tomorrow. From what I can see of the JS file though it’s perfect.

Paul –
I didn’t notice this before, but when I typed a JS error occurred. There was a typo in the script name 🙁
Go figure. Thanks for all your help. (and this tutorial of course)
Justin

Don’t worry about it. I’m just glad you got it sorted.

Great script!
I’m trying to write the SQL to only bring back results that match the text fields value as I have over 800 items in the database.
This would usually be done with a post from the form and a get in the PHP but I’m not sure how to go about this.

Can anyone help me?

I’m not sure what you mean? If you want to have it so your MySQL database brings back what is written in your text field, you would use the first bit of code in the tutorial. The autocomplete script sorts out the AJAX submission on key press etc.

Let me know if you meant something else & I’ve gotten this wrong.

Hi Paul,
Yes, I think you’re understanding what I’m after.
However, I dont understand how the sql in the php (column LIKE ‘value%’) can get the value of the text field input?
I would have expected something like $_GET[‘autocomplete’] to pass the value across. I’m new to PHP and JQuery especially 🙂

author

Ahhh, ok I get what you mean. In your autocomplete call you would point to your script. So something like:

$(function() {
  $("#autocomplete input#autotext").autocomplete('script.php', {
	  width: 180,
	  selectFirst: false,
	  delay: 250
  });
});

$(function() {

$("#autocomplete input#autotext").autocomplete('script.php', {

width: 180,

selectFirst: false,

delay: 250

});

The data will be accessable on the other end using $_GET['q']. Just remember that before you place it into a MySQL query you should escape the data using something like mysql_real_escape_string(), you can find more about it on php.net.

Hope that helps. 😉

what I have at the minute is the sql is pulling back all rows in the database… which is about 800 rows. Then the autocomplete does its job and suggests as you type. On the face of it everything works great, but I’m worried about server load pulling back 800 rows every time.
Is this the way its supposed to work?
I expected to use the “where column like %autocomplete%” to limit the returned results, then autosuggest from those rows returned.
Is the latter achievable?

Thanks Paul – You’re a legend 🙂
Its now working with a single keyword entered, but I get no results with more than one keyword entered.
I’m assuming this is all down to the sql? If it is, I’m sure I can build up a dynamic query by exploding the values in q?

author

Yeah you would have to do an explode, as long as your MySQL database is capable of handling the load you should be ok.

If not that is what the second option in my tutorial is for. Instead of asking the MySQL database each time, echo all the data into a JS object & then query that. I’m not sure if you can do that with multiple keywords though.

Its coming along nicely now Paul. Not tested it fully but the multiple keyword aspect appears to be working. The only thing I’ve found is that only the 1st word is in bold. All other words (separated by spaces) in not showing in bold.
The autocomplete.js does have the word ‘strong’ in there, so I’m guessing thats where I need to start scratching my head lol!

author

Sorry I didn’t get back to you quickly been watching American Idol. 😆

Well it appears that the bold is done by a parser function under the highlight option of autocomplete. Here is the default function:

highlight: function(value, term) {
		return value.replace(new RegExp("(?![^&;]+;)(?!<[^<>]*)(" + term.replace(/([^$()[]{}*.+?|\])/gi, "\$1") + ")(?![^<>]*>)(?![^&;]+;)", "gi"), "<strong>$1</strong>");
	}

highlight: function(value, term) {

return value.replace(new RegExp("(?![^&;]+;)(?!<[^<>]*)(" + term.replace(/([^$()[]{}*.+?|\])/gi, "\$1") + ")(?![^<>]*>)(?![^&;]+;)", "gi"), "<strong>$1</strong>");

}

You can change it by defining a new one when initialising. Something like:

$("#autocomplete input#autotext").autocomplete('script.php', {
	width: 180,
	selectFirst: false,
	delay: 250,
        highlight: function(value, term) {
		return value.replace(new RegExp("(?![^&;]+;)(?!<[^<>]*)(" + term.replace(/([^$()[]{}*.+?|\])/gi, "\$1") + ")(?![^<>]*>)(?![^&;]+;)", "gi"), "<strong>$1</strong>");
	}
});

$("#autocomplete input#autotext").autocomplete('script.php', {

width: 180,

selectFirst: false,

delay: 250,

highlight: function(value, term) {

return value.replace(new RegExp("(?![^&;]+;)(?!<[^<>]*)(" + term.replace(/([^$()[]{}*.+?|\])/gi, "\$1") + ")(?![^<>]*>)(?![^&;]+;)", "gi"), "<strong>$1</strong>");

}

});

I’ve just kept the same function, but that is an example to show you how to define a different highlighter function. I’m afraid javascript based regex is foreign to me so I can’t help there. 🙁

I have just noticed that there is a option for setting a multiple seperator, but it is set to ‘, ‘ by default. You could try changing that if it helps.

Hope I’ve been able to help a little more. Sorry I can’t be more help with the regex, but I barely get by with PHP regex never mind JS regex. 😆

American Idol eh? lol!
You certainly have been a huge help Paul – I couldn’t have done it without you 🙂

author

Yep. I only watch the auditions. I like to laugh at the people who are adamant that they can sing when they really can’t. 😛

No problem, glad I was of help. 😉

I may be pushing my luck here (like I haven’t already taken up enough of your time!), but there is just one more thing I’m stuck on.
When the user hits the enter key when highlighting their selection, the input field gets filled out correctly… however, I want the form to also get submitted at the same time. I’m using the traditional submit rather than Ajax.
People talk about ‘triggers’ and ‘callbacks’ but I can’t find an actual coded example?

author

Hmmm. Well I haven’t used triggers much but while looking through the source code for autocomplete I noticed a bit that blocks submission of the form when hitting enter (for selecting a item).

I believe it is hardcoded into the code though, I’m not sure if removing it would help (I think it’s commented so it shouldn’t be hard to find in the source). Not sure there is a way. Only way I can think of isn’t really the best solution, but here it is just in case:

$('form#target input-field').bind('keyup', function(e) {
  var code = (e.keyCode ? e.keyCode : e.which);
  if(code == 13) { //Enter keycode
     //Do something
  }
});

$('form#target input-field').bind('keyup', function(e) {

var code = (e.keyCode ? e.keyCode : e.which);

if(code == 13) { //Enter keycode

//Do something

}

});

Problem with that is that it may not allow time for the autocomplete to fill the box in before submitting the data meaning it may be blank.

Sorry if that hasn’t been much help, but my best guess would be to scan through the source & try to find the part that stops submission & disable it. Also just to let you know the site will be down from 3:00PM – 4:00PM GMT for a while for some redecoration. 😉

Hi Paul,
I’ve run into another issue. As you were so helpful last time I hoped you could help me out again? 🙂
I’ve posted the question on the JQuery forum:
http://forum.jquery.com/topic/autocomplete-stop-rollover-return-submitting-data#14737000000700381

Fingers crossed its easy to resolve 🙂

author

Hi Chris, Apologies for not getting to you earlier. WordPress seems to have stopped sending me comment received emails so I hadn’t realized you’d left a comment. I’ll have to look into that.

I’ve had a look at your request & I’m not sure it has been solved yet, I believe WordPress uses jQuery autocomplete (or a similar one) in it’s tag entry field & it has exactly the same problem. Sorry that I haven’t been much help. 🙁

Hi Paul,
Thanks for looking into it anyway – I appreciate it.
I’m surprised it hasn’t cropped up as an issue for other people. But it does seem to be the same in all plugins based on the JQuery Autocomplete. It the big area of functionality that break away from the Google standard.

Chris

author

No problems, if I find anything I’ll leave a comment here or I’ll drop you an email (if you don’t mind of course).

I’m a little busy right now trying to get some more tutorials done & the comments threaded (as you can probably tell), but I’ll see what I can find once I’ve done those. 😉

author

Hey Chris,

I think I might have found a solution to your problem. There is a setting in the autocomplete options called ‘selectFirst’. It is automatically set to ‘true’, apparently if you set it to false it won’t auto select the first option on tab or return.

Let me know if that works out. 😉

Hi Paul,
That sounds encouraging! I’ll give it a go when I’m back in the office tomorrow.
Failing that, line 138 has the case statement for the return key. I might be able to put an extra if statement around it i.e. if selection made by keyword, return true.

I’ll be back 😉

author

No problem. By the looks of the options that should do what you are asking for. I, personally, think that it should be set to false by default, but it is set to true as far as I am able to tell.

Anyway, let me know how it goes. 😉

Hi Paul,
Bad news I’m afraid. It seems ’selectFirst’ is just to select (highlight) the 1st row that is returned. I already had it set to false so that the user had to press the down cursor to select the 1st row, or use the mouse.
It has no effect on the mouseover+return key combination.

Plan B 🙁

Think I’ve cracked it!
I set up a new variable called keyboardSelect and set it to 0.
Next set keyboardSelect to 1 for each key case (up, down, pageup/down).
On the return key case wrapped everything in if (keyboardSelect == 0) so nothing happens if the keyboard selection hasn’t been used.
Then to reset everything when the autocomplete is hidden, add keyboardSelect = 0 to the hideResults() function, and the hide: case function on line 722.

Not tested it fully yet but looks like it should work!

… and added keyboardSelect=0; to line 612 to reset when a highlight has been made with the mouse i.e. stop jquery submit on return key again.

author

I never give it a thought that it wouldn’t stop mouseover submission.

Nice job with the additions to the code. As far as I can tell the extra bits of code sound like it should work well. Let me know how you get on or if I can be of any more help. 😉

Hi
Im trying to make it so that when the user selects a product from the drop down the product selected is displayed.

So how can i get the product_url as a hidden field in the drop down and then use this url on selection?

Im using the method of filling a fake js file.

Thanks

author

Hi,

I’m not sure what you mean by drop down? Can you explain what you are trying to do a little more.

Hi Paul

When users start typing in my website the text input field expands to show the matches below in a list. (Sorry not drop down)

At the moment wen the user uses the cursor to select their chosen record and they press enter, the chosen record gets populated in the search field.

I need to make changes so that when the user highlights the record they want and press enter, the url within that product is loaded.

I have added javascript to the input box so it will now search on the chosen field but I need the browser to actually go to the product url.

Thanks

Darren

author

So I guess you are looking for something like Wikipedia’s search. It’s really all done in the back-end.

I can try to help you with code if you like, but here is the general idea. You need to have the product submit to your PHP script. Take the product name & match it against all of your products (assuming they have URLs associated with them). Check if the result of the match is greater than 1, if so show some sort of search result page, otherwise jump straight to the products page.

That is the basic idea behind Wikipedia’s search (or best I can guess). Again if you need any more help I’m happy to help. 😉

Hi Paul,

I’ve got a tricky situation wherein I have multiple autocompletes on a page, so I don’t really want to write out a jquery function for each of them. What I want to do is just call a function, and pass some params which will allow me to use a single jquery function for all. Here’s what I’ve got…

The JS

function lookup(theID, theValue)
{
	$(function() {

	$('#'+theID).autocomplete("search.php", {
		width: 260,
		selectFirst: false,
		max:15,
		extraParams: {id:theID}
	}); 
}); 
};

function lookup(theID, theValue)

{

$(function() {

$('#'+theID).autocomplete("search.php", {

width: 260,

selectFirst: false,

max:15,

extraParams: {id:theID}

});

};

The html:

<form action="" method="post">
	<input onkeyup="lookup(this.id, '32');" type="text" name="apple" id="apple" />
  <input type="submit" value="Get Value" />
</form>

</form>

The data set is taken from bassistance.de so I;m not conerned about that, and using Firebug I can see the lookups firing and results being returned. For some reason however, the actual results don’t appear. It definetly has something to do with my function…

Any help would be awesome. Here’s a demo link:

http://budgetbeacon.com/test.php

author

Well offhand I can only see that you don’t really need the $(function(){}) wrapped inside your function as you don’t want to run it on DOM load. I’m not sure if that is the cause of the problem, but you could try while I have a proper look at your code. 😉

Hi Paul,

I commented that code out (legacy from testing without function call) and I get the same result.

Cheers!

author

Hmmm. I can’t see anything else obvious wrong with the code. I’ll see if I can cobble together a test on my localhost to try it out & let you know the result in a little while. 😉

author

Ok so I’ve managed to get a result on your script & my localhost. It seems to only work if you enter a few characters (after page reload), not sure why at the minute. For example entering “a” will not give anything, but “ad” will. After that you get results everytime you enter a letter.

The other odd thing I’ve noticed is that when you enter more than one letter, both your example & my local version end up using an obcene amount of server queries & I’m not sure what’s causing it…

Edit: Forgot to mention I do see results shown on the page not just in Firebug.

author

Sorry about commenting to myself soooo many times, but I think I might have a solution to your problem. It might need tweaking a little but here is the jist:

$(function() {
	$('body form input[type=text]').each(function() {
		$(this).autocomplete('search.php', {
			width: 260,
			selectFirst: false,
			max:15,
			extraParams: {id: $(this).attr('id')}
		});
	});
})

$(function() {

$('body form input[type=text]').each(function() {

$(this).autocomplete('search.php', {

width: 260,

selectFirst: false,

max:15,

extraParams: {id: $(this).attr('id')}

});

})

The only problem with this method is that it relies on looping through each form. If you can figure out the CSS selector needed to do it, it solves the multiple request problem.

Turns out it was because it was recursively binding keyup functions to the input field, due to the function call on keyup & then autocomplete binding to keyup.

Hope that helps & let me know if I can help any more. 🙂

Older Comments

Newer Comments

Full HTML

Follow Me

Categories

Recent Posts

Recent Comments

I'll keep your WordPress site up-to-date and working to its best.