PHP SQL Server: Pagination

Summary: in this tutorial, you will learn how to perform pagination that breaks a large data set into smaller pages.

This tutorial begins where the selecting data from a table using PHP tutorial left off.

Paginating result set #

Step 1. Define a new method in the AuthorDB class that paginates data from the Authors table:

function paginate(int $limit, int $offset=0)
{
    $sql = 'SELECT * FROM Authors
                ORDER BY FirstName
                OFFSET :offset ROWS
                FETCH NEXT :limit ROWS ONLY';
    $stmt = $this->conn->prepare($sql);
    $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
    $stmt->execute();
    $authors = [];
    while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
        $authors[] = new Author($row['FirstName'], $row['LastName'], $row['BirthDate'], $row['AuthorID']);
    }
    return $authors;
}

First, define a method called findById that takes $authorId as the parameter:

function findById(int $authorId)

Second, construct a SELECT statement that retrieves a number of rows from all columns of the Authors table using the FETCH and OFFSET clauses:

$sql = 'SELECT * FROM Authors
        ORDER BY FirstName
        OFFSET :offset ROWS
        FETCH NEXT :limit ROWS ONLY';

In the query, the :offset and :limit are named placeholders. Using a parameterized query with named placeholders can help prevent SQL injection.

Third, create a prepared statement for executing the SELECT statement:

$stmt = $this->conn->prepare($sql);

Fourth, bind values from the $limit and $offset variables to the :limit and :offset parameters:

$stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);

Fifth, execute the prepared statement:

$stmt->execute();

Sixth, fetch rows from the result set as an associative array with the column names as keys, create an Author object from each row, and add the Author object to the $authors array:

$authors = [];
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
   $authors[] = new Author($row['FirstName'], $row['LastName'], $row['BirthDate'], $row['AuthorID']);
}

Note that the \PDO::FETCH_ASSOC fetch mode instructs PDO to return the result set as an associative array.

Finally, return the $authors array:

return $authors;

Step 2. Modify the index.php to use the paginate() method:

<?php

require_once 'authordb.php';

// Connect to the SQL Server
$conn = require_once 'connect.php';

$authorDB = new AuthorDB($conn);

// Retrieve the first 5 authors sorted by first name
$authors = $authorDB->paginate(5, 0);
foreach ($authors as $author) {
    echo $author->getFirstName() . ' ' . $author->getLastName() . '<br>';
}

How it works.

First, include the authordb.php to ensure the AuthorDB class is available in the script:

require_once 'authordb.php';

Second, connect to the SQL Server and assign the PDO object to $conn variable:

$conn = require_once 'connect.php';

Third, create a new AuthorDB object with the $conn object:

$authorDB = new AuthorDB($conn);

Fourth, retrieve the first five authors from the Authors table sorted by the author’s first names:

$authors = $authorDB->paginate(5, 0);

Finally, display the first and last names of each author:

foreach ($authors as $author) {
    echo $author->getFirstName() . ' ' . $author->getLastName() . '<br>';
}

Step 3. Run the index.php on the web browser.

It should return the first five authors sorted by first name:

Alexander Turner
Ann Kelly
Anthony Pham
Barbara Lopez
Charles Nicholson
...

If you want to get the next five authors, you need to change the offset parameter:

$authors = $authorDB->paginate(5, 5);

Output:

Craig Hart
Daniel Wilson
David Hunter
Donna Bailey
Jade Tate
...

Summary #

Execute the SELECT statement with the FETCH and OFFSET clauses to perform a pagination.

Was this tutorial helpful?