pagination returns more pages than results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sectionLeader123
Forum Commoner
Posts: 31
Joined: Fri Oct 11, 2013 8:46 am

pagination returns more pages than results

Post by sectionLeader123 »

I have the following query with pagination applied and it works great but I have noticed that when I execute a query, lets say there are four sets of results that should be returned so I should get 4 pages back but I get 36 pages, is there something I have missed or done wrong, anyways here is my code thanks.

Code: Select all

<?php
if(isset($_GET['sCriteria']))
{
	error_reporting(E_ALL);
	include 'connect.php';
	$criteria = $_GET['sCriteria'];
	$sql = "SELECT COUNT(*) FROM Customers, Company";
	$result=mysql_query($sql) or die(mysql_error());
	$result2=mysql_fetch_row($result);
	$numrows = $result2[0];
	$rowsperpage = 1;
	$totalpages = ceil($numrows / $rowsperpage);
	if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
	   // cast var as int
	   $currentpage = (int) $_GET['currentpage'];
	} else {
	   // default page num
	   $currentpage = 1;
	} // end if

	if ($currentpage > $totalpages) {
	   // set current page to last page
	   $currentpage = $totalpages;
	} // end if
	// if current page is less than first page...
	if ($currentpage < 1) {
	   // set current page to first page
	   $currentpage = 1;
	} // end if

	$offset = ($currentpage - 1) * $rowsperpage;

	$sql="	SELECT Customers.CUST_ID, Customers.CUST_Forename, Customers.CUST_Surname, 
	Customers.CUST_Email, Customers.CUST_Mobile, Customers.CUST_HomeNum, 
	Customers.CUST_AddressL1, Customers.CUST_AddressL2, Customers.CUST_AddressL3,
	Customers.CUST_Postcode, Company.COMP_ID, Company.COMP_Name, Company.COMP_Email, Company.COMP_PrimaryNum,
	Company.COMP_SecondaryNum, Company.COMP_AddressL1, Company.COMP_AddressL2, Company.COMP_AddressL3,
	Company.COMP_Postcode
	FROM Customers, Company
	WHERE
	(Customers.CUST_Forename LIKE '%$criteria%' OR Customers.CUST_Surname LIKE '%$criteria%'
	OR Company.COMP_Name LIKE '%$criteria%')
	LIMIT $offset, $rowsperpage
	";

	$result=mysql_query($sql) or die(mysql_error());
	while($result2=mysql_fetch_assoc($result))
	{
		$CID = $result2['CUST_ID'];
		$cuEmail = $result2['CUST_Email'];
		$forename = $result2['CUST_Forename'];
		$surname = $result2['CUST_Surname'];
		$mobile = $result2['CUST_Mobile'];
		$homenum = $result2['CUST_HomeNum'];
		$add1 = $result2['CUST_AddressL1'];
		$add2 = $result2['CUST_AddressL2'];
		$add3 = $result2['CUST_AddressL3'];
		$pCode = $result2['CUST_Postcode'];
		$compID = $result2['COMP_ID'];
		$compName = $result2['COMP_Name'];
		$compEmail = $result2['COMP_Email'];
		$pNumber = $result2['COMP_PrimaryNum'];
		$sNumber = $result2['COMP_SecondaryNum'];
		$compAdd1 = $result2['COMP_AddressL1'];
		$compAdd2 = $result2['COMP_AddressL2'];
		$compAdd3 = $result2['COMP_AddressL3'];
		$compPostcode = $result2['COMP_Postcode'];
	}//end while

	$range = 3;

	// if not on page 1, don't show back links
	if ($currentpage > 1) {
	   // show First link to go back to page 1
		 echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={1}&sCriteria={$criteria}\">First </a>";
	   // get previous page num
	   $prevpage = $currentpage - 1;
	   // show Previous link to go back to 1 page
	  echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$prevpage}&sCriteria={$criteria}\">Previous</a>";
	} // end if

	 // loop to show links to range of pages around current page
	for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
	   // if it's a valid page number...
	   if (($x > 0) && ($x <= $totalpages)) {
		  // if we're on current page...
		  if ($x == $currentpage) {
			 // 'highlight' it but don't make a link
			 echo " [<b>$x</b>] ";
		  // if not current page...
		  } else {
			 // make it a link
			  echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$x}&sCriteria={$criteria}\">$x</a>";
		  } // end else
	   } // end if 
	} // end for
					 
	// if not on last page, show forward and last page links        
	if ($currentpage != $totalpages) {
	   // get next page
	   $nextpage = $currentpage + 1;
		// echo forward link for next page 
	  echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$nextpage}&sCriteria={$criteria}\">Next </a>";
	   // echo forward link for lastpage
		 echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$totalpages}&sCriteria={$criteria}\">Last</a>";
	} // end if
}
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: pagination returns more pages than results

Post by Celauran »

$totalpages has no criteria applied, so it's returning the full row count.
sectionLeader123
Forum Commoner
Posts: 31
Joined: Fri Oct 11, 2013 8:46 am

Re: pagination returns more pages than results

Post by sectionLeader123 »

Sorry but how do I fix it.

Thanks Again
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: pagination returns more pages than results

Post by Christopher »

Base your calculations on the page number you are on. Determine the starting record for the page and then use the LIMIT clause with the starting record number and the number of records per page
(#10850)
sectionLeader123
Forum Commoner
Posts: 31
Joined: Fri Oct 11, 2013 8:46 am

Re: pagination returns more pages than results

Post by sectionLeader123 »

I thought I was setting the criteria here or is there something I need to add:
$totalpages = ceil($numrows / $rowsperpage);

I am setting the limit at the end of my query here:
LIMIT $offset, $rowsperpage
sectionLeader123
Forum Commoner
Posts: 31
Joined: Fri Oct 11, 2013 8:46 am

Re: pagination returns more pages than results

Post by sectionLeader123 »

I got it thanks guys appreciate the input
Post Reply