Problem with prev/next page script and WHERE clause

[patheticsam]

Hi,

 

I have an issues that I can't seems to figure what i'm doing wrong, I'm mostly new to php and I have a script that splits the results from DB into different pages.

 

The script is working correctly but not if I had a WHERE clause in the SQL command..I really don't understand why....

 

here's the script :

 

<?php 


require_once('../admin/config.php');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}

$plan_type = $_POST['plan_type'];
$plan_style = $_POST['plan_style'];
$plan_bedroom = $_POST['plan_bedroom'];
$plan_bathroom = $_POST['plan_bathroom'];
$plan_garage = $_POST['plan_garage'];
$plan_superficie = $_POST['plan_superficie'];

$tbl_name="plans";		//your table name

$adjacents = 3;

$query = "SELECT COUNT(*) as num FROM $tbl_name WHERE plan_type='$plan_type'";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];


$targetpage = "index.php"; 	//your file name  (the name of this file)
$limit = 15; 								//how many items to show per page
$page = $_GET['page'];
if($page) 
	$start = ($page - 1) * $limit; 			//first item to display on this page
else
	$start = 0;								//if no page var is given, set start to 0


$sql = "SELECT * FROM $tbl_name LIMIT $start, $limit WHERE plan_type='$plan_type'";
$result = mysql_query($sql);


if ($page == 0) $page = 1;					//if no page var is given, default to 1.
$prev = $page - 1;							//previous page is page - 1
$next = $page + 1;							//next page is page + 1
$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1;						//last page minus 1


$pagination = "";
if($lastpage > 1)
{	
	$pagination .= "<div class=\"nextpage\">";
	//previous button
	if ($page > 1) 
		$pagination.= "<a href=\"$targetpage?page=$prev\">< Précedent</a>  ";
	else
		$pagination.= "<span class=\"disabled\">< Précendent  </span>";	

	//pages	
	if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
	{	
		for ($counter = 1; $counter <= $lastpage; $counter++)
		{
			if ($counter == $page)
				$pagination.= "  <span class=\"current\">$counter</span>  ";
			else
				$pagination.= "  <a href=\"$targetpage?page=$counter\">$counter</a>  ";					
		}
	}
	elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
	{
		//close to beginning; only hide later pages
		if($page < 1 + ($adjacents * 2))		
		{
			for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
			{
				if ($counter == $page)
					$pagination.= "  <span class=\"current\">$counter</span>  ";
				else
					$pagination.= "  <a href=\"$targetpage?page=$counter\">$counter</a>  ";					
			}
			$pagination.= "...";
			$pagination.= "  <a href=\"$targetpage?page=$lpm1\">$lpm1</a>  ";
			$pagination.= "  <a href=\"$targetpage?page=$lastpage\">$lastpage</a>  ";		
		}
		//in middle; hide some front and some back
		elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
		{
			$pagination.= "  <a href=\"$targetpage?page=1\">1</a>  ";
			$pagination.= "  <a href=\"$targetpage?page=2\">2</a>  ";
			$pagination.= "...";
			for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
			{
				if ($counter == $page)
					$pagination.= "  <span class=\"current\">$counter</span>  ";
				else
					$pagination.= "  <a href=\"$targetpage?page=$counter\">$counter</a>  ";					
			}
			$pagination.= "...";
			$pagination.= "  <a href=\"$targetpage?page=$lpm1\">$lpm1</a>  ";
			$pagination.= "  <a href=\"$targetpage?page=$lastpage\">$lastpage</a>  ";		
		}
		//close to end; only hide early pages
		else
		{
			$pagination.= "  <a href=\"$targetpage?page=1\">1</a>  ";
			$pagination.= "  <a href=\"$targetpage?page=2\">2</a>  ";
			$pagination.= "...";
			for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
					$pagination.= "  <span class=\"current\">$counter</span>  ";
				else
					$pagination.= "  <a href=\"$targetpage?page=$counter\">$counter</a>  ";					
			}
		}
	}

	//next button
	if ($page < $counter - 1) 
		$pagination.= "  <a href=\"$targetpage?page=$next\">Suivant ></a>";
	else
		$pagination.= "  <span class=\"disabled\">Suivant ></span>";
	$pagination.= "</div>\n";		
}
?>
<?php
	while($row = mysql_fetch_array($result))
	{
		echo	"
                            	<table class=\"plantable\">
								<tr>
									<td class=\"first\"><img src=\"../timthumb.php?src=admin/PHOTOS-PLANS/".$row['photo_main']."&h=140&w=150&zc=1\" alt=\"Plan de maison ".$row[plan_type]." ".$row['plan_style']."\"></td>
									<td class=\"second\">DL00-".$row['plan_id']."<br /><br /><b>".$row['plan_type']."</b><br />Style : ".$row['plan_style']."<br />Chambres à coucher : ";															
									if ($row['plan_bedroom']=='Plus de 5')
									 {
									 echo $row['plan_bedroom_sup'];
									 }
									else
									 {
									 echo $row['plan_bedroom'];
									 }
									echo 
									"<br />Salles de bain : ";
									if ($row['plan_bathroom']=='3 et plus')
									 {
									 echo $row['plan_bathroom_sup'];
									 }
									else
									 {
									 echo $row['plan_bathroom'];
									 }
									echo 																			
									"</td>
									<td class=\"third\"><a href=\"view_plan.php?cmd=view&id=".$row['plan_id']."\">Voir les détails du plan</a><br /><br /><br /><p class=\"price\">".$row['plan_price']." $ </p><br /><a href=\"order_plan.php?cmd=order&id=".$row['plan_id']."\"><img src=\"../images/bouton_commander.png\" alt=\"Commander un plan de maison\" border=\"0\"></a></td>
								</tr>
							</table>
							<br />
					";
			}
?>				
							<?=$pagination?>

 

Basically if I remove the "WHERE" clause highlighted in red

 

$sql = "SELECT * FROM $tbl_name LIMIT $start, $limit WHERE plan_type='$plan_type'";

 

the prev/next script works appart from the facts that it displays all the reseults from the DB and I need to display only the results =$plan_type

 

 

If anyone can help me it would be really appreciated..

 

Thanks!!

[PFMaBiSmAd]

See my replies in the following thread - http://forums.phpfreaks.com/index.php?topic=363172.0

[Christian F.]

The exact problem you have is explained in the MySQL manual. Pay particular attention to which order the arguments are listed in.