PHP PDO Pagination using a MySQL Stored Procedure

This example paginates the student table with one MySQL stored procedure. The procedure returns the requested page of records as its first result set and the total record count as its second result set.

CREATE PROCEDURE GetStudentsAndCount(
    IN p_start INT,
    IN p_limit INT
)
BEGIN
    SELECT id,name,class,mark,gender
    FROM student
    ORDER BY id
    LIMIT p_start,p_limit;

    SELECT COUNT(*) AS total
    FROM student;
END

The LIMIT clause selects one page of records, while COUNT() supplies the total needed to calculate the number of pages. An ORDER BY clause keeps page results in a predictable order.

Create the MySQL Pagination Stored Procedure Top ↑

The procedure accepts two integer parameters:

• p_start - the zero-based starting row.
• p_limit - the maximum number of records to return.

DELIMITER $$

CREATE PROCEDURE GetStudentsAndCount(
    IN p_start INT,
    IN p_limit INT
)
BEGIN
    SELECT id,name,class,mark,gender
    FROM student
    ORDER BY id
    LIMIT p_start,p_limit;

    SELECT COUNT(*) AS total
    FROM student;
END$$

DELIMITER ;

The first SELECT returns only the required records. The second SELECT returns one row containing the complete table count.

Calculate the Current Page, Limit and Start Top ↑

The page number normally arrives through the URL:

?page=3

Validate it as a positive integer before using it to calculate the starting record.

$limit=5;

$page=filter_input(
    INPUT_GET,
    'page',
    FILTER_VALIDATE_INT,
    [
        'options' => [
            'min_range' => 1
        ]
    ]
);

if($page===false || $page===null){
    $page=1;
}

$start=($page-1)*$limit;

With five records per page:

Call the Stored Procedure using PDO Top ↑

The PDO connection object $dbo is supplied by the config.php connection file.

<?php
require 'config.php';

$stmt=$dbo->prepare(
    "CALL GetStudentsAndCount(:start,:limit)"
);

$stmt->bindValue(
    ':start',
    $start,
    PDO::PARAM_INT
);

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

$stmt->execute();

The page and limit values are integers calculated by the application and passed separately to the stored procedure.

Read Both Result Sets with nextRowset() Top ↑

The stored procedure returns two result sets. First collect the student records:

$students=$stmt->fetchAll(
    PDO::FETCH_ASSOC
);

Then move to the second result set:

if($stmt->nextRowset()){
    $total_records=(int)$stmt->fetchColumn();
}else{
    $total_records=0;
}

After all required result sets have been read, close the cursor:

$stmt->closeCursor();

This is particularly useful with MySQL stored procedures because it releases any remaining result-set resources before another statement is executed on the same connection.

Calculate the Number of Pages Top ↑

$total_pages=max(
    1,
    (int)ceil(
        $total_records/$limit
    )
);

For 23 records with five records per page:

ceil(23 / 5) = 5 pages

Display the Student Records Safely Top ↑

Database values should be escaped when inserted into HTML output.

if($students){
    foreach($students as $student){
        $name=htmlspecialchars(
            (string)$student['name'],
            ENT_QUOTES,
            'Windows-1252'
        );

        $class=htmlspecialchars(
            (string)$student['class'],
            ENT_QUOTES,
            'Windows-1252'
        );

        echo $name.' - '.$class.'<br>';
    }
}else{
    echo 'No records found for this page.';
}

Create Previous, Page and Next Links Top ↑

For a small number of pages, all page numbers can be displayed. For a large dataset, showing hundreds of links is not useful, so this example displays a small window around the current page.

if($total_records>0){
    $nav_page=min($page,$total_pages);

    if($nav_page>1){
        $previous=$nav_page-1;

        echo "<a href='?page=1'>First</a> ";
        echo "<a href='?page=$previous'>Previous</a> ";
    }

    $from=max(
        1,
        $nav_page-2
    );

    $to=min(
        $total_pages,
        $nav_page+2
    );

    for($i=$from;$i<=$to;$i++){
        if($i===$nav_page){
            echo "<strong>$i</strong> ";
        }else{
            echo "<a href='?page=$i'>$i</a> ";
        }
    }

    if($nav_page<$total_pages){
        $next=$nav_page+1;

        echo "<a href='?page=$next'>Next</a> ";
        echo "<a href='?page=$total_pages'>Last</a>";
    }
}

Complete PHP PDO Pagination Example Top ↑

This combines validation, stored procedure execution, both result sets, safe output and page navigation.

<?php
require 'config.php';

$limit=5;

$page=filter_input(
    INPUT_GET,
    'page',
    FILTER_VALIDATE_INT,
    [
        'options' => [
            'min_range' => 1
        ]
    ]
);

if($page===false || $page===null){
    $page=1;
}

$start=($page-1)*$limit;

