Paging problem

Hi Folks,

I’m having trouble with my results paging.

I can’t get the next button to take me to the next page. I can;t get the total to output the proper total. Can anyone please help?

Many thanks



      // ROWS DISPLAYED PER PAGE

      $rows_per_page = 10;

      // GET PAGE NUMBER

      $pageNum = 1;

      $offset = (!empty($pageNum)) ? $pageNum : $pageNum = 0;

      // URL CLEAN UP

      $self = $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];

      $self = str_replace("page={$offset}", "", $self);



      // GET QUERY RESULTS

      $offset = ($pageNum) ? ($pageNum - 1) * $rows_per_page : 0;

      $query = "SELECT * FROM products INNER JOIN productCat ON products.productCat = productCat.catID  LIMIT {$offset},{$rows_per_page}";

echo   $query;

      $result = mysql_query($query);



      // GET NUMBER OF PAGES

      $ltq = mysql_query($query);

      $listings_total = mysql_num_rows($ltq);

      $NumPgs = ceil($listings_total / $rows_per_page);

      ?>
<h2>Monster Metals</h2>
     

 <table width="100%">
  <tr class="header">
     
      <td>Name</td>   <td>Description</td><td>Category</td><td>&nbsp;</td>
      </tr>
      <?php while($row = mysql_fetch_array($result)){ ?>

     <tr >
     
      <td>   <a href="product.php?productid=<?=$row['productID']?>">  <?=$row['productName']?></a></td>
         <td>   <?=$row['productDescription']?></td>
         
          <td>   <?=$row['CatName']?></td><td>
      </td>
      </tr>
      
      
      <?php } ?>

</table>
<p>
      <?=$prev = ($NumPgs > 0 && $pageNum!=1) ? "<a href=\\"?pageNum=".($pageNum-1)."\\"><<Prev</a>&nbsp;&nbsp;":

      "<<Prev&nbsp;&nbsp;"; ?>

      [Page <?php echo $pageNum; ?>]

      <?=$next = ($pageNum < $NumPgs) ? "<a href=\\"?pageNum=".($pageNum+1)."\\">&nbsp;&nbsp;Next>></a>":

      "&nbsp;&nbsp;Next>>";?>&nbsp;
</p>


      <p>&nbsp;<?=$offset+1?> to <?=$offset+$rows_per_page?>, of <?=$listings_total?> products</p>

I use this class for paginating database query results

 
<?php
 
class paginator {
 
    // Properties
    protected $linesPerPage;    //number of lines to print per page
    protected $numLinksDisplay;    //number of page links to display at a time
    protected $query;
    protected $currPage;
    protected $totRows;
    protected $totPages;
    protected $offset;
 
    /*     * *********************************************************************
      Class Constructor
     * ********************************************************************* */
 
    public function __construct($numLines, $numLinks, $query, $conn) {
        $this->linesPerPage = ceil($numLines);
        $this->numLinksDisplay = ceil($numLinks);
        $this->query = $query;
        $this->currPage = 1;
        $this->offset = 0;
        $this->initialise($conn);
    }
 
    /*     * *********************************************************************
      Class Accessor Methods
     * ********************************************************************* */
 
    public function setQuery($query,$conn) {
        $this->query = $query;
        $this->initialise($conn);
    }
 
    //-------------------------------------------------------------------------
    public function setCurrPage($type, $pageNum) {
        $pageNum = ceil($pageNum);
        //check which link was clicked
        switch ($type) {
            case 'pageLink':
                $this->currPage = $pageNum;
                break;
            case 'prevNext':
                $this->currPage = $this->currPage + $pageNum;
                if ($this->currPage < 1) {
                    $this->currPage = 1;
                }
                if ($this->currPage > $this->totPages) {
                    $this->currPage = $this->totPages;
                }
                break;
            case 'firstPg':
                $this->currPage = 1;
                break;
            case 'lastPg':
                $this->currPage = $this->totPages;
                break;
        }
        //calculate the offset of the first record number to retrieve from the DB for this page
        $this->offset = ($this->currPage * $this->linesPerPage) - $this->linesPerPage;
        if ($this->offset < 0) {
            $this->offset = 0;
        }
        if ($this->offset > $this->totRows) {
            $this->offset = $this->totRows;
        }
    }
 
    //-----------------------------------------------
    public function getCurrPage() {
        return $this->currPage;
    }
 
    /*     * *********************************************************************
      Class Methods
     * ********************************************************************* */
 
