PHP Page Number failure - one extra erroneous page

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

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

PHP Page Number failure - one extra erroneous page

Post 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>";
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP Page Number failure - one extra erroneous page

Post by requinix »

Do you have any products with .catid values that do not exist in the categories table?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

No we don't. If they are not used, they are on "pause", so pause = "on".
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP Page Number failure - one extra erroneous page

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

Syncing...?? How?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP Page Number failure - one extra erroneous page

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP Page Number failure - one extra erroneous page

Post by Celauran »

What query did you use? What error did it give you?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post 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...??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP Page Number failure - one extra erroneous page

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post 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>";
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP Page Number failure - one extra erroneous page

Post by Celauran »

How many rows are returned by $queryc if you remove the LIMIT clause? Does that match with what $numrows says it should?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

18
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP Page Number failure - one extra erroneous page

Post 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.
Post Reply