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, [...]

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

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

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
  });
})

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

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