pagination problem

tbobker

PLease can you help me with this pagination problem. The limits have been set however the links are not working properly.

But if in the address bar you type the page numbers manualy the previous page link works but only gives the current page not the previous page?

her is some code.

<?php


$conn = mysql_connect("localhost","Username","Password");
mysql_select_db("Database",$conn);

$limit          = 10;
$query_count    = "SELECT count(*) FROM petition";
$result_count   = mysql_query($query_count,$conn);
$totalrows      = mysql_num_rows($result_count);

if(empty($page)){
    $page = 1;
}


$limitvalue = $page * $limit - ($limit);
$query  = "SELECT * FROM petition LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Error: " . mysql_error());

if(mysql_num_rows($result) == 0){
    echo("Nothing to Display!");
}

$bgcolor = "#E0E0E0"; // light gray

echo("<table>");

while($row = mysql_fetch_array($result)){



	$date = $row['date'];
	$fname=  $row['firstName'];
	$lname = $row['lastName'];
	$address = $row['address'];
	$email = $row['email'];
	$pcode = $row['pcode'];
	$country = $row['country'];
	$comment = $row['comments'];




    if ($bgcolor == "#E0E0E0"){
        $bgcolor = "#FFFFFF";
    }else{
        $bgcolor = "#E0E0E0";
    }

echo("<tr bgcolor=".$bgcolor."><td>");
echo($date);
echo("</td><td>");
echo($fname);
echo("</td><td>");
echo($lname);
echo("</td><td>");
echo($address);
echo("</td><td>");
echo($email);
echo("</td><td>");
echo($pcode);
echo("</td><td>");
echo($country);
echo("</td><td>");
echo($comment);
echo("</td></tr>");
}

echo("</table>");

if($page != 1){
    $pageprev = $page--;

    echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a> ");
}else{
    echo("PREV".$limit." ");
}

$numofpages = $totalrows / $limit;

for($i = 1; $i <= $numofpages; $i++){
    if($i == $page){
        echo($i." ");
    }else{
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
    }
}


if(($totalrows % $limit) != 0){
    if($i == $page){
        echo($i." ");
    }else{
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
    }
}

if(($totalrows - ($limit * $page)) > 0){
    $pagenext = $page++;

    echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>");
}else{
    echo("NEXT".$limit);
}

mysql_free_result($result);

?>

Piranha

$page have not been set prior to

    if(empty($page)){
        $page = 1;
    }

. This will make

    $limitvalue = $page * $limit - ($limit);

to always return the same result (0).

Tezread

here is a working pagination function that i found on this site and adapted for my project. Hope this helps. You can see it in action at

http://www.mindseyemidlands.co.uk/notts_quality/info_resource/download_casestudies.php

 <?php 
include("connect.php"); 

$limit          = 4;                 
$query_count    = "SELECT filename , file_dir, keywords, description FROM downloads where category = 'Case study'";      
$result_count   = mysql_query($query_count);      
$totalrows      = mysql_num_rows($result_count);   

$PHP_SELF    =    $_SERVER['PHP_SELF']; 

if(!isset($_GET['page'])){ // Checks if the $page variable is empty (not set) 
    $page = 1;     // If it is empty, we're on page 1 
} else{ 

    $page = $_GET['page']; 
} 



$limitvalue = $page * $limit - ($limit);   
// Ex: (page2 * 5(items per page) = 10) - 5 = 5 <- data starts at 5 

$query  = "SELECT filename , file_dir, keywords, description FROM downloads where category = 'Case study' LIMIT $limitvalue, $limit";          
$result = mysql_query($query) or die("Error: " . mysql_error());   

if(mysql_num_rows($result) == 0){ 
    echo("Nothing to Display!"); 
} 

$bgcolor = "#E0E0E0"; // light gray 

echo("<table>"); 

while($row = mysql_fetch_array($result)){ 
    if ($bgcolor == "#E0E0E0"){ 
        $bgcolor = "#FFFFFF"; 
    }else{ 
        $bgcolor = "#E0E0E0"; 
    } 

echo("<tr bgcolor=".$bgcolor."><td>"); 
echo '<a href="' . $row['file_dir'] . '">' . $row['filename'] . '</a>';
   // echo($row["filename"]); 
    echo("</td><td>"); 
    echo($row["description"]); 
    echo("</td></tr>"); 
    } //closing while loop 

echo("</table>"); 

if($page != 1){   
    $pageprev = $page - 1; //decrementing $page-- does not work on all php configurations. 


    echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV".$limit."</a> ");   
}else{ 
    echo("PREV".$limit." "); 
} 

$numofpages = $totalrows / $limit;   


#echo "<br>", $totalrows; 
   #exit; 

for($i = 1; $i <= $numofpages; $i++){ 
    /* This for loop will add 1 to $i at the end of each pass until $i is greater   
    than $numofpages. */ 
    if($i == $page){ 
        echo($i." "); 
    }else{ 
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
    } 
} //code above has been decoded lozza 


if(($totalrows % $limit) != 0){ 
    if($i == $page){ 
        echo($i." "); 
    }else{ 
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
    } 
} 

if(($totalrows - ($limit * $page)) > 0){ 
    $pagenext = $page + 1; //incrementin $page like $page++ does not work in all enviroments 

    echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>");   
}else{ 
    echo("NEXT".$limit);   
} 

mysql_free_result($result); 
?>

tbobker

Thanks that script works. You have relieved my fustration.!

Tezread

no problem. I cannot take the credit though as not so long ago i was in thre same situation as you until i did a search for 'pagination'. it is a common problem. 🙂

Piranha

Aviod coding like this:

$query_count    = "SELECT filename , file_dir, keywords, description FROM downloads where category = 'Case study'";

It will return all rows to PHP, and that may take lots of time and resources. Instead do what you did to start with, user SELECT count, then the database will only return one row. Of course then you need to read the result instead of just count the rows returned.