[Resolved] search results paginating problem

[jadeddog]

hey all... after much searchign around for an example of how to paginate search results, i have pieced together the following code.... there is a seach page that contains a form, this form passes 'criteria' to the following pages code..... this page should be showing the results at 10 results per page... and then give links at the bottom of the page to go to the next pages results..... my problem is that is shows the results correctly, and it will show the "next" link is there are over 10 results, but it does not "cut off" the results at 10, it will just show all the results (ie. if there are 15 matches for the search criteria, this page will show all 15)..... can anybody see my problem? thx in advance

<? 
include("other.inc");
$connection = mysql_connect($host,$user,$password)or die ("couldn’t connect to server");
$db = mysql_select_db($database,$connection)or die ("Couldn’t select database");

//check if this is the first page 
if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) 
{ 
	//we give the value of the first row to 0 
	$startrow = 0; 
//otherwise we take the value from the URL 
} 
else 
{ 
	$startrow = (int)$_GET['startrow']; 
} 

//query details table begins
if (isset($_GET['criteria'])) 
{
	$criteria = $_GET['criteria'];
}
else 
{
	$criteria = $_POST['criteria'];
}

if ("{$_POST['criteria']}" == "") 
{
	echo "There were no matches found for your search.";
	echo "<form name='search' action='show_search.php' method='post'>";
		echo "Enter your search criteria:<br>";
		echo "<input type='text' name='criteria'><br>";
		echo "<input type='submit' name='search' value='Search'>";
	echo "</form>";
	exit;
}
else
{
	$userinput = explode(" ","{$_POST['criteria']}");
	$query = "SELECT * FROM houses where houseID like '%%'";

$arraycount = count($userinput);
for ($i = 0; $i < $arraycount; $i++) 
	{ 
		$query .= " and name like '%$userinput[$i]%' 
		or address like '%$userinput[$i]%'
		or city like '%$userinput[$i]%' 
		or province like '%$userinput[$i]%' 
		or postalCode like '%$userinput[$i]%' 
		or phoneNum like '%$userinput[$i]%' 
		or altPhoneNum like '%$userinput[$i]%' 
		or description like '%$userinput[$i]%' 
		or grouping like '%$userinput[$i]%'
		or latitude like '%$userinput[$i]%'
		or longitude like '%$userinput[$i]%'
		LIMIT $startrow, 25"; 
	}

//below is the table layout for results 
$result = mysql_query($query) or die (mysql_error($connection));
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) 
{
	extract($row);
	echo "$name<br>";
}
}

$numrows=mysql_num_rows($result); 
echo "$numrows";

//now these are the links
if ($numrows>10)
{ 
	echo '<a href="search_test.php?startrow='.($startrow+10).'&criteria='.$criteria.'">Next</a>';  

} 
if ($numrows>10)
{ 
	echo '<a href="search_test.php?startrow='.($startrow-10).'&criteria='.$criteria.'">Back</a>';  

} 

?>

[yelvington]

The only LIMIT clause in your code is clearly hardcoded at 25, not 10.

[jadeddog]

yeah i caught that shortly after posting, its now at 10, but its still not working... now it only shows the first 10 records and does not display a link anywhere, even though the $numrows>10... so the if statement isnt working (which i wouldnt think) or its something else

i would post the corrected code, but all i did is change the 25 to a 10

can anybody help with this?

[jadeddog]

well i figured it out, kinda... it will work when i only search with one variable (ex. if i only type in "montreal" for my search variable), when searching for this one variable, the search performs as it should and paginates as it should.... the problem arises when i search for more than one term (ex. if i search for "new york")... i then get an error as follows:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'and name like '%york%' or address like '%york%' or

so whats happening is that on the second loop (searching for matches to the second variable) something has gone wrong.... i have this exact same script on another page that does not paginate and i can search for multiple terms just fine, so i dont think its the query that is faulty... i think it has something to do with the setting of the $criteria variable after the "if (isset($_GET['criteria']))" line of code, but im really not sure

i really dont know what is going on here... any help would be WONDERFUL... here is my updated code, please let me know if you notice anything that should be changed

<? 
include("other.inc");
$connection = mysql_connect($host,$user,$password)or die ("couldn’t connect to server");
$db = mysql_select_db($database,$connection)or die ("Couldn’t select database");

//check if this is the first page 
if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) 
{ 
	//we give the value of the first row to 0 
	$startrow = 0; 
//otherwise we take the value from the URL 
} 
else 
{ 
	$startrow = (int)$_GET['startrow']; 
} 

//query details table begins
	if (isset($_GET['criteria'])) 
	{
		$criteria = $_GET['criteria'];
	}
	else 
	{
		$criteria = $_POST['criteria'];
	}

if ($criteria == "") 
{
	echo "There were no matches found for your search.";
	echo "<form name='search' action='show_search.php' method='post'>";
		echo "Enter your search criteria:<br>";
		echo "<input type='text' name='criteria'><br>";
		echo "<input type='submit' name='search' value='Search'>";
	echo "</form>";
	exit;
}
else
{
	$userinput = explode(" ","$criteria");
	$query = "SELECT * FROM houses where houseID like '%%'";

$arraycount = count($userinput);
for ($i = 0; $i < $arraycount; $i++) 
	{ 
		$query .= " and name like '%$userinput[$i]%' 
		or address like '%$userinput[$i]%'
		or city like '%$userinput[$i]%' 
		or province like '%$userinput[$i]%' 
		or postalCode like '%$userinput[$i]%' 
		or phoneNum like '%$userinput[$i]%' 
		or altPhoneNum like '%$userinput[$i]%' 
		or description like '%$userinput[$i]%' 
		or grouping like '%$userinput[$i]%'
		or latitude like '%$userinput[$i]%'
		or longitude like '%$userinput[$i]%'
		LIMIT $startrow, 10"; 
	}

//below is the table layout for results 
$result = mysql_query($query) or die (mysql_error($connection));
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) 
{
	extract($row);
	echo "$name<br>";
}
}

$numrows=mysql_num_rows($result); 

//now these are the links
if ($numrows>=10)
{ 
	echo '<a href="search_test.php?startrow='.($startrow+10).'&criteria='.$criteria.'">Next</a>';  

} 
if (($startrow>=10))
{ 
	echo '<a href="search_test.php?startrow='.($startrow-10).'&criteria='.$criteria.'">Back</a>';  

} 

?>

[jadeddog]

bump still looking for help on this one