    private function initialise($conn) {
        //count all the records to work out max number of rows and number of pages needed
        $rs = @mysql_query($this->query, $conn) or die("<p>3-Server is busy.<br />Please try again later.</p>");
        $this->totRows = mysql_num_rows($rs); //total number of rows to display
        @mysql_free_result($rs);
        if ($this->totRows &#37; $this->linesPerPage == 0) {
            $this->totPages = $this->totRows / $this->linesPerPage;
        } else {
            $this->totPages = round(($this->totRows / $this->linesPerPage) + 0.5);  //total number of pages required
        }
        $this->numLinksDisplay = ($this->numLinksDisplay > $this->totPages) ? $this->totPages : $this->numLinksDisplay;
        //echo $this->totRows.'<br />'.$this->totPages.'<br />'.$this->numLinksDisplay;  die();
    }
 
//-------------------------------------------------------------------------------------
    public function getPageRecords($conn) {
        $query = $this->query . ' limit ' . $this->offset . ',' . $this->linesPerPage;
        $rs = @mysql_query($query, $conn);
        if (!$rs) {
            return false;
        } else {
            return $rs;
        }
    }
 
//-------------------------------------------------------------------------------------
    public function showLinks() {
        if ($this->totPages <= 1)
            return;  //no need to display any links
 $str = '<div id="page_links_wrap">' .
                '<div id="page_links_summ">' .
                '<p id="totPages">Total pages: ' . $this->totPages . '</p>' .
                '<p id="totRows">Total records: ' . $this->totRows . '</p>' .
                '</div>' .
                '<div id="page-links-container">' .
                '<ul id="page-links">' .
                '<li id="liFirstPage"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=-1&amp;type=firstPg" title="Click to view first page">First</a></li>' .
                '<li id="liPrevPage"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=-1&amp;type=prevNext" title="Click to view previous page">Previous</a></li>';
        //calculate the min and max link numbers to display for this page
        if ($this->numLinksDisplay % 2 == 0) { //even number of links to display
            $limit1 = $this->currPage - $this->numLinksDisplay / 2;
            $limit2 = $this->currPage + ($this->numLinksDisplay / 2) - 1;
        } else { //odd number of links to display
            $limit1 = $this->currPage - ($this->numLinksDisplay - 1) / 2;
            $limit2 = $this->currPage + ($this->numLinksDisplay - 1) / 2;
        }
        if ($limit1 < 1 && $this->currPage < $this->numLinksDisplay)
            $limit1 = 1;
        if ($limit2 > $this->totPages)
            $limit2 = $this->totPages;
        //adjust the link numbers for when we are within $_SESSION['numLinksDisplay']/2 of either end
        if ($this->currPage <= $this->numLinksDisplay / 2)
            $limit2 = $this->numLinksDisplay;
        if ($this->currPage > $this->totPages - $this->numLinksDisplay / 2)
            $limit1 = $this->totPages - $this->numLinksDisplay + 1;
        //echo '<br />'.$_SESSION['currPage'].'<br />'.$limit1.'<br />'.$limit2.'<br /><br />';
        //display the page links
        for ($i = $limit1; $i <= $limit2; $i = $i + 1) {
            $str = $str . '<li id="liPg' . $i . '"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=' . $i . '&amp;type=pageLink" title="Go to page ' . $i . '">' . $i . '</a></li>';
        }
        $str = $str . '<li id="liNextPage"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=1&amp;type=prevNext" title="Click to view next page">Next</a></li>' .
                '<li id="liLastPage"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=-1&amp;type=lastPg" title="Click to view last page">Last</a></li>' .
                '</ul>' .
                '</div>' .
                '</div>';
        return $str;
    }
 
//-------------------------------------------------------------------------------------
    public function getLinkStyles() {
        if ($this->totPages <= 1)
            return;  //no need to display any links
 $str = '<style type="text/css"> ' .
                '#page_links_wrap {' .
                //'border: 1px solid green; ' .
                'margin: 5px auto 0px auto; ' .
                'padding: 0px 0px 0px 0px; ' .
                'width: 250px}' .
                '#page-links-container { ' .
                'font-size: 8pt; ' .
                'font-family: tahoma, arial, sans serif; ' .
                'margin: 0px 0px 0px 0px; ' .
                'padding: 0px 0px 0px 0px} ' .
                '#page-links-container ul { ' .
                'clear: both; ' .
                'padding: 0px 0px 0px 0px; ' .
                'margin: 0px 0px 10px 0px;' .
                'list-style-type: none} ' .
                '#page-links-container ul li { ' .
                'display: inline; ' .
                'color: rgb(0,0,205); ' .
                'padding: 3px 4px 3px 4px; ' .
                'margin: 0px 0px 0px 6px} ' .
                '#page-links-container ul li a { ' .
                'text-decoration: none; ' .
                'font-size: 8pt} ' .
                '#page-links-container ul li a:hover { ' .
                'text-decoration: underline} ' .
                '#page-links-container ul li a:visited { ' .
                'color: rgb(0,0,205);} ' .
                '#page_links_summ { ' .
                'font-size: 8pt; ' .
                'font-family: tahoma, arial, sans serif; ' .
                'overflow: hidden} ' .
                '#totPages { ' .
                'margin: 5px 0px 5px 10px; padding: 0px 0px 0px 0px;' .
                'float: left} ' .
                '#totRows { ' .
                'margin: 5px 10px 5px 0px; padding: 0px 0px 0px 0px;' .
                'float: right} ' .
                '</style> ';
        return $str;
    }
 
//--------------------------------------------------------------------------------------
    public function applyLinkStyles() {
        if ($this->totPages <= 1)
            return;  //no need to display any links
            //hide or display the 'previous' and 'next' buttons as required
 if ($this->totPages == 1)
            echo '<script type="text/javascript">document.getElementById("page-links-container").style.display="none";</script>';
        if ($this->currPage == 1) {
            echo '<script type="text/javascript">document.getElementById("liPrevPage").disabled = true;</script>';
        } else {
            echo '<script type="text/javascript">document.getElementById("liPrevPage").disabled = false;</script>';
        }
        if ($this->currPage == $this->totPages) {
            echo '<script type="text/javascript">document.getElementById("liNextPage").disabled = true;</script>';
        } else {
            echo '<script type="text/javascript">document.getElementById("liNextPage").disabled = false;</script>';
        }
        //highlight the current page's page link
        echo '<script type="text/javascript">document.getElementById("liPg' . $this->currPage . '").style.backgroundColor="rgb(200,200,200)";</script>';
        echo '<script type="text/javascript">document.getElementById("liPg' . $this->currPage . '").style.border="1px solid rgb(0,0,0)";</script>';
    }
 
//--------------------------------------------------------------------------------------
}
//end of class
 
