offset 與 limit

limit 5 表示只會回傳 5 筆
offset 1 表示跳過第 1 筆，從第二筆開始回傳

#會抓最新的五筆 (128~124)
SELECT * FROM `comments` order by id desc limit 5;

#會跳過前面五筆，再進行抓取 (123~119)
SELECT * FROM `comments` order by id desc limit 5 offset 5;

$item_per_page = 5;
$page=1;
這個時候
limit : $item_per_page
offset :  ($page-1)*item_per_page

在 main.php 加上我們定義好的東西

  $page = 1;
  $limit = 5;
  $offset = ($page - 1) * $limit;
  $stmt = $conn->prepare(
    'select ' .
      'C.id as id, C.content as content, C.create_at as create_at, ' .
      'U.nickname as nickname, U.username as username ' .
    'from comments as C ' . 
    'left join users as U on C.username = U.username ' .
    'where C.is_deleted is Null ' .
    'order by C.id desc ' .
    'limit ? offset ? '
  );
  $stmt->bind_param('ii', $limit, $offset);
  $result = $stmt->execute();

抓出有幾筆資料

<?php
   $stmt = $conn->prepare(
     'select count(id) as count from comments where is_deleted is null'
   );
   $result = $stmt->execute();
   $result = $stmt->get_result();
   $row = $result->fetch_assoc();
   echo $row['count'];
?>

製作總共有幾筆、有幾頁的畫面
利用　ceil 無條件進位

<?php
  $stmt = $conn->prepare(
     'select count(id) as count from comments where is_deleted is null'
  );
  $result = $stmt->execute();
  $result = $stmt->get_result();
  $row = $result->fetch_assoc();
  $count = $row['count'];
  $total_page = ceil($count / $limit);
?>
<hr>
<div class="page__info">
    <span>總共有 <?php echo $count ?>筆資料，頁數：</span>
    <span><?php echo $page ?> / <?php echo $total_page ?></span>
</div>

製作 paginator，用 get 帶頁數

#前面代碼的調整
  $page = 1;
  if (!empty($_GET['page'])) {
    $page = $_GET['page'];
  }

製作分頁按鈕

<div class="paginator">
    <a href="main.php?page=1">最前頁</a>
    <a href="main.php?page=<?php echo $page-1?>">上一頁</a>
    <a href="main.php?page=<?php echo $page+1?>">下一頁</a>
    <a href="main.php?page=<?php echo $total_page ?>">最末頁</a>
</div>

利用 php 做一些邏輯判斷，第一頁不顯示上一頁、最末頁不顯示下一頁，等功能

留意型態問題，可以用 intval 轉成數字，或是不用 ===

<div class="paginator">
    <?php if ($page != 1) {?>
        <a href="main.php?page=1">最前頁</a>
        <a href="main.php?page=<?php echo $page-1?>">上一頁</a>
    <?php }?>
    <?php if ($page != $total_page) {?>
        <a href="main.php?page=<?php echo $page+1?>">下一頁</a>
        <a href="main.php?page=<?php echo $total_page ?>">最末頁</a>
    <?php }?>
</div>

重點摘要