基礎分頁邏輯

<?php
// 配置
$perPage = 10;
$page = max(1, (int)($_GET['page'] ?? 1)); // 當前頁，最小 1

// 1. 取得總筆數
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass');
$total = $pdo->query('SELECT COUNT(*) FROM messages')->fetchColumn();
$totalPages = (int)ceil($total / $perPage);

// 2. 修正頁碼範圍
$page = min($page, max(1, $totalPages));

// 3. 計算偏移量
$offset = ($page - 1) * $perPage;

// 4. 查詢當頁資料
$stmt = $pdo->prepare('SELECT * FROM messages ORDER BY created_at DESC LIMIT ?, ?');
$stmt->bindValue(1, $offset, PDO::PARAM_INT);
$stmt->bindValue(2, $perPage, PDO::PARAM_INT);
$stmt->execute();
$items = $stmt->fetchAll();

// 5. 輸出資料
foreach ($items as $item) {
    echo "<div>{$item['title']} - {$item['content']}</div>";
}

// 6. 產生分頁連結
$baseUrl = strtok($_SERVER['REQUEST_URI'], '?'); // 移除查詢字串
$params = $_GET;
unset($params['page']);

function pageLink(int $p, int $current, string $base, array $params): string {
    $params['page'] = $p;
    $url = $base . '?' . http_build_query($params);
    $class = $p === $current ? ' class="active"' : '';
    return "<a href=\"$url\"$class>$p</a>";
}

echo '<div class="pagination">';
if ($page > 1) echo pageLink($page - 1, $page, $baseUrl, $params) . ' '; // 上一頁

// 數字頁碼：顯示當前頁前後各 2 頁
$start = max(1, $page - 2);
$end = min($totalPages, $page + 2);
for ($i = $start; $i <= $end; $i++) {
    echo pageLink($i, $page, $baseUrl, $params) . ' ';
}

if ($page < $totalPages) echo pageLink($page + 1, $page, $baseUrl, $params); // 下一頁
echo '</div>';
?>

分頁樣式 CSS (簡易)

<style>
.pagination { margin: 20px 0; text-align: center; }
.pagination a { display: inline-block; padding: 6px 12px; margin: 0 2px; border: 1px solid #ddd; color: #333; text-decoration: none; border-radius: 3px; }
.pagination a:hover { background: #f5f5f5; }
.pagination a.active { background: #007bff; color: #fff; border-color: #007bff; cursor: default; }
.pagination span.disabled { color: #999; cursor: not-allowed; }
</style>

進階：游標分頁 (適用大數據、無限滾動)

<?php
// 基於唯一遞增 ID (或 created_at + id) 的游標分頁
$cursor = $_GET['cursor'] ?? null; // 上一頁最後一筆的 ID
$limit = 20;

$query = 'SELECT * FROM messages WHERE 1=1';
$params = [];

if ($cursor) {
    $query .= ' AND id < ?'; // 向舊資料翻頁
    $params[] = $cursor;
}
$query .= ' ORDER BY id DESC LIMIT ?';
$params[] = $limit + 1; // 多取一筆判斷是否有下一頁

$stmt = $pdo->prepare($query);
$stmt->execute($params);
$items = $stmt->fetchAll();

$hasMore = count($items) > $limit;
if ($hasMore) array_pop($items); // 移除多取的那筆

$nextCursor = $hasMore ? end($items)['id'] : null;

// API 回應
header('Content-Type: application/json');
echo json_encode([
    'data' => $items,
    'next_cursor' => $nextCursor,
    'has_more' => $hasMore,
]);
?>

效能優化關鍵點

1. 索引覆蓋：WHERE 和 ORDER BY 欄位建立複合索引
2. 避免 OFFSET 深分頁：LIMIT 100000, 10 需掃描 10 萬行，改用游標分頁或延遲關聯：
   SELECT * FROM t WHERE id IN (SELECT id FROM t ORDER BY id LIMIT 100000, 10)
3. 快取總數：COUNT(*) 慢時用 Redis 快取、資料庫觸發器維護計數表、或估算值
4. 靜態化：首頁、熱門列表靜態生成 HTML，定時更新

前端整合：AJAX 無限滾動

<script>
let loading = false, cursor = null, hasMore = true;
const container = document.getElementById('list');

async function loadMore() {
    if (loading || !hasMore) return;
    loading = true;
    const url = new URL('/api/messages', location.origin);
    if (cursor) url.searchParams.set('cursor', cursor);
    
    const res = await fetch(url);
    const {data, next_cursor, has_more} = await res.json();
    
    data.forEach(item => {
        const div = document.createElement('div');
        div.textContent = item.title + ' - ' + item.content;
        container.appendChild(div);
    });
    
    cursor = next_cursor;
    hasMore = has_more;
    loading = false;
}

// 滾動觸發
window.addEventListener('scroll', () => {
    if (window.innerHeight + window.scrollY >= document.body.offsetHeight - 500) {
        loadMore();
    }
});

loadMore(); // 首屏
</script>