{"id":4408,"date":"2024-07-31T07:46:13","date_gmt":"2024-07-31T00:46:13","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4408"},"modified":"2024-08-18T10:12:42","modified_gmt":"2024-08-18T03:12:42","slug":"php-sql-server-pagination","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-sql-server-pagination\/","title":{"rendered":"PHP SQL Server: Pagination"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to perform pagination that breaks a large data set into smaller pages.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0selecting data from a table using PHP tutorial<\/a> left off.<\/p>\n\n\n\n

Paginating result set #<\/a><\/h2>\n\n\n\n

Step 1. Define a new method in the AuthorDB<\/code> class that paginates data from the Authors<\/code> table:<\/p>\n\n\n

function<\/span> paginate<\/span>(int $limit, int $offset=0<\/span>)<\/span>\n<\/span>{\n    $sql = 'SELECT * FROM Authors\n                ORDER BY FirstName\n                OFFSET :offset ROWS\n                FETCH NEXT :limit ROWS ONLY'<\/span>;\n    $stmt = $this<\/span>->conn->prepare($sql);\n    $stmt->bindValue(':limit'<\/span>, $limit, \\PDO::PARAM_INT);\n    $stmt->bindValue(':offset'<\/span>, $offset, \\PDO::PARAM_INT);\n    $stmt->execute();\n    $authors = [];\n    while<\/span> ($row = $stmt->fetch(\\PDO::FETCH_ASSOC)) {\n        $authors[] = new<\/span> Author($row['FirstName'<\/span>], $row['LastName'<\/span>], $row['BirthDate'<\/span>], $row['AuthorID'<\/span>]);\n    }\n    return<\/span> $authors;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
First, define a method called findById<\/code> that takes $authorId<\/code> as the parameter:<\/p>\n\n\n
function<\/span> findById<\/span>(int $authorId)<\/span><\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$sql = 'SELECT * FROM Authors\n        ORDER BY FirstName\n        OFFSET :offset ROWS\n        FETCH NEXT :limit ROWS ONLY'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
In the query, the :offset<\/code> and :limit<\/code> are named placeholders. Using a parameterized query with named placeholders can help prevent SQL injection.<\/p>\n\n\n\n
Third, create a prepared statement for executing the SELECT<\/code> statement:<\/p>\n\n\n
$stmt = $this<\/span>->conn->prepare($sql);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, bind values from the $limit<\/code> and $offset<\/code> variables to the :limit<\/code> and :offset<\/code> parameters:<\/p>\n\n\n
$stmt->bindValue(':limit'<\/span>, $limit, \\PDO::PARAM_INT);\n$stmt->bindValue(':offset'<\/span>, $offset, \\PDO::PARAM_INT);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, execute the prepared statement:<\/p>\n\n\n
$stmt->execute();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, fetch rows from the result set as an associative array with the column names as keys, create an Author<\/code> object from each row, and add the Author<\/code> object to the $authors<\/code> array:<\/p>\n\n\n
$authors = [];\nwhile<\/span> ($row = $stmt->fetch(\\PDO::FETCH_ASSOC)) {\n   $authors[] = new<\/span> Author($row['FirstName'<\/span>], $row['LastName'<\/span>], $row['BirthDate'<\/span>], $row['AuthorID'<\/span>]);\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the \\PDO::FETCH_ASSOC<\/code> fetch mode instructs PDO to return the result set as an associative array.<\/p>\n\n\n\n
Finally, return the $authors array:<\/p>\n\n\n
return<\/span> $authors;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the index.php<\/code> to use the paginate()<\/code> method:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'authordb.php'<\/span>;\n\n\/\/ Connect to the SQL Server<\/span>\n$conn = require_once<\/span> 'connect.php'<\/span>;\n\n$authorDB = new<\/span> AuthorDB($conn);\n\n\/\/ Retrieve the first 5 authors sorted by first name<\/span>\n$authors = $authorDB->paginate(5<\/span>, 0<\/span>);\nforeach<\/span> ($authors as<\/span> $author) {\n    echo<\/span> $author->getFirstName() . ' '<\/span> . $author->getLastName() . '<br>'<\/span>;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, include the authordb.php<\/code> to ensure the AuthorDB<\/code> class is available in the script:<\/p>\n\n\n
require_once<\/span> 'authordb.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, connect to the SQL Server and assign the PDO<\/code> object to $conn<\/code> variable:<\/p>\n\n\n
$conn = require_once<\/span> 'connect.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, create a new AuthorDB<\/code> object with the $conn<\/code> object:<\/p>\n\n\n
$authorDB = new<\/span> AuthorDB($conn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, retrieve the first five authors from the Authors<\/code> table sorted by the author’s first names:<\/p>\n\n\n
$authors = $authorDB->paginate(5<\/span>, 0<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, display the first and last names of each author:<\/p>\n\n\n
foreach<\/span> ($authors as<\/span> $author) {\n    echo<\/span> $author->getFirstName() . ' '<\/span> . $author->getLastName() . '<br>'<\/span>;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the index.php<\/code> on the web browser.<\/p>\n\n\n\n
It should return the first five authors sorted by first name:<\/p>\n\n\n
Alexander Turner\nAnn Kelly\nAnthony Pham\nBarbara Lopez\nCharles Nicholson\n...<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to get the next five authors, you need to change the offset<\/code> parameter:<\/p>\n\n\n
$authors = $authorDB->paginate(5<\/span>, 5<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Craig Hart\nDaniel Wilson\nDavid Hunter\nDonna Bailey\nJade Tate\n...<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute the SELECT<\/code> statement with the FETCH<\/code> and OFFSET<\/code> clauses to perform a pagination.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, construct a SELECT statement<\/a> that retrieves a number of rows from all columns of the Authors<\/code> table using the FETCH and OFFSET<\/a> clauses:<\/p>\n\n\n