CODEWITHSUNDEEP
phpmysqlmysqli
Pachuca
Pachuca

Reputation: 274

PHP display n post per page

I've seen other post similar to what I'm trying to do, but I can't seem to figure out what I'm doing wrong. I need to display 5/n results where n = total from my sql query. I've tried doing what other people suggested, but for some reason I'm getting really weird results and I can't seem to fix the problem.

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

  $stmt = $db -> prepare($sqlPosts);
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($totalPosts);
  $stmt->fetch();

  $sql = "SELECT
              body_of_post,
              date_time,
              post_owner,
              title,
              id
          FROM
              post_table
          ORDER BY
              date_time
          DESC LIMIT
              ?,?";
  $sqlFive = 5;
  $sqlTotal = $totalPosts/$sqlFive;
  $page = ($page - 1) * $limit;
  $stmt = $db -> prepare($sql);
  $stmt->bind_param('dd',$sqlFive, $sqlTotal);
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($body_of_post, $date_time, $post_owner, $title, $id);

  echo "<p> Number of results found: ".(($stmt->num_rows)/2)."/".$stmt->num_rows."</p>";
  echo '<a href=\"index.php?page='.$page.'\">Next '.$page.'</a>';

This is the result I get:

Number of results found: 1/2

Next 0

It should instead be like this:

Number of results found: 5/10

Next 5

Would someone please explain to me what I'm doing wrong and how to fix this? Also would it be easier to use OFFSET in the query?

Upvotes: 1

Views: 222

Answers (1)

Waldson Patricio
Waldson Patricio

Reputation: 1529

You are using the wrong variables to fetch and display the information:

//I'm assuming that $page exists somewhere before the code you posted
....
$rowsPerPage = 5; //renamed for clarity
$totalPages  = ceil($totalPosts/$rowsPerPage); //we need to round this value up
$offset      = ($page - 1) * $rowsPerPage; //e.g. on $page=2 we need an offset of five rows. Renamed for clarity
$stmt        = $db->prepare($sql);  
$stmt->bind_param('dd', $offset, $rowsPerPage); //OFFSET comes first when using comma notation
$stmt->execute();

$totalPostsOnThisPage = $stmt->num_rows; //to make things clear
//You could also show ($offset + 1) . '-' . ($offset + $totalPostsOnThisPage) . ' of ' . $totalPosts --- output: 6-10 of 10
echo "<p> Number of results found: ". $totalRowsOnThisPage . "/" . $totalPosts ."</p>";
//display next only if has more posts
if ($page < $totalPages) {

    echo '<a href=\"index.php?page='.$page.'\">Next '. $rowsPerPage .'</a>';
}

Upvotes: 2

Related Questions