Mike Lopez

Development

How to Build a Paginated PHP REST API With PDO

By Published

A simple API request flowing to an article response with pagination controls

A paginated PHP REST API needs more than LIMIT and OFFSET. Its public contract should define input limits, stable ordering, response metadata, navigation links, error shapes, and the behavior of empty or out-of-range pages.

This example implements a read-only GET /api/articles endpoint with plain PHP, PDO, and MySQL. It uses numbered pages because clients need direct navigation. For a large feed that only moves forward, cursor pagination may be a better fit.

The code targets PHP 8.2 or newer with pdo_mysql, MySQL 8.0 or newer, and an InnoDB table with this minimum shape:

CREATE TABLE articles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    excerpt TEXT NOT NULL,
    status VARCHAR(32) NOT NULL,
    published_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_articles_public_feed (status, published_at DESC, id DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Define the response contract

A successful response has one predictable shape:

{
  "data": [],
  "meta": {
    "page": 1,
    "per_page": 20,
    "total": 0,
    "total_pages": 0
  },
  "links": {
    "self": "https://example.test/api/articles?page=1&per_page=20",
    "first": "https://example.test/api/articles?page=1&per_page=20",
    "prev": null,
    "next": null,
    "last": null
  }
}

An empty collection is still a successful query, so return 200 with an empty data array. Invalid parameters receive 400. Unsupported methods receive 405 with an Allow header. An unexpected server failure receives 500 with a generic public message and a server-side correlation ID.

Create the PDO connection safely

Keep credentials outside source control. Fail with exceptions and disable emulated prepares so MySQL performs native parameter handling where supported.

<?php

declare(strict_types=1);

const MAXIMUM_PAGE = 100000;

/**
 * Creates the application's PDO connection from environment variables.
 */
function createPdo(): PDO
{
    $host = getenv('DB_HOST') ?: '127.0.0.1';
    $port = getenv('DB_PORT') ?: '3306';
    $database = getenv('DB_NAME');
    $username = getenv('DB_USER');
    $password = getenv('DB_PASSWORD');

    if (!$database || !$username || $password === false) {
        throw new RuntimeException('Database configuration is incomplete.');
    }

    return new PDO(
        sprintf('mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $host, $port, $database),
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
}

Do not send PDO exception messages to the client. They can expose schema, query, and infrastructure details.

Validate page parameters

Apply both a minimum and a maximum. A client should not be able to request millions of rows or create an integer overflow in the offset calculation.

<?php

declare(strict_types=1);

final readonly class PageRequest
{
    /**
     * Stores a validated numbered-page request.
     */
    public function __construct(
        public int $page,
        public int $perPage
    ) {
    }
}

/**
 * Reads a bounded positive integer from a query parameter.
 */
function queryInteger(array $query, string $name, int $default, int $maximum): int
{
    if (!array_key_exists($name, $query)) {
        return $default;
    }

    $value = filter_var(
        $query[$name],
        FILTER_VALIDATE_INT,
        ['options' => ['min_range' => 1, 'max_range' => $maximum]]
    );

    if ($value === false) {
        throw new InvalidArgumentException(sprintf('%s must be a positive integer.', $name));
    }

    return $value;
}

/**
 * Builds a validated page request from the query string.
 */
function readPageRequest(array $query): PageRequest
{
    return new PageRequest(
        queryInteger($query, 'page', 1, MAXIMUM_PAGE),
        queryInteger($query, 'per_page', 20, 100)
    );
}

The limits are part of your API policy. Publish them in the API documentation and test them.

Query with deterministic ordering

Order by a business field plus a unique tie-breaker. If two articles share a timestamp, ordering only by published_at does not guarantee which one comes first.

<?php

declare(strict_types=1);

final readonly class PageResult
{
    /**
     * Stores one page of article data and the matching total.
     *
     * @param list<array<string, mixed>> $items
     */
    public function __construct(
        public array $items,
        public int $total
    ) {
    }
}

/**
 * Fetches a numbered page of published articles.
 */
function fetchPublishedArticles(PDO $pdo, PageRequest $request): PageResult
{
    $totalStatement = $pdo->prepare(
        'SELECT COUNT(*) FROM articles WHERE status = :status'
    );
    $totalStatement->execute([':status' => 'published']);
    $total = (int) $totalStatement->fetchColumn();
    $offset = ($request->page - 1) * $request->perPage;

    $dataStatement = $pdo->prepare(
        'SELECT id, title, excerpt, published_at
         FROM articles
         WHERE status = :status
         ORDER BY published_at DESC, id DESC
         LIMIT :limit OFFSET :offset'
    );
    $dataStatement->bindValue(':status', 'published', PDO::PARAM_STR);
    $dataStatement->bindValue(':limit', $request->perPage, PDO::PARAM_INT);
    $dataStatement->bindValue(':offset', $offset, PDO::PARAM_INT);
    $dataStatement->execute();

    return new PageResult($dataStatement->fetchAll(), $total);
}

The count and data statements are separate reads. Under autocommit, an insert or delete between them can make total differ briefly from the returned page. If the contract requires both values from one database state, run the two plain SELECT statements inside a short InnoDB REPEATABLE READ transaction with a consistent snapshot. Otherwise document that the total is advisory and keep the endpoint read-only and retry-safe.

Run EXPLAIN ANALYZE with representative shallow and deep pages. The index and query must be measured against real distributions rather than assumed to be fast.

Build links without trusting request headers

Construct the configured public API URL on the server. Do not reflect an arbitrary Host header into response links unless a trusted proxy has already validated it.

<?php

declare(strict_types=1);

/**
 * Returns the canonical URL for one page of the collection.
 */
function pageUrl(string $baseUrl, int $page, int $perPage): string
{
    return $baseUrl . '?' . http_build_query(
        ['page' => $page, 'per_page' => $perPage],
        '',
        '&',
        PHP_QUERY_RFC3986
    );
}

/**
 * Builds pagination metadata and navigation links.
 *
 * @return array{meta:array<string,int>, links:array<string,?string>}
 */
function paginationDocument(
    string $baseUrl,
    PageRequest $request,
    int $total
): array {
    $totalPages = $total === 0
        ? 0
        : min(MAXIMUM_PAGE, (int) ceil($total / $request->perPage));
    $lastPage = max(1, $totalPages);

    return [
        'meta' => [
            'page' => $request->page,
            'per_page' => $request->perPage,
            'total' => $total,
            'total_pages' => $totalPages,
        ],
        'links' => [
            'self' => pageUrl($baseUrl, $request->page, $request->perPage),
            'first' => pageUrl($baseUrl, 1, $request->perPage),
            'prev' => $request->page > 1
                ? pageUrl($baseUrl, $request->page - 1, $request->perPage)
                : null,
            'next' => $request->page < $totalPages
                ? pageUrl($baseUrl, $request->page + 1, $request->perPage)
                : null,
            'last' => $totalPages > 0
                ? pageUrl($baseUrl, $lastPage, $request->perPage)
                : null,
        ],
    ];
}

MAXIMUM_PAGE is shared by request validation and link generation. total still reports the matching row count, while total_pages reports the part of the numbered collection this API permits clients to navigate. No emitted next or last URL can contain a page that readPageRequest() rejects.

You may also expose navigation through the HTTP Link header. RFC 8288 defines typed web links such as next and prev. Keeping links in the JSON body is often more convenient for clients, and supporting both can be useful when the contract documents both representations.

Send consistent JSON and errors

Centralize response serialization so every path sets the same content type and encoding behavior.

<?php

declare(strict_types=1);

/**
 * Sends a JSON response and stops request processing.
 */
function sendJson(int $status, array $document, array $headers = []): never
{
    http_response_code($status);
    header('Content-Type: application/json; charset=utf-8');

    foreach ($headers as $name => $value) {
        header($name . ': ' . $value);
    }

    echo json_encode(
        $document,
        JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES
    );
    exit;
}

/**
 * Creates the public representation of an API error.
 *
 * @return array{error:array{code:string, message:string, request_id:string}}
 */
function errorDocument(string $code, string $message, string $requestId): array
{
    return [
        'error' => [
            'code' => $code,
            'message' => $message,
            'request_id' => $requestId,
        ],
    ];
}

RFC 9110 is the current reference for HTTP method and status semantics.

Wire the endpoint together

The endpoint rejects unsupported methods, validates parameters, returns the page, and keeps internal failures out of the response.

<?php

declare(strict_types=1);

$requestId = bin2hex(random_bytes(12));
header('X-Request-ID: ' . $requestId);

try {
    if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'GET') {
        sendJson(
            405,
            errorDocument('method_not_allowed', 'Only GET is supported.', $requestId),
            ['Allow' => 'GET']
        );
    }

    $pageRequest = readPageRequest($_GET);
    $result = fetchPublishedArticles(createPdo(), $pageRequest);
    $pagination = paginationDocument(
        'https://api.example.test/api/articles',
        $pageRequest,
        $result->total
    );

    sendJson(
        200,
        [
            'data' => $result->items,
            'meta' => $pagination['meta'],
            'links' => $pagination['links'],
        ]
    );
} catch (InvalidArgumentException $exception) {
    sendJson(
        400,
        errorDocument('invalid_request', $exception->getMessage(), $requestId)
    );
} catch (Throwable $exception) {
    error_log(sprintf('[%s] %s', $requestId, $exception));
    sendJson(
        500,
        errorDocument('server_error', 'The request could not be completed.', $requestId)
    );
}

In a framework, exception middleware would normally own this concern. The plain-PHP example keeps it visible so the contract is complete.

Test the API contract

Integration tests should cover:

  • Default and maximum page sizes
  • Invalid strings, zero, negatives, floats, arrays, and oversized values
  • Empty collections
  • First, middle, final, and beyond-final pages
  • Duplicate publication timestamps
  • JSON encoding of multibyte content
  • Database failure without detail leakage
  • Unsupported methods and the Allow header
  • Link correctness behind the production proxy
  • Every non-null generated link parses to a request accepted by readPageRequest(), including the maximum page boundary
  • Authorization if the collection is not public

Decide how beyond-final pages behave. This example returns 200 with an empty list, which is simple and consistent. An API may instead return 404, but that choice must be documented and tested.

Know when to switch to cursors

OFFSET supports numbered pages and direct jumps, but a deep offset becomes increasingly expensive and can shift when rows are inserted between requests. Cursor pagination uses the last stable ordering key as the next starting point.

Switch when clients naturally move forward, result sets are large or active, and exact total pages are not essential. Keep numbered pages when direct navigation and totals are central to the product.

Whichever model you choose, the surrounding requirements stay the same: validated inputs, stable order, bounded work, secure database access, clear status semantics, and a response contract clients can rely on.