Page 1 of 1

How do I use PDO for this query of pagination?

Posted: Wed Mar 22, 2017 10:55 am
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>";

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

Posted: Wed Mar 22, 2017 11:50 am
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);

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

Posted: Wed Mar 22, 2017 11:54 am
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.

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

Posted: Wed Mar 22, 2017 11:59 am
by Celauran
That's great. See any way you could wrap this whole chunk of code in a function?

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

Posted: Wed Mar 22, 2017 12:03 pm
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.

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

Posted: Wed Mar 22, 2017 12:06 pm
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.

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

Posted: Tue Mar 28, 2017 8:59 pm
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