CODEWITHSUNDEEP
php
Alex
Alex

Reputation: 179

PHP - pagination with prepared statements

I'm pretty new so go easy on me. I have seen a number of tutorials and questions related to this subject. I am trying to figure out what I am doing wrong because I can't seem to get pagination to work with my prepared statements. My database currently has 10 rows that it should return. I'm going to try and add the option to insert more rows later so that number will change. Here's the database query and code.

//determine how many results are available in database

  $sqlPosts = "SELECT
                count(post_owner)
                FROM post_table";

  $stmt = $db -> prepare($sqlPosts);
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($totalPosts);
  $stmt->fetch();
  //determine which page user is currently on
  if (!isset($_GET['page']))
  $current_Page = 1;
  else
  $current_Page = $_GET['page'];
  // define how many results you want per page
  $page = 1;
  $rowsPerPage = 5;
  //total number of available pages
  $total_Pages  = ceil($totalPosts/$rowsPerPage);
  // determine the sql LIMIT starting number for the results on the displaying page
  $start_from = ($page - 1) * $rowsPerPage;


  // retrieve selected results from database and display them on page
  $sql = "SELECT
              body_of_post,
              date_time,
              post_owner,
              title,
              id
          FROM
              post_table
          ORDER BY
              date_time
          DESC LIMIT
              ?,?";
  $stmt = $db->prepare($sql);
  $stmt->bind_param('dd', $start_from, $rowsPerPage);
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($body_of_post, $date_time, $post_owner, $title, $id);
while ($stmt->fetch()) {
  echo '<table border="1">
        <tr>
          <td bgcolor="#CCCCCC">Title</td>
          <td bgcolor="#CCCCCC">Post</td>
          <td bgcolor="#CCCCCC">Created by</td>
          <td bgcolor="#CCCCCC">Date</td>
        </tr>
        <br>';
    echo
    '<tr><th><h2>"' . $title .'"</th>
    <th>'. $body_of_post. '</th>
    <th>'. $post_owner. '</th>
    <th>'. $date_time. '</tr></th></h2>';
  }

I think my problem is in the while loop, but i'm not sure how else to code it. The paging part should be fine. When I click the Next link I see that index.php?page= will update inclemently, but the database rows remain the same. I would like for page=1 to show the first 5 rows from the database than page=2 should show the next 5 rows and so on.

Here's the paging part.

if($current_Page > 1){
    $prev_page = $current_Page - 1;
    $previous = "<a href=\"index.php?page=$prev_page\">Previous</a>";
  }

  if ($total_Pages > 1){
      for($page = 1; $page <= $total_Pages; $page++){
          if ($page == $current_Page){
            $page_count = "";
          }
          else
            echo "<a href=\"index.php?page=$page\">Next$rowsPerPage</a>";
         }
      echo $page_count;
}

Upvotes: 1

Views: 956

Answers (1)

Shadow
Shadow

Reputation: 34232

The problem is with the following line:

$start_from = ($page - 1) * $rowsPerPage;

You set $page variable to a fix 1, therefore only the first set of records get displayed. Change this line to

$start_from = ($current_Page - 1) * $rowsPerPage;

to use the page parameter supplied in the url.

Upvotes: 1

Related Questions