How to Build Simple Pagination with PHP

When displaying a large amount of data from a database, loading it all at once on a single page isn't a good choice — the page becomes slow and users struggle to navigate. The solution is pagination: dividing data into several pages with a certain number of rows per page. In this tutorial, we'll build pagination from scratch using native PHP and MySQL.

The Basic Concept of Pagination

Pagination works by using the SQL LIMIT and OFFSET clauses:

• LIMIT — the number of rows retrieved per page (for example 10).
• OFFSET — how many rows to skip before starting to retrieve data.

For example, for page 3 with 10 items per page: LIMIT 10 OFFSET 20 (skip the first 20 rows, take the next 10). The formula: OFFSET = (current_page - 1) * items_per_page.

Preparing the Database

Make sure you have a table with plenty of data. For testing, use a simple articles table:

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    date DATE NOT NULL
);

-- Fill it with dummy data (run several times)
INSERT INTO articles (title, content, date) VALUES
    ('PHP Article 1', 'Article content 1...', '2024-01-01'),
    ('PHP Article 2', 'Article content 2...', '2024-01-02'),
    ('PHP Article 3', 'Article content 3...', '2024-01-03');
    -- Add up to 50+ rows for a good test

Database Connection File

<?php
// db.php
$dsn = "mysql:host=localhost;dbname=database_name;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
$pdo = new PDO($dsn, 'root', '', $options);

The PHP Pagination Logic

Create an article_list.php file with the complete pagination logic:

<?php
require 'db.php';

// Pagination configuration
$per_page = 10; // number of items per page

// Get the page number from the URL, default to 1
$page = max(1, (int) ($_GET['page'] ?? 1));

// Count the total rows
$total_rows = (int) $pdo->query("SELECT COUNT(*) FROM articles")->fetchColumn();

// Calculate the total pages
$total_pages = (int) ceil($total_rows / $per_page);

// Make sure the page doesn't exceed the total
$page = min($page, max(1, $total_pages));

// Calculate the offset
$offset = ($page - 1) * $per_page;

// Retrieve data for this page
$stmt = $pdo->prepare(
    "SELECT id, title, date FROM articles ORDER BY date DESC LIMIT :limit OFFSET :offset"
);
$stmt->bindValue(':limit',  $per_page, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset,   PDO::PARAM_INT);
$stmt->execute();
$articles = $stmt->fetchAll();
?>

<!-- Display the data -->
<h2>Article List (Page <?= $page ?> of <?= $total_pages ?>)</h2>
<p>Total: <?= $total_rows ?> articles</p>

<ul>
<?php foreach ($articles as $item): ?>
  <li>
    <a href="article.php?id=<?= $item['id'] ?>">
      <?= htmlspecialchars($item['title']) ?>
    </a>
    <small>— <?= $item['date'] ?></small>
  </li>
<?php endforeach; ?>
</ul>

Note the use of bindValue() with PDO::PARAM_INT for LIMIT and OFFSET — this is required because integer values can't be parameterized with a plain ? in some PDO configurations.

Building the Page Navigation

Add the following code after the article list to display navigation links:

<?php
function buildPaginationLinks(int $page, int $total_pages, string $base_url = '?'): string
{
    $html = '<nav><ul style="list-style:none; display:flex; gap:8px;">';

    // "Previous" button
    if ($page > 1) {
        $prev = $page - 1;
        $html .= "<li><a href='{$base_url}page=$prev'>&laquo; Previous</a></li>";
    }

    // Page numbers (show a maximum of 5 pages around the active page)
    $start = max(1, $page - 2);
    $end   = min($total_pages, $page + 2);

    if ($start > 1) {
        $html .= "<li><a href='{$base_url}page=1'>1</a></li>";
        if ($start > 2) {
            $html .= "<li><span>...</span></li>";
        }
    }

    for ($i = $start; $i <= $end; $i++) {
        $active = ($i === $page) ? ' style="font-weight:bold;"' : '';
        $html .= "<li><a href='{$base_url}page=$i'$active>$i</a></li>";
    }

    if ($end < $total_pages) {
        if ($end < $total_pages - 1) {
            $html .= "<li><span>...</span></li>";
        }
        $html .= "<li><a href='{$base_url}page=$total_pages'>$total_pages</a></li>";
    }

    // "Next" button
    if ($page < $total_pages) {
        $next = $page + 1;
        $html .= "<li><a href='{$base_url}page=$next'>Next &raquo;</a></li>";
    }

    $html .= '</ul></nav>';
    return $html;
}

// Display the navigation
echo buildPaginationLinks($page, $total_pages);

Pagination with a Search Filter

To combine pagination with a search feature, make sure the search parameter is carried through in every page link:

<?php
$keyword  = htmlspecialchars(trim($_GET['q'] ?? ''));
$per_page = 10;
$page     = max(1, (int) ($_GET['page'] ?? 1));

// Query with a search filter
$stmt_count = $pdo->prepare("SELECT COUNT(*) FROM articles WHERE title LIKE :q");
$stmt_count->execute([':q' => "%$keyword%"]);
$total_rows = (int) $stmt_count->fetchColumn();

$total_pages = (int) ceil($total_rows / $per_page);
$offset      = ($page - 1) * $per_page;

$stmt = $pdo->prepare(
    "SELECT id, title, date FROM articles WHERE title LIKE :q ORDER BY date DESC LIMIT :limit OFFSET :offset"
);
$stmt->bindValue(':q',      "%$keyword%", PDO::PARAM_STR);
$stmt->bindValue(':limit',  $per_page,    PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset,      PDO::PARAM_INT);
$stmt->execute();
$articles = $stmt->fetchAll();

// The base URL includes the search parameter
$base_url = "?q=" . urlencode($keyword) . "&";
echo buildPaginationLinks($page, $total_pages, $base_url);

Conclusion

Simple pagination with PHP and MySQL only requires a few basic concepts: counting the total rows, determining the offset based on the active page, using LIMIT and OFFSET in the SQL query, then building navigation links that include the page parameter in the URL. The pagination function you built can be reused on many pages. From this foundation, you can develop it further with AJAX for a smoother experience.