Simple PHP Pagination script

php

pagination

database

sql

byAnton Shumikhin·Dec 27, 2024

To setup pagination, we slave drive SQL using LIMIT :perPage OFFSET :offset to restrict amount of data served per page:

// Hustle your current page number from URL.. or slum it at page one.
$page = max(1, (int) ($_GET['page']));
$perPage = 10;
$offset = ($page - 1) * $perPage;

// $pdo = new PDO('mysql:host=your_host;dbname=your_db', 'username', 'password');

$stmt = $pdo->prepare("SELECT * FROM table_name LIMIT :perPage OFFSET :offset");
$stmt->execute([':perPage' => $perPage, ':offset' => $offset]);
$results = $stmt->fetchAll();

// For pagination links... watch this space!

You'll need to replace table_name and tailor the PDO connection to your database. This chunk of code swiftly handles pagination, bamboozling out perPage records at a time.

Building the pagination roadmap

Calculation and validation: The prelims

Before we get painting our pagination panel, we need a total tally of the pages. This requires a quick sweep over the database to count all rows, then summoning ceil() to work out the pages:

$stmt = $pdo->query("SELECT COUNT(*) FROM table_name");
$rowsCount = $stmt->fetchColumn();

$totalPages = ceil($rowsCount / $perPage);

Make sure you don't trip over the current page number. Use min() to shield against requests trespassing beyond the number of total pages:

$page = min($totalPages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
    'options' => array(
        'default'   => 1,
        'min_range' => 1,
    ),
)));

Whipping up the pagination links

With the current page and total breadcrumb trail, let's craft navigational links. Opt for showing only a handful of links—don't snowball your users:

$start = max(1, $page - 5);
$end = min($totalPages, $page + 5);

for ($i = $start; $i <= $end; $i++) {
    // Because everything's better with a link!
    echo "<a href='script.php?page=" . $i . "'" . ($page == $i ? " class='active'" : "") . ">" . $i . "</a> ";
}

Craft context-based Prev and Next links like a master illusionist while maintaining the illusion that the current page link isn't a link:

if($page > 1){
    echo "<a href='script.php?page=" . ($page - 1) . "'>Prev</a> ";
}

// Our current page magically becomes a label!

if($page < $totalPages){
    echo "<a href='script.php?page=" . ($page + 1) . "'>Next</a> ";
}

Securing queries and boosting performance: Operation overdrive

When forming the query strings, safety is not just a protocol, it's the heart of matters. Power PDO prepared statements with PDO::PARAM_INT for bulletproof SQL execution.

Embrace exceptions and errors for what they really are—rare pets! Groom them into giving the user wholesome feedback.

Ensure SQL queries incorporate proper LIMIT clauses, because fetching unlimited data is as bad as unlimited pizza...Alright maybe not 🍕

Let the data flow

Using while or foreach loops, allow the data from your paged query to flow out:

foreach ($results as $row) {
    // It's raining data! 
    echo "<p>" . htmlspecialchars($row['column_name']) . "</p>";
}

In the spirit of duality, consider placing pagination controls both at the top and bottom of your data table for a tad easier scrolling.

Let online tutorials and docs be your Sherpa to refine this basic pagination into an unstoppable beast.

Powering up pagination

Load balancing: A balancing act

To enhance performance, keep the row count reined within bearable limits. Use indices on columns that frequently flirt with sorting or filtration conditions in paginated data.

Sprucing up the pagination: UX on steriods

To supercharge user experience consider displaying "First" and "Last" page links, especially when the user is a few pages adrift from either end.

// First, last and ellipsis play smooth operators in pagination
echo "<a href='script.php?page=1'>First</a> ";
echo "...";
echo "<a href='script.php?page=" . $totalPages . "'>Last</a> ";