限制每页显示的数据库条目数

Question

How would we select just, lets say 12 database entries per page, and then push 12 more onto the next page and so on and so forth?

This would be implementing pagination from boostrap, so:

<ul class="pagination">
  <li><a href="#">1</a></li>
  <li><a href="#">2</a></li>
  <li><a href="#">3</a></li>
  <li><a href="#">4</a></li>
  <li><a href="#">5</a></li>
</ul>

and if it helps here is some php, this is just the basic form that I used to grab and display the entires, but they all appear on one page right now.

$id = (empty($_GET['id'])) ? : $_GET['id'] ;

$query = "SELECT title,whole FROM stories ";
$stmt = $db->prepare($query);

$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
//delete this-> $info = mysql_fetch_array($query1);   ?
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{

    ?>
    <div class="left">
       <a href="">
          <span class="text-content"><span><?php echo htmlentities($row["whole"]); ?></span></span>
       </a>
    </div>
    <?php
}

?>

Answer 1

Based on your code examples, you could easily use LIMIT and OFFSET in your query to enable pagination.

SELECT title, whole FROM stories LIMIT 12 OFFSET 0

The above query would show the 12 results.

<?php
$per_page = 12;
$id = (empty($_GET['id'])) ? 0 : $_GET['id'] ;
$offset = $per_page * $id;
$query = "SELECT title, whole FROM stories LIMIT :limit OFFSET :offset";
$stmt = $db->prepare($query);
$stmt->bindParam(":limit", $per_page, PDO::PARAM_INT);
$stmt->bindParam(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
//delete this-> $info = mysql_fetch_array($query1);   ?
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{

    ?>
    <div class="left">
       <a href="">
          <span class="text-content"><span><?php echo htmlentities($row["whole"]); ?></span></span>
       </a>
    </div>
    <?php
}

This isn't tested, but it should point you in the right direction.

Here is a link to a more in-depth explanation.