Pagination using stored precedure

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 ;

1. 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.
2. 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.

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.