PHP 分頁技術完整實作：原理解析、SQL LIMIT 用法與完整程式碼

分頁核心原理

分頁僅需兩個參數：

• $pageSize：每頁筆數
• $currentPage：目前頁碼 (從 1 開始)

MySQL LIMIT offset, rows 即可取得指定區段資料：

SELECT * FROM `table` LIMIT ($currentPage - 1) * $pageSize, $pageSize

完整分頁類別範例 (PDO 版)

<?php
class Paginator {
    private PDO $pdo;
    private int $pageSize;
    private int $currentPage;
    private string $table;
    private string $where = '';

    public function __construct(PDO $pdo, string $table, int $pageSize = 10) {
        $this->pdo = $pdo;
        $this->table = $table;
        $this->pageSize = $pageSize;
        $this->currentPage = max(1, (int)($_GET['page'] ?? 1));
    }

    public function setWhere(string $where): self {
        $this->where = $where ? "WHERE $where" : '';
        return $this;
    }

    public function getTotal(): int {
        $sql = "SELECT COUNT(*) FROM {$this->table} {$this->where}";
        return (int)$this->pdo->query($sql)->fetchColumn();
    }

    public function getTotalPages(): int {
        $total = $this->getTotal();
        return $total ? (int)ceil($total / $this->pageSize) : 0;
    }

    public function getData(): array {
        $offset = ($this->currentPage - 1) * $this->pageSize;
        $sql = "SELECT * FROM {$this->table} {$this->where} ORDER BY id DESC LIMIT $offset, {$this->pageSize}";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function renderLinks(string $baseUrl = '?'): string {
        $totalPages = $this->getTotalPages();
        if ($totalPages <= 1) return '';

        $html = '';
        return $html;
    }
}
?>

使用範例

<?php
require 'Paginator.php';

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'user', 'pass');
$paginator = new Paginator($pdo, 'articles', 10)
    ->setWhere("status = 'published'");

$articles = $paginator->getData();
foreach ($articles as $art) {
    echo "
{$art['title']}
";
}
echo $paginator->renderLinks();
?>

關鍵點總結

1. 使用 intval() 或 (int) 強制轉型頁碼，防止 SQL 注入。
2. 總頁數計算：ceil($total / $pageSize)。
3. 連結產生時保留其他查詢參數 (如搜尋關鍵字)，可用 http_build_query() 組合。
4. 大資料集建議使用「游標分頁」 (WHERE id > $lastId LIMIT $pageSize) 替代 OFFSET，效能更佳。