分頁三要素:總筆數、每頁筆數、當前頁碼。由此推導總頁數、起始位移、資料庫 LIMIT 子句。
<?php
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$pageSize = 15;
// 1. 取得總筆數
$total = $pdo->query("SELECT COUNT(*) FROM articles")->fetchColumn();
// 2. 計算總頁數
$pageCount = ceil($total / $pageSize);
$page = min($page, $pageCount); // 修正超出範圍頁碼
// 3. 計算位移
$offset = ($page - 1) * $pageSize;
// 4. 查詢當頁資料
$stmt = $pdo->prepare("SELECT * FROM articles ORDER BY id DESC LIMIT :offset, :size");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':size', $pageSize, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll();
// 5. 輸出資料
foreach ($rows as $row) {
echo "{$row['title']}";
}
// 6. 產生分頁連結
for ($i = 1; $i <= $pageCount; $i++) {
$class = ($i === $page) ? 'class="active"' : '';
echo "$i ";
}
?>
<?php
class Paginator {
private PDO $pdo;
private int $pageSize;
private int $currentPage;
private int $total;
private int $pageCount;
public function __construct(PDO $pdo, int $pageSize = 15) {
$this->pdo = $pdo;
$this->pageSize = $pageSize;
$this->currentPage = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
}
public function paginate(string $table, string $where = '', array $params = [], string $order = 'id DESC'): array {
// 總筆數
$countSql = "SELECT COUNT(*) FROM $table" . ($where ? " WHERE $where" : '');
$this->total = $this->pdo->prepare($countSql);
$this->pdo->execute($params);
$this->total = $this->pdo->fetchColumn();
$this->pageCount = max(1, (int)ceil($this->total / $this->pageSize));
$this->currentPage = min($this->currentPage, $this->pageCount);
$offset = ($this->currentPage - 1) * $this->pageSize;
$sql = "SELECT * FROM $table" . ($where ? " WHERE $where" : '') . " ORDER BY $order LIMIT :offset, :size";
$stmt = $this->pdo->prepare($sql);
foreach ($params as $k => $v) $stmt->bindValue($k, $v);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':size', $this->pageSize, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll();
}
public function links(string $baseUrl = ''): string {
$html = '';
for ($i = 1; $i <= $this->pageCount; $i++) {
$class = ($i === $this->currentPage) ? ' class="active"' : '';
$html .= "$i ";
}
return $html;
}
public function info(): array {
return [
'total' => $this->total,
'page' => $this->currentPage,
'pageCount' => $this->pageCount,
'pageSize' => $this->pageSize,
];
}
}
// 使用方式
$pager = new Paginator($pdo, 15);
$articles = $pager->paginate('articles', 'status = ?', [1]);
echo $pager->links();
?>
OFFSET 深分頁效能下降,改用 游標分頁(WHERE id > last_id LIMIT N)。COUNT(*) 壓力。ORDER BY 欄位需建立索引。分頁是 Web 開發基礎技能,掌握核心邏輯並封裝為可復用元件,能大幅提升開發效率與程式碼品質。