Pagination in PHP Using a Merged Stored Procedure and PDO

Pagination of records refers to the process of dividing a large dataset into smaller, manageable chunks, or "pages." This is typically done in database queries to retrieve a subset of rows (e.g., 10 or 20 records at a time) instead of retrieving the entire dataset at once.

We will learn how to implement pagination using a single stored procedure in MySQL that retrieves both student records and the total number of records. The script, utilizing PHP PDO, simplifies pagination logic and optimizes database interaction

Creating Stored Procedure

DELIMITER $$

CREATE PROCEDURE GetStudentsAndCount(IN start INT, IN offset INT)
BEGIN
    -- First, select the students with LIMIT
    SELECT * FROM student
    LIMIT start, offset;

    -- Then, select the total number of records
    SELECT COUNT(*) AS total FROM student;
END$$

DELIMITER ;

This MySQL stored procedure, `GetStudentsAndCount`, accepts two input parameters, `start` and `offset`, for pagination:

It first retrieves a subset of records from the `student` table using `LIMIT start, offset` to fetch only the specified number of records starting from the given index.
It then runs a second query to count the total number of records in the `student` table, returning the count as `total`, which is useful for implementing pagination and navigation.

Creating and executing Stored Procedures
The connection object $dbo is taken from config.php file.

require 'config.php'; // Database connection having $dbo

// Define pagination variables
$limit = 5;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$start = ($page - 1) * $limit;

// Call the merged stored procedure
$stmt = $dbo->prepare("CALL GetStudentsAndCount(:start, :limit)");
$stmt->bindParam(':start', $start, PDO::PARAM_INT);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();

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

// Move to the next result set to get the total count
$stmt->nextRowset();
$total_records = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
$total_pages = ceil($total_records / $limit);

// Display records
foreach ($students as $student) {
    echo $student['name'] . ' - ' . $student['class'] . '<br>';
}

// Pagination navigation
for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}

Database Connection: The `config.php` file contains the database connection, which defines the `$dbo` object.
Pagination Setup: The `$limit` defines records per page, and `$start` calculates the starting point for fetching data based on the current page number.
Stored Procedure Call: The script calls the stored procedure `GetStudentsAndCount`, binding the parameters `:start` and `:limit` for pagination.
Fetch Data: The script first fetches student records, then uses `nextRowset()` to fetch the total record count from the second result set.
Pagination Links: Based on the total records and page count, it generates links to navigate through the pages.

Sample Student table SQL dump SQlite Paging of records

Podcast on MySQL database management using PHP PDO

PDO References

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

Pagination using stored precedure

Creating Stored Procedure

Subhendu Mohapatra