Page 2 of 2

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Mon Aug 23, 2010 4:19 am
by Gargoyle
use explode() instead of split()

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Mon Aug 23, 2010 4:25 am
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;	
	

	
?>

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Mon Aug 23, 2010 5:15 am
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

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Mon Aug 23, 2010 5:45 am
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.

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Mon Aug 23, 2010 9:06 am
by Gargoyle
oh come on.

Code: Select all

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

Code: Select all

$_POST['search'] = 'yolanda';
for example

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Mon Aug 23, 2010 2:09 pm
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;	
	

	
?>

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Mon Aug 23, 2010 9:03 pm
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 );

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Tue Aug 24, 2010 4:30 am
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;	
	

	
?>

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Wed Aug 25, 2010 2:56 pm
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.

Re: Ajax autosuggest/autocomplete from database - problem

Posted: Wed Aug 25, 2010 5:27 pm
by defroster
Ok, thanks Gargoyle you have help me loads. All the best!