pagination returns more pages than results
Posted: Mon Oct 14, 2013 7:28 am
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
}
?>