Ajax Autocomplete

As a first technical post this is an ideal candidate as it took me bleedin' ages to get sorted out - so here goes.

The purpose behind this exercise is to try and make a form input text field on your webpage come up with auto-complete suggestions based on items already in the database in question. Okay lets start right at the very very beginning:

1. Go and get the scriptaculous scripts, download them to your computer and unzip them. Have a look through the folder you just unzipped, there should be a subdirectory called src with a bunch of .js files in it. Got it? Good! Bung controls.js and effects.js in the directory you are keeping your scripts in (for our example we'll call it scripts/).

2. Now you want to go and get prototype, and chuck the script prototype.js in the same location as the other two you just got.

3. Now then, in the header of your html file where the input text field appears you want to link the scripts you just got, something like this:

<script src="scripts/prototype.js" type="text/javascript"></script>
<script src="scripts/effects.js" type="text/javascript"></script>
<script src="scripts/controls.js" type="text/javascript"></script>

Make sure you include them in this order or they'll get upset.

4. In the same file go and find your input field. It might look like this to begin with:

<input name="rodent" type="text" />

We need to add an id attribute to the field as this is what the autocompleter looks at.

<input name="rodent" id="rdnt" type="text" />

5. Next job is to add in a div underneath the input tag to take the results, something nice and simple:

<div id="autocomplete">

6. Last but no means least for this file, create a new autocomplete object:

<script language="JavaScript">

new Ajax.Autocompleter("rdnt", "autocomplete", "auto_comp.php", {frequency: 0.1});

</script>

A bit of explanation here: there parameters passed are 1. the input id, 2. the div id, 3. a php file that returns a <ul> list of database results and 4. I like frequency 0.1 (seconds) as it seems pretty smooth - for big databases this might be a bit too much strain.

7. Ok now well visit the php. This is quite simple to do: imagine we have a database table with an id and name of a small furry animal. We want to return autocomplete suggestions based on what's been typed i.e. if someone types a "b" into the input box above we want to see entries like "badger" and "beaver" pop up. Note that the value currently in the text box will be passed via the $_POST['<input name>'] variable. Something like the following should do the job:

<ul>
<?php
$text = mysql_escape_string($_POST['rodent']);
$sql = "SELECT * FROM rodents WHERE name LIKE '".$text."%' GROUP BY name"
$result = mysql_query($sql)
while ($row = mysql_fetch_array($result)) {
echo '<li id="ac_items">'.$row['name'].'</li>'
} ?>
</ul>

Nothing exciting just returning an unordered list of the query returns.
This should now be working (touch wood) but probably doesn't look very special. It's time to go and visit the css - this is up to you how you do it - I would recommend setting list-style: none and padding-left: 0 in div.autocomplete ul li {}, change the cursor for the li elements and probably include a mouse over colour too so it's ovbious the elements of the list do something.

I've skipped a good deal in this little post but there's a fantastic wiki for all the script.aculo.us scripts and many other points and examples to be found through google.

Good Luck