分頁列表是後台管理、數據展示的標配功能。本文提供從數據庫分頁查詢、頁碼計算、URL參數保持、模板分離的完整MVC風格實現,並優化大數據量深分頁性能。
<?php
// config.php
const PAGE_SIZE = 15;
const PAGE_LINKS = 7; // 頁碼鏈接顯示數量(奇數)
// list.php (Controller)
$page = max(1, (int)($_GET['page'] ?? 1));
$where = []; $params = [];
// 構建WHERE條件(示例:關鍵詞搜索)
if ($kw = trim($_GET['kw'] ?? '')) {
$where[] = '(FirstName LIKE ? OR LastName LIKE ?)';
$params[] = "%$kw%"; $params[] = "%$kw%";
}
$whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
// 1. 獲取總數(單獨查詢,可緩存)
$countSql = "SELECT COUNT(*) FROM persons $whereSql";
$stmt = $pdo->prepare($countSql);
$stmt->execute($params);
$total = $stmt->fetchColumn();
$totalPages = (int)ceil($total / PAGE_SIZE);
$page = min($page, max(1, $totalPages));
// 2. 獲取當前頁數據(優化:延遲關聯/覆蓋索引)
$offset = ($page - 1) * PAGE_SIZE;
$dataSql = "SELECT id, FirstName, LastName FROM persons $whereSql ORDER BY id LIMIT ?, ?";
$stmt = $pdo->prepare($dataSql);
$stmt->bindValue(1, $offset, PDO::PARAM_INT);
$stmt->bindValue(2, PAGE_SIZE, PDO::PARAM_INT);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 3. 頁碼範圍計算
$half = (PAGE_LINKS - 1) / 2;
$start = max(1, $page - $half);
$end = min($totalPages, $start + PAGE_LINKS - 1);
if ($end - $start + 1 < PAGE_LINKS) {
$start = max(1, $end - PAGE_LINKS + 1);
}
// 4. 構建頁碼鏈接(保持其他GET參數)
function page_url(int $p, array $params = []): string {
$params['page'] = $p;
return '?' . http_build_query($params);
}
$pageLinks = [];
if ($page > 1) $pageLinks[] = ['url' => page_url(1), 'text' => '首頁', 'active' => false];
if ($page > 1) $pageLinks[] = ['url' => page_url($page-1), 'text' => '上一頁', 'active' => false];
for ($i = $start; $i <= $end; $i++) {
$pageLinks[] = ['url' => page_url($i), 'text' => (string)$i, 'active' => $i === $page];
}
if ($page < $totalPages) $pageLinks[] = ['url' => page_url($page+1), 'text' => '下一頁', 'active' => false];
if ($page < $totalPages) $pageLinks[] = ['url' => page_url($totalPages), 'text' => '尾頁', 'active' => false];
// 5. 傳遞給視圖
include 'list_view.php';
<table class="table table-striped">
<thead><tr><th>ID</th><th>名字</th></tr></thead>
<tbody>
<?php foreach ($rows as $row): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= htmlspecialchars($row['FirstName'] . ' ' . $row['LastName']) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<nav aria-label="分頁導航">
<ul class="pagination">
<?php foreach ($pageLinks as $link): ?>
<li class="page-item <?= $link['active'] ? 'active' : '' ?>">
<a class="page-link" href="<?= $link['active'] ? 'javascript:;' : htmlspecialchars($link['url']) ?>">
<?= $link['text'] ?>
</a>
</li>
<?php endforeach; ?>
</ul>
<p class="text-muted">共 <?= $total ?> 條,第 <?= $page ?> / <?= $totalPages ?> 頁</p>
</nav>
WHERE id > $lastId ORDER BY id LIMIT 15,避免OFFSET掃描開銷。INDEX idx_list (id, FirstName, LastName)實現Using index。COUNT(*)結果,定期異步更新。分離數據邏輯與視圖渲染,構建可維護、高性能的分頁組件。