Ajax autosuggest/autocomplete from database - problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Gargoyle
Forum Contributor
Posts: 130
Joined: Wed Jul 14, 2010 12:25 am

Re: Ajax autosuggest/autocomplete from database - problem

Post by Gargoyle »

use explode() instead of split()
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Ajax autosuggest/autocomplete from database - problem

Post by defroster »

Thanks, that works better. But all i get is the good old 'bla:' I am so confused about this:

Code: Select all

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

		$new_string = substr ( $new_string,0, ( strLen ( $new_string ) -1 ) );
		
		print_r("bla: $total");
			exit;
			
		if ( strlen ( $new_string ) > 3 ):
		
			$split_stemmed = explode ( " ",$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;	
	

	
?>
Gargoyle
Forum Contributor
Posts: 130
Joined: Wed Jul 14, 2010 12:25 am

Re: Ajax autosuggest/autocomplete from database - problem

Post by Gargoyle »

thats ok because $total hasn't yet been set at the time you try to print it.

put it right before

Code: Select all

if($total>0):
again and see what happens
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Ajax autosuggest/autocomplete from database - problem

Post by defroster »

Ok, thanks again. A little progress now. I now got:

Code: Select all

bla: 0
with the following code:

Code: Select all

<?php
	require_once('db.php');
	include('classes/stem.php');
	include('classes/cleaner.php');
	
	$_POST['search'] = 'some valid keyword';
	
	
	if( !empty ( $_POST['search'] ) ):
	
		$string = $_POST['search'];
		$main_url = 'http://www.roscripts.com/';
	
		$stemmer = new Stemmer;
		$stemmed_string = $stemmer->stem ( $string );
	
		$clean_string = new jSearchString();
		$stemmed_string = $clean_string->parseString ( $stemmed_string );		
		
		$new_string = '';
		foreach ( array_unique ( explode ( " ",$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 = explode ( " ",$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 );
		
			print_r("bla: $total");
			exit;
			 
			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;	
	

	
?>

The database looks like this:
Image

So my conclusion is that nothing is found with this SQL query and therefore 'total is zero'. But what would be wrong with the query then?? Going crazy soon. Appreciate all the help you are providing. I am trying to learn.
Gargoyle
Forum Contributor
Posts: 130
Joined: Wed Jul 14, 2010 12:25 am

Re: Ajax autosuggest/autocomplete from database - problem

Post by Gargoyle »

oh come on.

Code: Select all

$_POST['search'] = 'some valid keyword';
should be

Code: Select all

$_POST['search'] = 'yolanda';
for example
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Ajax autosuggest/autocomplete from database - problem

Post by defroster »

Thanks, Of course.. what a miss from my side..

Ok, so now I get "bla: 1 " which is a good sign and when I remove the 'exit;' I still get no output.. ?

current code:

Code: Select all

<?php
	require_once('db.php');
	include('classes/stem.php');
	include('classes/cleaner.php');
	
	$_POST['search'] = 'yolanda ';
	
	
	if( !empty ( $_POST['search'] ) ):
	
		$string = $_POST['search'];
		$main_url = 'http://www.roscripts.com/';
	
		$stemmer = new Stemmer;
		$stemmed_string = $stemmer->stem ( $string );
	
		$clean_string = new jSearchString();
		$stemmed_string = $clean_string->parseString ( $stemmed_string );		
		
		$new_string = '';
		foreach ( array_unique ( explode ( " ",$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 = explode ( " ",$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 );
		
			print_r("bla: $total");
			
			 
			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;	
	

	
?>
Gargoyle
Forum Contributor
Posts: 130
Joined: Wed Jul 14, 2010 12:25 am

Re: Ajax autosuggest/autocomplete from database - problem

Post by Gargoyle »

you're fetching the single result before the while loop.

change

Code: Select all

$query = mysql_query($sql) or die ( mysql_error () );
$row_sql = mysql_fetch_assoc ( $query );
$total = mysql_num_rows ( $query );
to

Code: Select all

$query = mysql_query($sql) or die ( mysql_error () );
$total = mysql_num_rows ( $query );
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Ajax autosuggest/autocomplete from database - problem

Post by defroster »

Ok Thanks Gargoyle for having the patience with this. I am getting a search result now. However the page calling for the ajax function is not working at all. The results should show up as a list in index.php while typing basically. Help is very much appreciated :)

index.php

Code: Select all

<html>
<head>
<title>roScripts - Ajax auto-suggest</title>

	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<script src="lib/prototype.js" type="text/javascript"></script>
	<script src="src/scriptaculous.js" type="text/javascript"></script>
	<script src="src/unittest.js" type="text/javascript"></script>
	<link rel="stylesheet" href="css/style.css" type="text/css" />

</head>
	<body>
	
		<div id="container">
	
			<form method="get" action="">
				<label for="testinput">Search</label><br />
				<input type="text" id="search" name="search" autocomplete="off" class="input" value="" /><br />
				<div id="update" style="display:none;position:relative;"></div>
				<input type="image" name="register" class="submit-btn" src="http://www.roscripts.com/images/btn.gif" alt="submit" title="submit" />
			</form>

			<script type="text/javascript" language="javascript" charset="utf-8">

				  new Ajax.Autocompleter('search','update','response.php', { tokens: ','} );
				
			</script>
		
		</div>

	</body>
</html>
response.php

Code: Select all

<?php
	require_once('db.php');
	include('classes/stem.php');
	include('classes/cleaner.php');
	
	#$_POST['search'] = 'yolanda';
	
	
	if( !empty ( $_POST['search'] ) ):
	
		$string = $_POST['search'];
		$main_url = 'http://www.roscripts.com/';
	
		$stemmer = new Stemmer;
		$stemmed_string = $stemmer->stem ( $string );
	
		$clean_string = new jSearchString();
		$stemmed_string = $clean_string->parseString ( $stemmed_string );		
		
		$new_string = '';
		foreach ( array_unique ( explode ( " ",$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 = explode ( " ",$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 );
		
			$query = mysql_query($sql) or die ( mysql_error () );
			$total = mysql_num_rows ( $query );
		
		
			print_r("bla: $total");
			
			 
			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;	
	

	
?>
Gargoyle
Forum Contributor
Posts: 130
Joined: Wed Jul 14, 2010 12:25 am

Re: Ajax autosuggest/autocomplete from database - problem

Post by Gargoyle »

well, if the PHP part is working, there's nothing more I can do for you. javascript is a pain in the butt and I try to keep it away from me if at all possible. maybe open a new thread for your javascript issue.
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Ajax autosuggest/autocomplete from database - problem

Post by defroster »

Ok, thanks Gargoyle you have help me loads. All the best!
Post Reply