How do I use PDO for this query of pagination?

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

How do I use PDO for this query of pagination?

Post by simonmlewis »

I am trying to upgrade our code, but these are quite old and I'm not sure sure how to convert this to PDO. Help?!

Code: Select all

$query   = "SELECT COUNT(id) AS numrows FROM products WHERE pause = 'off' AND rcstock <> 'out of stock'";
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = min(160, $row['numrows']);
    // how many pages we have when using paging?
$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
   {
   if ($page == "1")
      {
      $nav .= " <a href=\"/productsnew\" class='pagelink'>$page</a>";
      }
      else
      {
      $nav .= " <a href=\"/productsnew/page/$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=\"/productsnew/page/$page\" class='pagelink'>Prev</a> ";
 
   $first = " <a href=\"/productsnew\" class='pagelink'>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=\"/productsnew/page/$page\" class='pagelink'>Next</a>";
 
   $last = " <a href=\"/productsnew/page/$maxPage\" class='pagelink'>Last Page</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 . "</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: How do I use PDO for this query of pagination?

Post by Celauran »

What is it you're stuck on? Top bit can be replaced by something like this:

Code: Select all

$query   = "SELECT COUNT(id) AS numrows FROM products WHERE pause = 'off' AND rcstock <> 'out of stock'";
$result  = $pdo->query($query);
$count   = $result->fetchColumn();
$numrows = min(160, $count);
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I use PDO for this query of pagination?

Post by simonmlewis »

That's got it. Just couldnt' quite grasp how to do it in PDO.
PS that 'function' we worked on is working an absolute treat!
Some tech guys ran a crawler for us to generate all the images.
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: How do I use PDO for this query of pagination?

Post by Celauran »

That's great. See any way you could wrap this whole chunk of code in a function?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I use PDO for this query of pagination?

Post by simonmlewis »

Trouble is, easy one of these is different, depending on the page (finding different data). So wouldn't really serve much of a purpose.
Unless I had a function to do them all, and each page just give it different things to query on, but then the variables will also differ.
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: How do I use PDO for this query of pagination?

Post by Celauran »

I don't know enough about how they're implemented. Just looks like it'd be ripe for abstracting away as I suspect each variation of this is more the same than different.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: How do I use PDO for this query of pagination?

Post by thinsoldier »

simonmlewis wrote:Trouble is, easy one of these is different, depending on the page (finding different data). So wouldn't really serve much of a purpose.
Unless I had a function to do them all, and each page just give it different things to query on, but then the variables will also differ.

Reminds me of my pagination class:

Code: Select all

<?php

$sql = "SELECT 
       id, foo, bar, baz, blitz, hum, drum, blimp, bump 
       FROM products 
       WHERE pause = 'off' AND rcstock <> 'out of stock'";

$pgn8 = new Pagination( $pdo, $sql, $resultsPerPage, 'pgn8_sql_foobar_outofstock');

// Use only page number instead of ?page=$page
$pgn8->modRewrite   = true;
$pgn8->base_url     = '/productsnew/page/';
$pgn8->query_string = '';

$pgn8->templates['showingFromTo'] = 'path/to/alternative/showing_x_of_y_foobars.php';
$pgn8->templates['pagination']    = 'path/to/alternative/prev123456789next.php';
$pgn8->templates['prevNext']      = 'path/to/alternative/PREV_NEXT.php';

$results_markup = [];

foreach( $pgn8->findPageResult() as $data )
{
	$resultItem = new ResultItemDecorator( $data );
	$resultItem->css_class ='cols3';
	$results_markup[] = $resultItem->decorate();
}

//-----------------------------

echo $pgn8->showingFromTo('Elephants'); // "Showing 25 - 30 of 76 Elephants"
echo '<div class="results-list">';
echo implode("\n\n", $results_markup);
echo '</div>';
echo $pgn8->displayPagination(); // show clickable page numbers
echo $pgn8->displayPrevNext(); // show big PREV | NEXT buttons
Warning: I have no idea what I'm talking about.
Post Reply