Trouble with pagination

PHP
1

Hello everyone,

I have added pagination to my script but have run into a problem. Instead of showing just the 3 products I want displayed, all the records are displayed on page 1. I have 9 records so far and therefore I should have 3 pages with 3 items showing on each page. Instead I get 1 page with all 9 records.

I’ve gone over my code numerous times, but I just can’t find the problem. Can anybody help by looking at the code? Any help would be much appreciated.


<?php
// This block grabs the whole list for viewing
	$product_list = "";
	$sql = mysql_query("SELECT * FROM products ORDER BY category DESC");
	
	// Adam's Pagination Logic
	$nr = mysql_num_rows($sql); // Get total of Num rows from the database query
	if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers
	} else { // If the pn URL variable is not present force it to be value of page number 1
    $pn = 1;
	} 

	unset($_GET['pn']);
	$MyURL = $_SERVER['PHP_SELF']."?".http_build_query($_GET);

	//This is where we set how many database items to show on each page 
	$itemsPerPage = 3; 

	// Get the value of the last page in the pagination result set
	$lastPage = ceil($nr / $itemsPerPage);

	// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
	if ($pn < 1) { // If it is less than 1
    $pn = 1; // force if to be 1
	} else if ($pn > $lastPage) { // if it is greater than $lastpage
    $pn = $lastPage; // force it to be $lastpage's value
	} 

	// This creates the numbers to click in between the next and back buttons
	$centerPages = "";
	$sub1 = $pn - 1;
	$sub2 = $pn - 2;
	$add1 = $pn + 1;
	$add2 = $pn + 2;
	if ($pn == 1) {
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
	} else if ($pn == $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
	} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub2 . '">' . $sub2 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add2 . '">' . $add2 . '</a> &nbsp;';
	} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
	}

	// This line sets the "LIMIT" range
	$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 

	// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax

	// $sql2 is what we will use to fuel our while loop statement below
	$sql2 = mysql_query("SELECT * FROM products ORDER BY category DESC $limit"); 

	// END Adam's Pagination Logic

	// Adam's Pagination Display Setup
	$paginationDisplay = ""; // Initialize the pagination output variable

	// This code runs only if the last page variable is not equal to 1 
	if ($lastPage != "1"){

	// This shows the user what page they are on, and the total number of pages
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '&nbsp;  &nbsp;  &nbsp; ';

	// If we are not on page 1 we can place the Back button
    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $MyURL.'&pn=' . $previous . '"> Back</a> ';
    } 

	// Lay in the clickable numbers display here between the Back and Next links
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';

	// If we are not on the very last page we can place the Next button
    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $MyURL.'&pn=' . $nextPage . '"> Next</a> ';
    } 
	}

	// END Adam's Pagination Display Setup
	
	$productCount = mysql_num_rows($sql); // count the output amount
	if ($productCount > 0) {
	while($row = mysql_fetch_array($sql)){ 
             $id = $row["id"];
			 $category = $row["category"];
			 $subcategory = $row["subcategory"];
			 $product_name = $row["product_name"];
			 $price = $row["price"];
			 $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
			 $product_list .= '<table style="float: left;" width="100%" border="0" cellspacing="0" cellpadding="0">
			<tr>
			<td width="20%" valign="top">' . $category . '</td>
			<td width="20%" valign="top">' . $subcategory . '</td>
			<td width="15%" valign="top">' . $price . '</td>
			<td width="25%" valign="top">' . $product_name . '</td>
			<td width="20%" valign="top"><a href="inventory_edit.php?pid=' . $id . '">edit</a> &bull; <a href="inventory_list.php?deleteid=' . $id .'">delete</a></td>
	        </tr>
    		</table>';
		    }
			} else {
			$product_list = "You have no products listed in your store yet";
			}
?>
2

You need to loop through $sql2, not $sql ($sql contains all of the products, $sql2 contains the subset for the page you are on)

    $productCount = mysql_num_rows($sql2); // count the output amount
    if ($productCount > 0) {
    while($row = mysql_fetch_array($sql2)){
3

cpradio… thank you SO much. I’ve sat with this code now for two days and here you manage to fix it within a couple of minutes. You truly are a PHP guru! :slight_smile:

4

Imagine that you had ten thousand products and you brought them all into memory just to list four or five on a page. Not very efficient. :frowning:

Have MySQL count them:

$sql   = "SELECT COUNT(*)
            FROM products";
$result = mysql_query($sql);
$count = mysql_result($result, 0);

Then bring in just the ones needed for the page using LIMIT

$start = $pn * $ppp - $ppp; 
$sql = "SELECT *
          FROM products
         LIMIT $start, $ppp";
$result = mysql_query($sql) or die(mysql_error());

The complete code:

$sql   = "SELECT COUNT(*)
            FROM products";
$result = mysql_query($sql);
$count = mysql_result($result, 0);

$ppp = 3;

if (!isset($_GET['pn'])) { 
    // if no pn, set to 1
    $pn = 1;
} else { 
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']);
    if($pn * $ppp > $count) {
        // if pn larger than last page, set to last page
        $pn = ceil($count / $ppp);
    } elseif ($pn < 1) {
        // if pn less than 1, set to 1
        $pn = 1;
    }
} 

$start = $pn * $ppp - $ppp; 
$sql = "SELECT *
          FROM products
         LIMIT $start, $ppp";
$result = mysql_query($sql) or die(mysql_error());