Ajax Makeover: Type-Ahead Service

For this second article of the Ajax Makover serie, I will introduce the server side component of the Smart Type-Ahead functionality.

You may want to jump to the updated demonstration page or review the client-side implementation first. It now reproduces the full ‘google suggest’ experience.

The Type-Ahead Service accepts two kinds of request:

1. A ‘Get’ request, containing the user input and the field id in the query string. The service returns a list of matching suggestions.
2. A ‘Post’ request, with the user input and the field id in the posted data. The service adds the input text / field id pair to its suggestions database.

The suggestion database

The suggestion database tracks all user inputs and their frequency. Here is a MySQL table description:

--
-- Table structure for table `SUGGESTIONS`
--
CREATE TABLE `SUGGESTIONS` (
`ID` int(11) NOT NULL uto_increment,
`FIELD_ID` varchar(100) NOT NULL default '',
`VALUE` varchar(255) NOT NULL default '',
`FREQUENCY` int(11) NOT NULL default '1',
PRIMARY KEY (`ID`))
TYPE=MyISAM AUTO_INCREMENT=1 ;

Handling of the ‘GET SUGGESTION’ request

The service described here is implemented in PHP (See the full code).

header('Content-Type: text/plain');

The services will return a list of suggestion, as plain text.

$inputTxt = secureUserInput($_GET['inputtxt']);
$inputId = secureUserInput($_GET['inputid']);

secureUserInput is a ’sanitization’ function that insure than no harmful data is injected into the service.

$dbusername = "your_db_username";
$dbpassword = "your_db_password";
$link = mysql_connect("",$dbusername,$dbpassword )
or die ("error: Sorry, DB server unavailable. Please try again later or contact the webmaster.");
mysql_select_db ("your_typeahead_db")
or die ("error: Sorry, database unavailable. Please try again later or contact the webmaster.");

This is the usual database connection.

if ($inputId != '') {
if($inputTxt != '')
$query = "SELECT VALUE FROM SUGGESTIONS WHERE FIELD_ID = '$inputId' AND VALUE LIKE '$inputTxt%' ORDER BY FREQUENCY DESC LIMIT 0 , 20";
else
$query = "SELECT VALUE FROM SUGGESTIONS WHERE FIELD_ID = '$inputId' ORDER BY FREQUENCY DESC LIMIT 0 , 20";

while ($row = @mysql_fetch_array($db_result)) {
$suggestions .= $row["VALUE"]."&";
}

This is the core of the Type-Ahead Service. The SQL Statement returns an ordered list of suggestions, where the most commonly used one appears first.

echo substr($suggestions,0,strlen($suggestions)-1);
}

This is the part that returns the list of suggestion (an ampersand delimited list) to the client.

Handling of the ‘POST SUGGESTION’ request

else {
$inputTxt = secureUserInput($_POST['inputtxt']);
$inputId = secureUserInput($_POST['inputid']);

This time we’re retrieving posted data.

if ($inputTxt != '' && $inputId != '') {
$query = "SELECT ID, FREQUENCY FROM SUGGESTIONS WHERE FIELD_ID = '$inputId' AND VALUE = '$inputTxt'";
$db_result = mysql_query($query)
or die ("error: Sorry, operation failed. Please try again later or contact the webmaster.");
$record = @mysql_fetch_array($db_result);
$recordId = $record['ID'];
$frequency= $record['FREQUENCY'] + 1;

Before inserting the new data, we need to check if we already have a record for the text/id pair. If we do, we’ll only update the frequency count.

if(is_numeric($recordId)) {
$query = "UPDATE SUGGESTIONS SET FREQUENCY = $frequency WHERE ID = $recordId";
} else {
$query = "INSERT INTO SUGGESTIONS (FIELD_ID, VALUE) VALUES ('$inputId','$inputTxt')";
}
$result = mysql_query($query);
}
}

We’re all set, the database has been updated.

This wraps up the auto-complete part of the Ajax Makover. We’ll keep adding more building blocks to the time-tracking application in the next articles.

Do you have any suggestion on how to improve the type-ahead functionality ? Please share your remarks.