Looking to add an auto suggest box on your website that searches your database? This article shows you how to use the Scriptaculous Ajax.AutoCompleter with PHP and MySQL to build a database powered autosuggest box.
First make sure you have the Scriptaculous library downloaded and included in your webpage. After you have downloaded the library and placed it in your website’s folder, including it is as simple as adding the following script tags between your head tags.

<script src="prototype.js" type="text/javascript"></script>
<script src="scriptaculous.js" type="text/javascript"></script>

Now, on to create the autcompleter in your webpage. Add the following code in your page where the Autocompleter should appear:

<p>
<form>
<input type="text" name="q" id="Search" size="30"/>
<input type="submit" value="Search"/>
</form>
</p>

<div id="SuggestBox" style="border: 1px solid #808080; background-color: #FFFFFF;">&nbsp;</div>

The SuggestBox div is where the search results will appear. You don’t need to worry about CSS positioning, Scriptaculous will automatically display it appropriately below your suggest input.

You will now need to add some Javascript to your page to use the Scriptaculous AutoCompleter and link it to your form and suggest div. Place the following code at the end of your page to enable your autocompleter.

<script language="Javascript">
function AutoComp() {
  var myAutoCompleter = new Ajax.Autocompleter('Search', 'SuggestBox', 'autocompleter.php');
}
document.onLoad = AutoComp();
</script>

This code creates a new Autocompleter and points it to the input with id “Search”, suggest div with id “SuggestBox”, and a PHP script named “autocompleter.php” located in the same folder on your site as the current page.

Now for the PHP/MySQL part of the article. Create the script named “autocompleter.php” as referenced above. Scriptaculous will use POST and the form input name to pass the search terms to your PHP script, so we will need to reference that for our search.

The following is an exampe of the PHP script you would use to search an “Articles” table in your database. Simply change the SQL as needed to search a different table.

<?php

$strSearchVal = $_POST['q'];

if (strlen($strSearchVal) < 1) {
 return false;
}

// Connect to your database here:
mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('dbname');

// Sanitize User Input for Security
$strSearchVal = mysql_real_escape_string($strSearchVal);

$strSQL = 'SELECT ID, Name FROM Articles WHERE (Name LIKE \'%' . $strSearchVal . '%\')';
$result = mysql_query($strSQL);

echo '<ul>';

while ($arrThisRow = mysql_fetch_array($result)) {
 echo '<li>' . $arrThisRow['Name'] . '</li>';
}

echo '</ul>';

?>

And now you should have a working autocompleter powered by PHP and MySQL.

Tips:

• You can better style the SuggestBox list with CSS.
• When using the autocompleter as a search form, you can add links to the list items to take the user to the page when the item is selected, rather than simply populating the search input.