//general order of steps to set up your pagination
 
$query = 'select * from tblMyTable';
 
$conn = dbConnectMysql() or die("<p>3625-Server is busy.<br />Cannot display products at the moment.<br />Please try again later.</p>");
if (!isset($_SESSION['pageMaker']) ) {
    $linesPerPage = 3;   //number of lines to print per page
    $numLinksDisplay = 5;    //number of page links to display at a time
    $pageMaker = new paginator($linesPerPage, $numLinksDisplay, $query, $conn);
} else {
    $pageMaker = unserialize($_SESSION['pageMaker']);
}
 
//set current page
if (isset($_GET['type'])) {
    $pageMaker->setCurrPage($_GET['type'], $_GET['txtPgNum']);
}
 
//get the records to display on this page
if (!$rs = $pageMaker->getPageRecords($conn)) {
    die('<p>**ERROR - cannot get records for the page at the moment.</p>');
}
 
echo $pageMaker->getLinkStyles();
 
/*
  Code to display the records for this page
*/
 
echo $pageMaker->showLinks();
 
//hide or display the 'previous' and 'next' buttons as required
                            $pageMaker->applyLinkStyles();
 
//serialise the session's pageMaker object for the next call to this page
                            $_SESSION['pageMaker'] = serialize($pageMaker);
 
?>

The first issue is that the page number is never changing identified by the below line.


// GET PAGE NUMBER
$pageNum = 1;

Instead that line should be something like the below.


// GET PAGE NUMBER
$pageNum = isset($_GET['pageNum']) && is_numeric($_GET['pageNum'])?$_GET['pageNum']:1;

The second problem is that you are never fetching the total number of actual results. To do that you will need to add SQL_CALC_FOUND_ROWS to the beginning of your query as shown below.


$query = "SELECT SQL_CALC_FOUND_ROWS * FROM products INNER JOIN productCat ON products.productCat = productCat.catID  LIMIT {$offset},{$rows_per_page}";

Lastly, you will now need to replace the below line code code with the actual total rather than the total number of rows in the result set (big difference).


$listings_total = mysql_num_rows($result);

The above code will return the number of rows in the result set but not the total. In order to get the actual total a separate query will be needed as shown below.


$total_result = mysql_query('SELECT FOUND_ROWS() total;');
$total_row = mysql_fetch_assoc($total_result);
$listings_total = total_row['total']; // the actual total

Having changed the above things your code should now change the pages correctly.