What are the best practices for handling pagination in PHP to avoid displaying all entries on a single page despite setting a limit for the number of entries per page?

When implementing pagination in PHP, it is important to use a combination of LIMIT and OFFSET clauses in your SQL query to retrieve a specific subset of data for each page. By using these clauses, you can control the number of entries displayed per page and navigate through different pages of results. Additionally, you can calculate the total number of pages based on the total number of entries and the limit per page to create navigation links for users to move between pages. 

<?php
// Establish a database connection
$pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');

// Define the limit of entries per page
$limit = 10;

// Get the current page number from the URL parameter
$page = isset($_GET['page']) ? $_GET['page'] : 1;

// Calculate the offset for the SQL query
$offset = ($page - 1) * $limit;

// Query to retrieve entries for the current page
$stmt = $pdo->prepare("SELECT * FROM table_name LIMIT :limit OFFSET :offset");
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

// Display the entries on the current page
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['column_name'] . "<br>";
}

// Calculate the total number of entries
$total_entries = $pdo->query("SELECT COUNT(*) FROM table_name")->fetchColumn();

// Calculate the total number of pages
$total_pages = ceil($total_entries / $limit);

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

Keywords

pagination PHP limit entries handling

Related Questions