Ajax autosuggest/autocomplete from database

Since google introduced the autosuggest or autocomplete search form on their toolbar everyone started looking for this piece of code to put on their websites. It's not new in the Ajax world but it's not seen as often as it should if you ask me. Why? It's hard to find good examples or examples that match everyone's needs. An Ajax autosuggest script is supposed to help the visitor and present in real time (as they type) some possible results based on their entered words/characters. In my opinion, this is a great feature to enhance the user experience. It's not hard to build an autocomplete search form. In fact, it's very easy. Our application will use script.aculo.us which is a rich Javascript library for the Ajax effect, an index page that will hold the search form and a response page that will perform the search based on the $_POST parameter received from our index and display some possible matches/results/suggestions. Since it's very easy to follow the demos and examples from the scriptaculous website we will focus more on the response page and the way that it will select the results and display them. Using what's explained in the PHP search engine tutorial I managed to build a nice response page in order to display my suggestions using Ajax. The response page will check the post data, stem it (plurals to singulars etc.), clean it (for common words, too short words etc.) and perform an sql search do display the entries that might match our words.

<?php
	require_once('db.php');
	include('classes/stem.php');
	include('classes/cleaner.php');
	
	if( !empty ( $_POST['search'] ) ):
	
		$string = $_POST['search'];
		$main_url = 'http://www.roscripts.com/';
	
		$stemmer = new PorterStemmer;
		$stemmed_string = $stemmer->stem ( $string );
	
		$clean_string = new jSearchString();
		$stemmed_string = $clean_string->parseString ( $stemmed_string );		
		
		$new_string = '';
		foreach ( array_unique ( split ( " ",$stemmed_string ) ) as $array => $value )
		{
			if(strlen($value) >= 3)
			{
				$new_string .= ''.$value.' ';
			}
		}

		$new_string = substr ( $new_string,0, ( strLen ( $new_string ) -1 ) );
		
		if ( strlen ( $new_string ) > 3 ):
		
			$split_stemmed = split ( " ",$new_string );
		        
		        mysql_select_db($database); 
			$sql = "SELECT DISTINCT COUNT(*) as occurences, title, subtitle FROM articles WHERE (";
		             
			while ( list ( $key,$val ) = each ( $split_stemmed ) )
			{
		              if( $val!='' && strlen ( $val ) > 0 )
		              {
		              	$sql .= "((title LIKE '%".$val."%' OR subtitle LIKE '%".$val."%' OR content LIKE '%".$val."%')) OR";
		              }
			}
			
			$sql=substr ( $sql,0, ( strLen ( $sql )-3 ) );//this will eat the last OR
			$sql .= ") GROUP BY title ORDER BY occurences DESC LIMIT 10";
		
			$query = mysql_query($sql) or die ( mysql_error () );
			$row_sql = mysql_fetch_assoc ( $query );
			$total = mysql_num_rows ( $query );
			 
			if($total>0):
	
			        echo '	<div class="entry">'."\n";
				echo '		<ul>'."\n";
					while ( $row = mysql_fetch_assoc ( $query ) ) 
					{				
						echo '			<li>'."\n";
						echo '				<a href="'.$main_url.'articles/show/'.$row['id'].'">'.$row['title'].''."\n";
						echo '				<em>'.$row['subtitle'].'</em>'."\n";
						echo '				<span>Added on 2007-06-03 by roScripts</span></a>'."\n";
						echo '			</li>'."\n";				
					}
					
				echo '		</ul>'."\n";
				echo '	</div>'."\n";
			endif;
		endif;
	endif;	
?>

It's more like a complex code and I encourage you to read and understand the PHP search engine tutorial, you can delete everything that is above and enter an sql query that you built in case you don't want to use this method of searching. As a short overview of the code engine I must say that no results will be shown if the string's length (after it will be stemmed and cleaned for common words/stop words) will be smaller than 3 characters:

if ( strlen ( $new_string ) > 3 ):

and that the result (if any) must be an unordered list (ul). Here's a demo with our application (download link lower on the page). Have fun!

For coding help and further support please use the forum.