將資料庫結果集切割為固定大小的頁面顯示，需三個核心參數：
$PageSize：每頁筆數$CurrentPageID：當前頁碼 (從 1 開始)$totalRows (透過 SELECT COUNT(*) 取得)衍生參數：
$numPages = ceil($totalRows / $PageSize)($CurrentPageID - 1) * $PageSize-- 第 1 頁 (每頁 10 筆)
SELECT * FROM `table` ORDER BY id DESC LIMIT 0, 10;
-- 第 2 頁
SELECT * FROM `table` ORDER BY id DESC LIMIT 10, 10;
-- 通用模板
SELECT * FROM `table` ORDER BY id DESC LIMIT {(page-1)*size}, {size}<?php
// 1. 連線資料庫 (建議改用 PDO/MySQLi)
$link = mysqli_connect("localhost", "user", "pass", "dbname") or die("Connect failed: " . mysqli_connect_error());
// 2. 取得當前頁碼
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$pageSize = 10;
// 3. 查總筆數
$totalRows = 0;
$res = mysqli_query($link, "SELECT COUNT(*) AS cnt FROM `articles`");
if ($row = mysqli_fetch_assoc($res)) $totalRows = (int)$row['cnt'];
// 4. 計算總頁數
$numPages = $totalRows ? (int)ceil($totalRows / $pageSize) : 0;
// 5. 修正頁碼範圍
$page = min($page, max(1, $numPages));
// 6. 取得分頁資料
$offset = ($page - 1) * $pageSize;
$sql = "SELECT * FROM `articles` ORDER BY id DESC LIMIT $offset, $pageSize";
$result = mysqli_query($link, $sql);
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
// 7. 產生分頁連結
$links = '';
if ($page > 1) {
$links .= '<a href="?page=1">首頁</a> ';
$links .= '<a href="?page=' . ($page - 1) . '">上一頁</a> ';
}
for ($i = max(1, $page - 2); $i <= min($numPages, $page + 2); $i++) {
$links .= ($i == $page) ? "<strong>$i</strong> " : '<a href="?page=' . $i . '">' . $i . '</a> ';
}
if ($page < $numPages) {
$links .= '<a href="?page=' . ($page + 1) . '">下一頁</a> ';
$links .= '<a href="?page=' . $numPages . '">尾頁</a> ';
}
// 8. 輸出
foreach ($rows as $row) {
echo "<div>" . htmlspecialchars($row['title']) . "</div>";
}
echo "<div class='pagination'>$links</div>";
?><?php
// Pager.class.php
class Pager {
public $pageSize = 10;
public $currentPage = 1;
public $totalRows = 0;
public $sql = '';
public $pdo; // PDO 實例
// 衍生屬性
public $numPages = 0;
public $isFirstPage = false;
public $isLastPage = false;
public $prevPage = 0;
public $nextPage = 0;
public function __construct(PDO $pdo, array $options = []) {
$this->pdo = $pdo;
foreach ($options as $k => $v) {
if (property_exists($this, $k)) $this->$k = $v;
}
$this->calculate();
}
private function calculate() {
if ($this->totalRows === 0) {
$stmt = $this->pdo->query("SELECT COUNT(*) FROM ($this->sql) AS t");
$this->totalRows = (int)$stmt->fetchColumn();
}
$this->numPages = $this->totalRows ? (int)ceil($this->totalRows / $this->pageSize) : 0;
$this->currentPage = max(1, min($this->currentPage, $this->numPages ?: 1));
$this->isFirstPage = ($this->currentPage === 1);
$this->isLastPage = ($this->currentPage === $this->numPages);
$this->prevPage = $this->isFirstPage ? 0 : $this->currentPage - 1;
$this->nextPage = $this->isLastPage ? 0 : $this->currentPage + 1;
}
// 取得資料 (回傳關聯陣列)
public function getPageData() {
if ($this->totalRows === 0) return [];
$offset = ($this->currentPage - 1) * $this->pageSize;
$stmt = $this->pdo->prepare("$this->sql LIMIT :offset, :limit");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $this->pageSize, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// 取得 PDOStatement (大資料集逐行處理，省記憶體)
public function getDataLink() {
if ($this->totalRows === 0) return false;
$offset = ($this->currentPage - 1) * $this->pageSize;
$stmt = $this->pdo->prepare("$this->sql LIMIT :offset, :limit");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $this->pageSize, PDO::PARAM_INT);
$stmt->execute();
return $stmt;
}
// 產生分頁 HTML (可依需求覆寫)
public function renderLinks($baseUrl = '?') {
if ($this->numPages <= 1) return '';
$html = '<nav class="pagination">';
if (!$this->isFirstPage) {
$html .= '<a href="' . $baseUrl . 'page=1">首頁</a> ';
$html .= '<a href="' . $baseUrl . 'page=' . $this->prevPage . '">上一頁</a> ';
}
$start = max(1, $this->currentPage - 3);
$end = min($this->numPages, $this->currentPage + 3);
for ($i = $start; $i <= $end; $i++) {
$html .= ($i == $this->currentPage)
? '<span class="current">' . $i . '</span> '
: '<a href="' . $baseUrl . 'page=' . $i . '">' . $i . '</a> ';
}
if (!$this->isLastPage) {
$html .= '<a href="' . $baseUrl . 'page=' . $this->nextPage . '">下一頁</a> ';
$html .= '<a href="' . $baseUrl . 'page=' . $this->numPages . '">尾頁</a> ';
}
$html .= '</nav>';
return $html;
}
}<?php
require 'Pager.class.php';
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'user', 'pass');
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$sql = "SELECT id, title, created_at FROM articles WHERE status = 1 ORDER BY id DESC";
$pager = new Pager($pdo, [
'sql' => $sql,
'pageSize' => 15,
'currentPage' => $page
]);
$data = $pager->getPageData();
?>
<!DOCTYPE html>
<html>
<body>
<div class="list">
<?php foreach ($data as $row): ?>
<article><h3><?= htmlspecialchars($row['title']) ?></h3></article>
<?php endforeach; ?>
</div>
<?= $pager->renderLinks() ?>
</body>
</html>不同資料庫分頁語法不同：
LIMIT offset, rows / LIMIT rows OFFSET offsetOFFSET offset ROWS FETCH NEXT rows ROWS ONLYROWNUM 子查詢或 OFFSET ... FETCH (12c+)使用 PDO 時，建議在 Pager 類別內依 $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) 動態產生對應 SQL，或採用 LIMIT 模擬函式庫。
OFFSET 深分頁效能問題，改用 WHERE id > last_seen_id ORDER BY id LIMIT 10。COUNT(*) 結果。{page, pageSize, total, data})，前端 Vue/React 元件渲染。
