PHP Pagination with Previous and Next Links

Previous and next links look like a small PHP problem until the database grows. Keep the database doing the slicing, validate the page number, bind the values through PDO, and treat deep pagination as a performance question.

Passing a raw start offset around feels more direct than calculating one from a page number. It works until someone sends a negative value, skips the first page, or pastes the variable into SQL without thinking. The fix is not complicated, it just needs to be deliberate.

Guide Path

Read PHP control flow first, because pagination is mostly input validation plus a database query. After this, the same pattern shows up in the PHP search tutorial, where the result set also needs a stable order and a safe page size.

Page Numbers

A raw offset is a database detail from the query. The browser URL should expose a page number because that is what people understand:

/articles?page=3

Read it as an integer and clamp it to page one or higher:

<?php

$page = filter_input(
    INPUT_GET,
    "page",
    FILTER_VALIDATE_INT,
    [
        "options" => [
            "default" => 1,
            "min_range" => 1,
        ],
    ],
);

$page = max(1, (int) $page);
$perPage = 10;
$offset = ($page - 1) * $perPage;

That gives the database the offset it needs without trusting the URL to provide a safe number.

Ask SQL for One Page

Do not fetch the full result set and slice it in PHP. Ask SQL for one page, using a stable sort order:

SELECT id, title, slug, published_at
FROM articles
WHERE published_at <= NOW()
ORDER BY published_at DESC, id DESC
LIMIT :limit OFFSET :offset;

Then bind the integer values through PDO before executing:

<?php

$statement = $pdo->prepare(
    "SELECT id, title, slug, published_at
     FROM articles
     WHERE published_at <= NOW()
     ORDER BY published_at DESC, id DESC
     LIMIT :limit OFFSET :offset",
);

$statement->bindValue("limit", $perPage, PDO::PARAM_INT);
$statement->bindValue("offset", $offset, PDO::PARAM_INT);
$statement->execute();

$articles = $statement->fetchAll();

A stable ORDER BY clause matters for predictable pagination. If rows share the same timestamp and the query has no secondary sort, page boundaries can shift while readers move through the list.

If this query belongs to a search screen, read building a search application with PHP and SQL next. Search pagination has the same mechanics, plus the extra cost of matching and ranking.

Next Page Detection

There are two common ways to decide whether the next link should show. The first option is a separate count query:

SELECT COUNT(*) AS total
FROM articles
WHERE published_at <= NOW();

That gives you page counts and "Page 3 of 12" style UI, but the count can be expensive on larger filtered result sets.

The second option fetches one extra row from the result:

<?php

$limit = $perPage + 1;

$statement->bindValue("limit", $limit, PDO::PARAM_INT);
$statement->bindValue("offset", $offset, PDO::PARAM_INT);
$statement->execute();

$rows = $statement->fetchAll();
$hasNext = count($rows) > $perPage;
$articles = array_slice($rows, 0, $perPage);

That is usually enough for previous and next links. You know whether another page exists without promising a total count that may be slow or stale.

Link Building

Keep existing filters when changing the page number. http_build_query() will encode the query string for you:

<?php

function pageUrl(int $page): string
{
    $query = $_GET;
    $query["page"] = $page;

    return "/articles?" . http_build_query($query);
}

Render previous and next links only when they make sense:

<?php if ($page > 1): ?>
    <a href="<?= htmlspecialchars(pageUrl($page - 1), ENT_QUOTES) ?>">Previous</a>
<?php endif; ?>

<?php if ($hasNext): ?>
    <a href="<?= htmlspecialchars(pageUrl($page + 1), ENT_QUOTES) ?>">Next</a>
<?php endif; ?>

The URL is encoded before it reaches the href, and the rendered attribute is escaped for HTML. That is the boundary discipline that keeps simple pagination from becoming a small injection bug.

Watch the Deep-page Cost

Offset pagination is fine for small and medium lists. The database still has to walk past skipped rows, though, so page 4,000 can be much more expensive than page 4.

When readers need to move through deep chronological lists, use keyset pagination instead. The URL carries the last seen position rather than a page number:

SELECT id, title, slug, published_at
FROM articles
WHERE
    published_at < :cursor_published_at
    OR (
        published_at = :cursor_published_at
        AND id < :cursor_id
    )
ORDER BY published_at DESC, id DESC
LIMIT :limit;

That shape follows the index instead of counting through all previous pages. It is less friendly for jumping to page 27, but it is better for endless lists, activity feeds, and large result sets.

The SQL indexes and query optimization guide covers why this works. Pagination is still a query-plan problem, even when the visible feature is just two links.

Keep Pagination Near the Database Boundary

Pagination belongs close to the query because the database owns the order, filters, and row limits. PHP should validate the request, bind the values, render the links, and escape the output.

That separation is the same one used in PHP and MySQL with PDO and building a small PHP database app. The page link is a tiny UI feature, but the correctness lives in the database boundary underneath it.

This example has been updated from the original CodeWalkers pagination tutorial, which put a raw start offset in the query string and pasted it into a MySQL LIMIT clause. The clamped page number and the bound PDO values replace both halves of that.

Frequently Asked Questions

Does LIMIT and OFFSET work the same in every database?

The keywords and behaviour are close across MySQL, PostgreSQL, and SQLite, but the exact syntax for combining them, and how each optimiser copes with a large offset, differ enough to test on the engine actually in use.

Should page numbers in a URL start at 0 or 1?

At 1. Readers count from one, and a URL saying page=1 for the first page matches what a person expects. Convert to the zero-based offset in the query rather than exposing it in the address.

What happens when someone requests a page past the last one?

The query returns zero rows rather than an error, because an offset beyond the result set is not invalid. Treat an empty result at a requested page as a normal case to handle, not a failure.

Is deep pagination good for search engines?

Not on its own. Pages deep in a sequence are reached slowly and compete with each other for the same terms. A sitemap, or a single view-all page for smaller lists, gives a clearer signal than relying on link-following.