How can pagination be implemented in PHP to display a limited number of database records per page?

When dealing with a large number of database records, it is important to implement pagination to display a limited number of records per page. This can be achieved in PHP by using LIMIT and OFFSET clauses in SQL queries to fetch a specific range of records from the database. Additionally, you can use GET parameters in the URL to determine the current page number and adjust the LIMIT and OFFSET values accordingly. 

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

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

// Determine current page number
$page = isset($_GET['page']) ? $_GET['page'] : 1;

// Calculate OFFSET value
$offset = ($page - 1) * $records_per_page;

// Fetch records from the database using 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 records
while ($row = $stmt->fetch()) {
    echo $row['column_name'] . '<br>';
}

// Display 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 PHP database records limit per page

Related Questions