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> </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> ":
"<<Prev "; ?>
[Page <?php echo $pageNum; ?>]
<?=$next = ($pageNum < $NumPgs) ? "<a href=\\"?pageNum=".($pageNum+1)."\\"> Next>></a>":
" Next>>";?>
</p>
<p> <?=$offset+1?> to <?=$offset+$rows_per_page?>, of <?=$listings_total?> products</p>
system
October 2, 2010, 11:33pm
2
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 % $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&type=firstPg" title="Click to view first page">First</a></li>' .
'<li id="liPrevPage"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=-1&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 . '&type=pageLink" title="Go to page ' . $i . '">' . $i . '</a></li>';
}
$str = $str . '<li id="liNextPage"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=1&type=prevNext" title="Click to view next page">Next</a></li>' .
'<li id="liLastPage"><a href="' . $_SERVER['PHP_SELF'] . '?txtPgNum=-1&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);
?>
oddz
October 3, 2010, 12:51am
3
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.