Hello everyone,

I'm trying to add pagination to my ecommerce website, but I'm getting an error when I click on the Next link. I'm sure this will be an easy fix for someone with more coding experience than me. I'm at a loss as to why the first page works, but not the second.

The page that gives an error:

Hats

Here is my code:

// Connect to the MySQL database
include "storescripts/connect_to_mysql.php";
$sql = mysql_query("SELECT * FROM products WHERE subcategory='$subcategory'");

// 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;
}

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

// 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
// This section is explained well in the video that accompanies this script
$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="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
} else if ($pn == $lastPage) {
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a> &nbsp;';
} else if ($pn > 1 && $pn < $lastPage) {
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?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 WHERE subcategory='$subcategory' $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="' . $_SERVER['PHP_SELF'] . '?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="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
}
}
// END Adam's Pagination Display Setup

$dynamicList = "";
$productCount = mysql_num_rows($sql); // count the output amount
if ($productCount > 0) {
// get all the product details
while($row = mysql_fetch_array($sql2)){
$id = $row["id"];
$product_name = $row["product_name"];
$price = $row["price"];
$details = $row["details"];
$category = $row["category"];
$subcategory = $row["subcategory"];
$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
$dynamicList .= '<table style="float: left;" width="50%" border="0" cellspacing="0" cellpadding="6">
<tr>
<td width="17%" valign="top"><a href="product.php?id=' . $id . '"><img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="77" height="102" border="1" /></a></td>
<td width="83%" valign="top">' . $product_name . '<br />
R' . $price . '<br />
<a href="product.php?id=' . $id . '">View Product Details</a></td>
</tr>
</table>';
}
}
} else {
$dynamicList = "We have no products listed in our store yet";
}
?>

These are my divs:

<div class="box3">
<h3><?php echo $category; ?> | <?php echo $subcategory; ?></h3>
<br />
<?php echo $dynamicList; ?><br />
</div>

<div class="box2">
<?php echo $paginationDisplay; ?><br />
<br />
<h3>Total Items: <?php echo $nr; ?></h3>
</div>