How can pagination be implemented in PHP to display MySQL results on multiple pages?

When displaying MySQL results in PHP, pagination can be implemented by limiting the number of results displayed per page and providing navigation links to access different pages of results. This can be achieved by using the LIMIT clause in the SQL query to fetch a specific range of results based on the current page number and the number of results per page. 

<?php

// Establish a database connection
$connection = mysqli_connect('localhost', 'username', 'password', 'database');

// Define the number of results to display per page
$results_per_page = 10;

// Get the current page number
if (isset($_GET['page'])) {
    $page = $_GET['page'];
} else {
    $page = 1;
}

// Calculate the starting result for the current page
$start_from = ($page - 1) * $results_per_page;

// Fetch results from MySQL using LIMIT clause
$query = "SELECT * FROM table_name LIMIT $start_from, $results_per_page";
$result = mysqli_query($connection, $query);

// Display results
while ($row = mysqli_fetch_assoc($result)) {
    // Display results as needed
}

// Create pagination links
$query = "SELECT COUNT(*) as total_rows FROM table_name";
$result = mysqli_query($connection, $query);
$row = mysqli_fetch_assoc($result);
$total_pages = ceil($row['total_rows'] / $results_per_page);

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

// Close database connection
mysqli_close($connection);

?>

Keywords

pagination PHP MySQL results multiple pages

Related Questions