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;
}Code language: PHP (php)

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

function findById(int $authorId)Code language: PHP (php)

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';Code language: PHP (php)

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);Code language: PHP (php)

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);Code language: PHP (php)

Fifth, execute the prepared statement:

$stmt->execute();Code language: PHP (php)

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']);
}Code language: PHP (php)

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;Code language: PHP (php)

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>';
}Code language: PHP (php)

How it works.

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

require_once 'authordb.php';Code language: PHP (php)

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

$conn = require_once 'connect.php';Code language: PHP (php)

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

$authorDB = new AuthorDB($conn);Code language: PHP (php)

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

$authors = $authorDB->paginate(5, 0);Code language: PHP (php)

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

foreach ($authors as $author) {
    echo $author->getFirstName() . ' ' . $author->getLastName() . '<br>';
}Code language: PHP (php)

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
...Code language: PHP (php)

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

$authors = $authorDB->paginate(5, 5);Code language: PHP (php)

Output:

Craig Hart
Daniel Wilson
David Hunter
Donna Bailey
Jade Tate
...Code language: PHP (php)

Summary

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