Paginate MySQL query results using PHP

A guide on how to paginate MySQL query results using PHP. This PHP code helps split data from MySQL into separate pages for better presentation.

<?php
// 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);
}

// Set the number of results per page
$results_per_page = 10;

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

// Determine the starting point for results in the database
$start_from = ($page - 1) * $results_per_page;

// Query the database with a limit on results for each page
$sql = "SELECT * FROM table_name LIMIT $start_from, $results_per_page";
$result = $conn->query($sql);

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

// Count total number of results in the database
$sql_total = "SELECT COUNT(id) AS total FROM table_name";
$result_total = $conn->query($sql_total);
$row_total = $result_total->fetch_assoc();
$total_pages = ceil($row_total["total"] / $results_per_page);

// Create links to other pages
for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='pagination.php?page=" . $i . "'>" . $i . "</a> ";
}

// Close the connection
$conn->close();
?>

Detailed explanation:

1. Database connection:

   • $conn = new mysqli($servername, $username, $password, $dbname): Connect to the MySQL database.

2. Set the number of results per page:

   • $results_per_page = 10: Sets the number of records to display on each page.

3. Determine the current page number:

   • $_GET['page']: Retrieves the page number from the URL. If not provided, the default is page 1.

4. Determine the starting point for results:

   • $start_from = ($page - 1) * $results_per_page: Calculates the starting record for the current page.

5. Query the database with a result limit:

   • LIMIT $start_from, $results_per_page: Queries data from the starting point with a limit on the number of results to display per page.

6. Count total number of results:

   • SELECT COUNT(id) AS total FROM table_name: Counts the total number of records in the table to determine how many pages are needed.

7. Create links to other pages:

   • for ($i = 1; $i <= $total_pages; $i++): Generates links to other pages based on the total number of pages.

8. Close the connection:

   • $conn->close(): Closes the connection to the database.

PHP Version:

This code can run on PHP versions from 5.0 and above and requires the MySQLi extension.