What are some best practices for paginating data in PHP to display only a certain number of records at a time?

When dealing with a large dataset in PHP, it's important to paginate the data to display only a certain number of records at a time. This helps improve performance and user experience by breaking up the data into manageable chunks. One common approach is to use SQL LIMIT and OFFSET clauses to fetch a specific subset of records from the database. 

<?php

// Connect to your database
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');

// Define the number of records to display per page
$records_per_page = 10;

// Calculate the OFFSET value based on the current page
$current_page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($current_page - 1) * $records_per_page;

// Query to fetch records with LIMIT and OFFSET
$stmt = $pdo->prepare("SELECT * FROM your_table LIMIT :limit OFFSET :offset");
$stmt->bindParam(':limit', $records_per_page, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

// Display the records
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Display each record as needed
}

// Pagination links
$total_records = $pdo->query("SELECT COUNT(*) FROM your_table")->fetchColumn();
$total_pages = ceil($total_records / $records_per_page);

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

Keywords

pagination data display PHP records limit

Related Questions