Skip to content
PHP Logo

How can pagination be implemented in PHP to display a limited number of database records per page with navigation buttons for the user?

Pagination can be implemented in PHP by limiting the number of database records displayed per page and providing navigation buttons for the user to navigate through the pages. This can be achieved by using SQL queries with LIMIT and OFFSET clauses to fetch a specific number of records for each page. Additionally, navigation buttons can be created to allow the user to move between pages.

php
<?php

// Establish a database connection
$connection = new PDO("mysql:host=localhost;dbname=example_db", "username", "password");

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

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

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

// Fetch records from the database using LIMIT and OFFSET
$query = $connection->prepare("SELECT * FROM table_name LIMIT :limit OFFSET :offset");
$query->bindParam(':limit', $records_per_page, PDO::PARAM_INT);
$query->bindParam(':offset', $offset, PDO::PARAM_INT);
$query->execute();
$records = $query->fetchAll();

// Display the records
foreach ($records as $record) {
    echo $record['column_name'] . "<br>";
}

// Create navigation buttons
$total_records = $connection->query("SELECT COUNT(*) FROM table_name")->fetchColumn();
$total_pages = ceil($total_records / $records_per_page);

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

?>