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 theFETCH
andOFFSET
clauses to perform a pagination.