Page 1 of 1

pagination returns more pages than results

Posted: Mon Oct 14, 2013 7:28 am
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
}
?>

Re: pagination returns more pages than results

Posted: Mon Oct 14, 2013 10:09 am
by Celauran
$totalpages has no criteria applied, so it's returning the full row count.

Re: pagination returns more pages than results

Posted: Mon Oct 14, 2013 10:15 am
by sectionLeader123
Sorry but how do I fix it.

Thanks Again

Re: pagination returns more pages than results

Posted: Mon Oct 14, 2013 9:43 pm
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

Re: pagination returns more pages than results

Posted: Tue Oct 15, 2013 3:46 am
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

Re: pagination returns more pages than results

Posted: Tue Oct 15, 2013 5:37 am
by sectionLeader123
I got it thanks guys appreciate the input