CODEWITHSUNDEEP
phpmysqlpdopagination
Muhambi
Muhambi

Reputation: 3522

Issue with pagination

I'm making a simple select-from-database-and-display application and am building pagination to go alongside it.

Here's my code:

<?php   
   $sqlcount = "SELECT * FROM user where user_id= ?";
   $qcount = $db->prepare($sqlcount);
   $qcount->execute(array($id));
   $qcount->setFetchMode(PDO::FETCH_BOTH);
   $total = $qcount->rowCount();

   // How many items to list per page
   $limit = 10;

   // How many pages will there be
   $pages = ceil($total / $limit);

   // What page are we currently on?
   $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
                            'options' => array(
                                'default'   => 1,
                                'min_range' => 1,
                            ),
   )));

   // Calculate the offset for the query
   $offset = ($page - 1)  * $limit;

   // Some information to display to the user
   $start = $offset + 1;
   $end = min(($offset + $limit), $total);

   // The "back" link
   $prevlink = ($page > 1) ? '<a href="?page=1" title="First page">&laquo;</a> <a href="?page=' . ($page - 1) . '" title="Previous page">&lsaquo;</a>' : '<span class="disabled">&laquo;</span> <span class="disabled">&lsaquo;</span>';

   // The "forward" link
   $nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page">&rsaquo;</a> <a href="?page=' . $pages . '" title="Last page">&raquo;</a>' : '<span class="disabled">&rsaquo;</span> <span class="disabled">&raquo;</span>';

   // Display the paging information
   echo '<div id="paging"><p>', $prevlink, ' Page ', $page, ' of ', $pages, ' pages, displaying ', $start, '-', $end, ' of ', $total, ' results ', $nextlink, ' </p></div>';

   $stmt = $db->prepare("SELECT * FROM recipes WHERE user_id = :userid ORDER BY name LIMIT :limit OFFSET :offset");
   $stmt->bindParam(':userid', $id, PDO:: PARAM_INT);
   $stmt->bindParam(':limit', $limit, PDO:: PARAM_INT);
   $stmt->bindParam(':offset', $offset, PDO:: PARAM_INT);
   $stmt->execute();
   $rowcount = $stmt->rowCount();

   echo "<ul id='container'>";
   if(empty($rowcount)){echo"No recipes";}
   while($row = $stmt->fetch()){
    $recipeid = $row['id'];
    $public = $row['public'];

    //here i left out some stuff unimportant to the problem
   }
?>

Before I put in the pagination, my results were displayed. Now, with error reporting on there are no errors, but it displays "No Recipes" and « ‹ Page 0 of 0 pages, displaying -9-0 of 0 results › ».

Upvotes: 2

Views: 281

Answers (1)

Alon Eitan
Alon Eitan

Reputation: 12025

You are trying to count the rows returned from the query with the rowCount() function, witch is being used to return the affected rows from INSERT, UPDATE, DELETE query. You need to create a count query.

Try this example instead:

$sqlcount = "SELECT COUNT(*) FROM user where user_id= ?";
$qcount = $db->prepare($sqlcount);
$qcount->execute(array($id));
$total = $qcount->fetchColumn();

Upvotes: 2

Related Questions