try{
    $stmt=$dbo->prepare(
        "CALL GetStudentsAndCount(:start,:limit)"
    );

    $stmt->bindValue(
        ':start',
        $start,
        PDO::PARAM_INT
    );

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

    $stmt->execute();

    $students=$stmt->fetchAll(
        PDO::FETCH_ASSOC
    );

    $total_records=0;

    if($stmt->nextRowset()){
        $total_records=(int)$stmt->fetchColumn();
    }

    $stmt->closeCursor();

    $total_pages=max(
        1,
        (int)ceil(
            $total_records/$limit
        )
    );

    if($students){
        foreach($students as $student){
            $name=htmlspecialchars(
                (string)$student['name'],
                ENT_QUOTES,
                'Windows-1252'
            );

            $class=htmlspecialchars(
                (string)$student['class'],
                ENT_QUOTES,
                'Windows-1252'
            );

            echo $name.' - '.$class.'<br>';
        }
    }else{
        echo 'No records found for this page.';
    }

    if($total_records>0){
        $nav_page=min($page,$total_pages);

        echo '<nav aria-label="Student pages">';

        if($nav_page>1){
            $previous=$nav_page-1;

            echo "<a href='?page=1'>First</a> ";
            echo "<a href='?page=$previous'>Previous</a> ";
        }

        $from=max(
            1,
            $nav_page-2
        );

        $to=min(
            $total_pages,
            $nav_page+2
        );

        for($i=$from;$i<=$to;$i++){
            if($i===$nav_page){
                echo "<strong>$i</strong> ";
            }else{
                echo "<a href='?page=$i'>$i</a> ";
            }
        }

        if($nav_page<$total_pages){
            $next=$nav_page+1;

            echo "<a href='?page=$next'>Next</a> ";
            echo "<a href='?page=$total_pages'>Last</a>";
        }

        echo '</nav>';
    }
}catch(Throwable $e){
    error_log($e->getMessage());

    echo 'Unable to load the requested page.';
}

Why Pagination Needs ORDER BY Top ↑

Pagination should use a deterministic ordering. Without ORDER BY, SQL does not guarantee that rows will be returned in a particular sequence.

This is preferable:

SELECT id,name,class,mark,gender
FROM student
ORDER BY id
LIMIT p_start,p_limit;

to:

SELECT id,name,class,mark,gender
FROM student
LIMIT p_start,p_limit;

A stable sort becomes particularly important when users move between successive pages.

Empty Tables and Invalid Page Numbers Top ↑

Missing or Invalid page Parameter Top ↑

The example defaults to page 1 when page is missing, zero, negative or not a valid integer.

Page Beyond the Last Page Top ↑

A valid positive page number can still point beyond the available records. In that case the stored procedure returns an empty first result set.

The application can display:

No records found for this page.

For a production application, another option is to redirect the visitor to the last valid page after the total count has been established.

No Records in the Table Top ↑

If COUNT(*) returns zero, there are no page links to display and the application can show a simple no-records message.

Pagination Performance with LIMIT and OFFSET Top ↑

Offset pagination is easy to understand and works well for many ordinary datasets. However, very deep pages can become increasingly expensive because the database may need to locate and skip many earlier rows before returning the requested page.

For example:

LIMIT 50000,20

can require substantially more work than:

LIMIT 0,20

For very large or frequently changing datasets, applications may eventually use keyset or cursor-style pagination based on an indexed column such as id. That is a different pagination strategy and does not need to replace this basic stored-procedure example.

Index the Ordering Column Top ↑

The column used for ordering should normally have an appropriate index when pagination operates over a large table. The primary-key id used in this example is already suitable for predictable ordering.

Common PDO Pagination Problems Top ↑

Not Validating the Page Number Top ↑

Do not directly trust $_GET['page']. Validate it as a positive integer before calculating the offset.

Using SELECT * Unnecessarily Top ↑

Select only the columns required by the page. This makes the query purpose clearer and avoids transferring unused data.

Paging without ORDER BY Top ↑

LIMIT alone does not define a reliable row order. Use an appropriate ORDER BY expression.

Forgetting the Total Record Count Top ↑

The number of returned records tells you only how many rows are on the current page. The complete count is required to calculate the total number of pages.

Calling fetchAll() Twice without nextRowset() Top ↑

The count is returned as a second result set. Call nextRowset() before trying to read it.

Leaving Stored Procedure Result Sets Open Top ↑

After reading the required result sets, call closeCursor() before issuing another query on the same PDO connection.

Displaying Database Values without HTML Escaping Top ↑

Prepared statements protect the database query. They do not automatically make database values safe for HTML output. Escape strings when displaying them in the page.

Displaying Every Page Number Top ↑

A dataset with hundreds of pages should not produce hundreds of navigation links. Show a small page range plus useful First, Previous, Next and Last links.

Related PHP PDO and SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: