Page 1 of 2

PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 9:58 am
by simonmlewis
Got a bit of an issue here.
We have this page that shows all the categories assigned NOT to "ancilliaries". Then per category, it shows each product.

Because of how many there are, I want to split it into pages. I thought this would work via the first level script you will see, and indeed it does, except, it adds another page on the bottom that isn't needed.

On the final page (4) it shows the final category and final product to be shown. I know this by removing the page numbering element.

But put it back, and you get a 5th page, that's empty.
Why the 5th page?

There are 18 categories in all. Varied number of product per category.

Code: Select all

<?php
$detect = new Mobile_Detect;
echo "<div class='head'><h1>all stone flooring</h1></div>";

	  // how many rows to show per page
$rowsPerPage = 5;
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if (isset($_GET['pagenum'])) {
        $pageNum = $_GET['pagenum'];
}

$offset = ($pageNum - 1) * $rowsPerPage;	

$queryc = "SELECT p.id AS id, catid, category, c.priority AS priority FROM products AS p INNER JOIN categories AS c ON p.catid = c.id WHERE pause <> 'on' AND producttype <> 'ancilliaries' GROUP BY category ORDER BY category LIMIT $offset, $rowsPerPage";
$resultc = $pdo->query($queryc);
$count = 0;
$countcat = 0;
while ($rowc = $resultc->fetch(PDO::FETCH_OBJ)) 
      {
      $countcat ++;
      echo "<div class='stone-type-header'";
      if ($countcat == "1") { echo " style='margin-top: 1px'";}
      echo "><h2>$rowc->category</h2></div>";
      $count = 0;
      $query = "SELECT * FROM products WHERE catid =:catid  AND pause <> 'on' ORDER BY title";
$result = $pdo->prepare($query);
$result->execute(array(':catid' => $rowc->catid));
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
      {
      
      $titlereplace = str_replace(" ", "-", $row->title);
      $categreplace = str_replace(" ", "-", $row->category);      
      $count ++;
      echo "<div class='thumbs-tile'><a href='/product/$categreplace/$row->id/$titlereplace'><img src='/images/productphotos/small/$row->photoprimary' alt='$row->title' />
      <div class='thumbs-tile-overlay'>
        <div class='thumbs-tile-overlay-inner'>
      <div class='thumbs-tile-title'>$row->title</div>
      <div class='thumbs-tile-price'>From ";
      if ($row->price1 > "0" && $row->discount1 == "yes")
        {
        echo "<i class='fa fa-caret-down' aria-hidden='true' style='color: #ff0000'></i> &pound;";
        printf ("%.2f", $row->price1);
        }
      else
        {
        echo "&pound;";
        printf ("%.2f", $row->instoreprice1);
        }
			
      echo " + VAT</div></div>
      </div></a>
      </div>";
      
      // if not on a mobile, ensure a clear line after ever four items, incase of dodgy image heights
      if ( $detect->isMobile() && !$detect->isTablet() ) {
}
else
{
 if (($count % 4) == 0)
      {
      echo "<div style='clear:both'></div>";
      }
}}
echo "<div style='clear:both'></div>";
    }
     echo "<br/>";

$query   = "SELECT COUNT(id) AS numrows FROM products WHERE pause <> 'on' AND producttype <> 'ancilliaries' GROUP BY category";
	$result  = $pdo->query($query);
  $numrows = $result->fetchColumn();
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " <div class='pagelinkactive'>$page</div> "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"/stone-flooring/$page\" class='pagelink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"/stone-flooring/$page\" class='pagelink'><i class='fa fa-angle-left' aria-hidden='true'></i> Prev</a> ";
 
   $first = " <a href=\"/stone-flooring/\" class='pagelink'><i class='fa fa-angle-double-left' aria-hidden='true'></i> First Page</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"/stone-flooring/$page\" class='pagelink'>Next <i class='fa fa-angle-right' aria-hidden='true'></i></a>";
 
   $last = " <a href=\"/stone-flooring/$maxPage\" class='pagelink'>Last Page <i class='fa fa-angle-double-right' aria-hidden='true'></i></a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last;
echo "</div>";
?>

Re: PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 10:19 am
by requinix
Do you have any products with .catid values that do not exist in the categories table?

Re: PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 11:03 am
by simonmlewis
No we don't. If they are not used, they are on "pause", so pause = "on".

Re: PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 11:49 am
by requinix
Well, you have two queries in there

Code: Select all

$queryc = "SELECT p.id AS id, catid, category, c.priority AS priority FROM products AS p INNER JOIN categories AS c ON p.catid = c.id WHERE pause <> 'on' AND producttype <> 'ancilliaries' GROUP BY category ORDER BY category LIMIT $offset, $rowsPerPage";

Code: Select all

$query   = "SELECT COUNT(id) AS numrows FROM products WHERE pause <> 'on' AND producttype <> 'ancilliaries' GROUP BY category";
that aren't matching up: the second is counting some number of products that the first isn't showing you. The catid values seemed like the culprit. Try syncing the queries to see if that helps, and if so then you need to see why the two as they are now isn't working.

Re: PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 12:14 pm
by simonmlewis
Syncing...?? How?

Re: PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 1:16 pm
by Celauran
The INNER JOIN is resulting in rows being excluded from the result set. If you want to know how many results your query is going to return, you need to run COUNT on the same query. Add the JOIN, remove offset/limit.

Re: PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 2:19 pm
by simonmlewis
I did try that, but couldn't get the code to work.
I did the same query, but with COUNT at the start, but it failed. Didn't produce anything.

Re: PHP Page Number failure - one extra erroneous page

Posted: Thu Jun 30, 2016 2:22 pm
by Celauran
What query did you use? What error did it give you?

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 8:12 am
by simonmlewis

Code: Select all

$query   = "SELECT COUNT(p.id) AS numrows, catid, category, c.priority AS priority FROM products AS p INNER JOIN categories AS c ON p.catid = c.id WHERE pause <> 'on' AND producttype <> 'ancilliaries' GROUP BY category";
This produces that extra page.

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 8:21 am
by simonmlewis
Is it a coincidence that whatever I enter at the top as rowsPerPage, that is how many are in the bottom page numbering...??

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 8:24 am
by Celauran
That query you just posted should be yielding precisely the same number of rows as the query you're trying to iterate over. I suspect the error lies elsewhere.

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 8:29 am
by simonmlewis
This is the whole chunk of code....

Code: Select all

<?php
$detect = new Mobile_Detect;
echo "<div class='head'><h1>all stone flooring</h1></div>";

// how many rows to show per page
$rowsPerPage = 5;
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if (isset($_GET['pagenum'])) {
        $pageNum = $_GET['pagenum'];
}

$offset = ($pageNum - 1) * $rowsPerPage;	

$queryc = "SELECT p.id AS id, catid, category, c.priority AS priority FROM products AS p INNER JOIN categories AS c ON p.catid = c.id WHERE pause <> 'on' AND producttype <> 'ancilliaries' GROUP BY category ORDER BY category LIMIT $offset, $rowsPerPage";
$resultc = $pdo->query($queryc);
$count = 0;
$countcat = 0;
while ($rowc = $resultc->fetch(PDO::FETCH_OBJ)) 
      {
      $countcat ++;
      echo "<div class='stone-type-header'";
      if ($countcat == "1") { echo " style='margin-top: 1px'";}
      echo "><h2>$rowc->category</h2></div>";
      $count = 0;
      $query = "SELECT * FROM products WHERE catid =:catid  AND pause <> 'on' ORDER BY title";
$result = $pdo->prepare($query);
$result->execute(array(':catid' => $rowc->catid));
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
      {
      
      $titlereplace = str_replace(" ", "-", $row->title);
      $categreplace = str_replace(" ", "-", $row->category);      
      $count ++;
      echo "<div class='thumbs-tile'><a href='/product/$categreplace/$row->id/$titlereplace'><img src='/images/productphotos/small/$row->photoprimary' alt='$row->title' />
      <div class='thumbs-tile-overlay'>
        <div class='thumbs-tile-overlay-inner'>
      <div class='thumbs-tile-title'>$row->title</div>
      <div class='thumbs-tile-price'>From ";
      if ($row->price1 > "0" && $row->discount1 == "yes")
        {
        echo "<i class='fa fa-caret-down' aria-hidden='true' style='color: #ff0000'></i> &pound;";
        printf ("%.2f", $row->price1);
        }
      else
        {
        echo "&pound;";
        printf ("%.2f", $row->instoreprice1);
        }
			
      echo " + VAT</div></div>
      </div></a>
      </div>";
      
      // if not on a mobile, ensure a clear line after ever four items, incase of dodgy image heights
      if ( $detect->isMobile() && !$detect->isTablet() ) {
}
else
{
 if (($count % 4) == 0)
      {
      echo "<div style='clear:both'></div>";
      }
}}
echo "<div style='clear:both'></div>";
    }
     echo "<br/>";

$query = "SELECT COUNT(p.id) AS numrows, catid, category, c.priority AS priority FROM products AS p INNER JOIN categories AS c ON p.catid = c.id WHERE pause <> 'on' AND producttype <> 'ancilliaries' GROUP BY category";
	$result  = $pdo->query($query);
  $numrows = $result->fetchColumn();
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " <div class='pagelinkactive'>$page</div> "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"/stone-flooring/$page\" class='pagelink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"/stone-flooring/$page\" class='pagelink'><i class='fa fa-angle-left' aria-hidden='true'></i> Prev</a> ";
 
   $first = " <a href=\"/stone-flooring/\" class='pagelink'><i class='fa fa-angle-double-left' aria-hidden='true'></i> First Page</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"/stone-flooring/$page\" class='pagelink'>Next <i class='fa fa-angle-right' aria-hidden='true'></i></a>";
 
   $last = " <a href=\"/stone-flooring/$maxPage\" class='pagelink'>Last Page <i class='fa fa-angle-double-right' aria-hidden='true'></i></a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last;
echo "</div>";
?>

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 8:33 am
by Celauran
How many rows are returned by $queryc if you remove the LIMIT clause? Does that match with what $numrows says it should?

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 8:49 am
by simonmlewis
18

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 9:08 am
by Celauran
There were two questions there and they're related. 18 by itself provides no information. Is the answer to both questions 18? If so, then there's an error in your logic somewhere. If not, the error is in the SQL.