SELECT * FROM articles ORDER BY created_at DESC LIMIT {offset}, {pageSize};
SELECT COUNT(*) FROM articles WHERE ...; -- 總筆數
總頁數 = ceil(總筆數 / 每頁筆數)
<?php
declare(strict_types=1);
class Paginator
{
public function __construct(
private PDO $pdo,
private string $baseUrl = '',
private int $pageSize = 15,
private string $pageParam = 'page'
) {}
/**
* @param string $sqlCount SELECT COUNT(*) FROM ... WHERE ...
* @param string $sqlData SELECT * FROM ... WHERE ... ORDER BY ... (不含 LIMIT)
* @param array $params 參數綁定陣列
* @return array{data: array, pagination: array}
*/
public function paginate(string $sqlCount, string $sqlData, array $params = []): array
{
// 1. 取得目前頁碼
$page = max(1, (int)($_GET[$this->pageParam] ?? 1));
// 2. 計算總筆數
$stmtCount = $this->pdo->prepare($sqlCount);
$stmtCount->execute($params);
$total = (int)$stmtCount->fetchColumn();
// 3. 計算分頁資訊
$totalPages = (int)ceil($total / $this->pageSize);
$page = min($page, max(1, $totalPages));
$offset = ($page - 1) * $this->pageSize;
// 4. 查詢當頁資料
$sqlPage = $sqlData . " LIMIT {$offset}, {$this->pageSize}";
$stmtData = $this->pdo->prepare($sqlPage);
$stmtData->execute($params);
$data = $stmtData->fetchAll(PDO::FETCH_ASSOC);
// 5. 組裝分頁中繼資料
$pagination = [
'current_page' => $page,
'per_page' => $this->pageSize,
'total' => $total,
'total_pages' => $totalPages,
'has_prev' => $page > 1,
'has_next' => $page < $totalPages,
'prev_page' => $page > 1 ? $page - 1 : null,
'next_page' => $page < $totalPages ? $page + 1 : null,
'first_url' => $this->buildUrl(1),
'last_url' => $this->buildUrl($totalPages),
'prev_url' => $page > 1 ? $this->buildUrl($page - 1) : null,
'next_url' => $page < $totalPages ? $this->buildUrl($page + 1) : null,
'links' => $this->generateLinks($page, $totalPages),
];
return ['data' => $data, 'pagination' => $pagination];
}
private function buildUrl(int $page): string
{
$params = $_GET;
$params[$this->pageParam] = $page;
return $this->baseUrl . '?' . http_build_query($params);
}
private function generateLinks(int $current, int $total): array
{
$links = [];
$range = 2; // 當前頁前後各顯示幾頁
for ($i = max(1, $current - $range); $i <= min($total, $current + $range); $i++) {
$links[] = [
'page' => $i,
'url' => $this->buildUrl($i),
'active' => $i === $current,
'label' => (string)$i,
];
}
// 加入首尾省略號邏輯...
return $links;
}
}
<?php
// api/articles.php
require_once 'bootstrap.php'; // $pdo, $paginator
$where = '';
$params = [];
if (!empty($_GET['category'])) {
$where = 'WHERE category = ?';
$params[] = $_GET['category'];
}
$sqlCount = "SELECT COUNT(*) FROM articles {$where}";
$sqlData = "SELECT id, title, excerpt, created_at FROM articles {$where} ORDER BY created_at DESC";
$result = $paginator->paginate($sqlCount, $sqlData, $params);
header('Content-Type: application/json');
echo json_encode([
'success' => true,
'data' => $result['data'],
'pagination' => $result['pagination'],
]);
<!-- Pagination.vue -->
<script setup>
import { ref, watch, onMounted } from 'vue';
const props = defineProps({
baseUrl: { type: String, required: true },
initialPage: { type: Number, default: 1 },
pageSize: { type: Number, default: 15 },
});
const loading = ref(false);
const articles = ref([]);
const pagination = ref({
current_page: props.initialPage,
total_pages: 1,
total: 0,
links: [],
});
async function fetchPage(page) {
loading.value = true;
try {
const url = new URL(props.baseUrl, window.location.origin);
url.searchParams.set('page', page);
const res = await fetch(url);
const json = await res.json();
if (json.success) {
articles.value = json.data;
pagination.value = json.pagination;
}
} catch (e) {
console.error(e);
} finally {
loading.value = false;
}
}
watch(() => props.initialPage, (p) => fetchPage(p));
onMounted(() => fetchPage(props.initialPage));
</script>
<template>
<div v-if="loading" class="loading">載入中...</div>
<ul v-else class="articles">
<li v-for="a in articles" :key="a.id">
<h3>{{ a.title }}</h3>
<p>{{ a.excerpt }}</p>
<time>{{ a.created_at }}</time>
</li>
</ul>
<nav v-if="pagination.total_pages > 1" class="pagination" aria-label="分頁">
<button @click="fetchPage(pagination.prev_page)" :disabled="!pagination.has_prev">« 上一頁</button>
<template v-for="link in pagination.links" :key="link.page">
<button
@click="fetchPage(link.page)"
:class="{ active: link.active }"
:disabled="link.active"
>{{ link.label }}</button>
</template>
<button @click="fetchPage(pagination.next_page)" :disabled="!pagination.has_next">下一頁 »</button>
</nav>
</template>
<style scoped>
.pagination { display: flex; gap: 0.5rem; margin-top: 1rem; }
.pagination button { padding: 0.5rem 1rem; border: 1px solid #ddd; background: white; cursor: pointer; }
.pagination button.active { background: #007bff; color: white; border-color: #007bff; }
.pagination button:disabled { opacity: 0.5; cursor: not-allowed; }
</style>
<?php
// articles.php (傳統模板)
$result = $paginator->paginate($sqlCount, $sqlData, $params);
$articles = $result['data'];
$p = $result['pagination'];
?>
<!-- 列表渲染 -->
<ul>
<li><h3>= htmlspecialchars($a['title']) ?></h3><p>= htmlspecialchars($a['excerpt']) ?></p></li>
</ul>
<!-- 分頁導覽 -->
<nav class="pagination">
<a href="= $p['prev_url'] ?>>« 上一頁</a>
<span class="current">= $link['label'] ?></span>
<a href="= $link['url'] ?>>= $link['label'] ?></a>
<a href="= $p['next_url'] ?>>下一頁 »</a>
<span class="info">共 = $p['total'] ?> 筆 / 第 = $p['current_page'] ?>/= $p['total_pages'] ?> 頁</span>
</nav>
| 優化點 | 做法 |
|---|---|
| 避免 `OFFSET` 深分頁效能劣化 | 游標分頁:`WHERE id > :last_id ORDER BY id LIMIT 15` (需唯一遞增 ID) |
| COUNT(*) 慢 | 資料表大時:快取總數、近似值 `EXPLAIN`、觸發器維護計數表、或捨棄總頁數改「載入更多」 |
| 重複查詢 | 同一請求多次分頁共用 COUNT 結果;Redis 快取熱門頁面 HTML/JSON |
| 大量資料匯出 | 不可用分頁逐頁查,改用 Unbuffered Query / Cursor / 分批生成 CSV |
分頁看似簡單,實則涉及 SQL 效能、URL 狀態管理、前端狀態同步、無障礙存取 (ARIA) 等細節。封裝可複用的 `Paginator` 類別,配合前端組件化,能在 SSR 與 SPA 雙模式下保持一致體驗,是每個 PHP 專案的基礎建設之一。