PHP Pagination problem... please help

[susanv]

Hello everyone,

 

I have an ecommerce website and I'm trying to add pagination, but I'm stuck.  I'm sure this will be an easy fix for someone with more experience than me. The first page works fine, but I get an error when I click on Next. I'm at a loss as to why.  Can someone please help...?

 

The page that gives an error:

 

http://www.hopefloatsdesign.co.za/shopping/list.php?category=clothing&subcategory=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 .= '  <span class="pagNumActive">' . $pn . '</span>  ';

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';

} else if ($pn == $lastPage) {

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';

    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';

} else if ($pn > 2 && $pn < ($lastPage - 1)) {

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a>  ';

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';

    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a>  ';

} else if ($pn > 1 && $pn < $lastPage) {

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';

    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';

    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';

}

// 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. '        ';

// If we are not on page 1 we can place the Back button

    if ($pn != 1) {

        $previous = $pn - 1;

        $paginationDisplay .=  '   <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 .=  '   <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>

 

 

[Muddy_Funster]

[QuickOldCar]

If you visit this link which shows the correct pagination results, you will see that you didn't include the GET parameters for category and subcategory in your links.

 

http://www.hopefloatsdesign.co.za/shopping/list.php?category=clothing&subcategory=hats&pn=2

[fugix]

please encase your code in code tags

[QuickOldCar]

Besides the tutorial here at phpfreaks on pagination.

http://www.phpfreaks.com/tutorial/basic-pagination

 

You may also look at my 2 demo examples of how I do my pagination.

The codes are at the link locations.

 

http://get.blogdns.com/paginate/

simple navigation of first,previous,show current,next,last

any amount of results per page can be set

pulls the current url along with any queries

trims the page number and replaces it

 

http://get.blogdns.com/dynaindex/paginate.php

deluxe navigation

hard set to 10 results per page

pulls the current url along with any queries

trims the page number and replaces it

capable of jumping ahead groups of pages or setting a desired page and go directly there

I use this code at my dynaindex doing multiple mysql select statements and optional GET parameters

 

I have the mysql commented out so I can show the demo, uncomment and use your own values, comment out the example $total_posts variable.

 

 

As for your script, I don't really like the way it works. As you have to manually insert the GET values which may change or add more at a later date, then if there is a GET value, the ? must be moved to before those values and ?pn=2 becomes &pn=2. (although you can check the GET values with isset and assign them where they need to go, or if empty have the variable empty)

 

I think it would be wiser to just fetch the entire url along with the queries, and trim the page number...then insert your new desired page number.