PHP pagination script from MySQL

Below is a PHP script that demonstrates how to implement pagination when displaying records from a MySQL database. The script includes explanations for each part, as well as examples.

Database Setup

Before running the script, ensure you have a MySQL database set up with a table called items. Here’s an example SQL command to create a simple table:

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL
);

INSERT INTO items (name, description) VALUES
('Item 1', 'Description for item 1'),
('Item 2', 'Description for item 2'),
('Item 3', 'Description for item 3'),
('Item 4', 'Description for item 4'),
('Item 5', 'Description for item 5'),
('Item 6', 'Description for item 6'),
('Item 7', 'Description for item 7'),
('Item 8', 'Description for item 8'),
('Item 9', 'Description for item 9'),
('Item 10', 'Description for item 10'),
('Item 11', 'Description for item 11'),
('Item 12', 'Description for item 12');  // Add more items as needed

PHP Script: pagination.php

<?php
// Database connection parameters
$servername = "localhost"; // Your database server
$username = "username"; // Your username
$password = "password"; // Your password
$dbname = "database_name"; // Your database name

// Create a connection to the database
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Step 1: Define how many results you want per page
$resultsPerPage = 4; // Number of results per page

// Step 2: Determine the total number of results in the database
$sql = "SELECT COUNT(id) AS total FROM items";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$totalResults = $row['total'];

// Step 3: Determine the total number of pages available
$totalPages = ceil($totalResults / $resultsPerPage);

// Step 4: Determine the current page number from the URL, default to page 1 if not set
$currentPage = isset($_GET['page']) ? (int)$_GET['page'] : 1;

// Prevent the page number from exceeding the boundaries
if ($currentPage > $totalPages) {
    $currentPage = $totalPages;
} elseif ($currentPage < 1) {
    $currentPage = 1;
}

// Step 5: Calculate the starting limit for the SQL query
$startLimit = ($currentPage - 1) * $resultsPerPage;

// Step 6: Retrieve the selected results from the database
$sql = "SELECT * FROM items LIMIT $startLimit, $resultsPerPage";
$result = $conn->query($sql);

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

// Step 8: Create pagination links
echo "<br>";

echo "Page: ";
for ($page = 1; $page <= $totalPages; $page++) {
    if ($page == $currentPage) {
        echo "<strong>$page</strong> "; // Highlight the current page
    } else {
        echo "<a href='?page=$page'>$page</a> "; // Create a link for each page
    }
}

// Step 9: Close the database connection
$conn->close();
?>

Explanation of the Script:

1. Database Connection: The script starts by connecting to the MySQL database using mysqli.
2. Results Per Page: You can specify how many results to display per page with the variable $resultsPerPage.
3. Total Results: A SQL query is executed to count the total number of rows in the items table using COUNT(id). This count is necessary to determine how many pages are needed.
4. Total Pages: The total number of pages is calculated by dividing the total number of results by the number of results per page, rounding up using ceil().
5. Current Page: The script checks the URL for the current page number (using $_GET['page']). If no page is specified, it defaults to page 1. The script also ensures that the page number is within the valid range.
6. Starting Limit: The limit for the SQL query is determined by calculating the start position of the records to be fetched, based on the current page.
7. Retrieve Results: The main query retrieves records from the items table based on the calculated limit using LIMIT $startLimit, $resultsPerPage.
8. Display Results: The script loops through the results and displays each item.
9. Pagination Links: The script generates pagination links at the bottom. It highlights the current page and creates a clickable link for each page.
10. Close Connection: Finally, the script closes the database connection.

Example Usage:

• When you visit pagination.php, it will display the first page of results.
• You can navigate through the pages using the generated links (e.g., Page: 1 2 3 …).

This script demonstrates how to implement basic pagination with PHP and MySQL. It makes it easy to navigate through large datasets while keeping the user experience smooth and organized. Adjust the $resultsPerPage variable to change how many items are shown on each page according to your requirements.