Can you provide an example code snippet in PHP demonstrating how to implement pagination with limited results per page using MySQL queries?

When dealing with a large dataset in MySQL, implementing pagination with limited results per page can improve performance and user experience. This can be achieved by using LIMIT and OFFSET in your MySQL query to fetch a specific number of rows starting from a given offset. 

<?php

// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Define pagination variables
$results_per_page = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page - 1) * $results_per_page;

// Fetch data using pagination
$sql = "SELECT * FROM your_table LIMIT $offset, $results_per_page";
$result = $conn->query($sql);

// Display data
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

// Pagination links
$sql = "SELECT COUNT(*) AS total FROM your_table";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_pages = ceil($row["total"] / $results_per_page);

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

$conn->close();

?>

Keywords

PHP pagination MySQL query results per page

Related Questions