How to Implement Efficient Data Pagination with PHP

How to Implement Efficient Data Pagination with PHP

Introduction:
In web development, data pagination is an essential feature when displaying large amounts of data. Pagination not only enhances site performance but also provides a better browsing experience for users. This article will show you how to implement a simple and efficient data pagination function using PHP, with code examples.

1. Preparation

Before writing the code, you'll need to prepare a MySQL database and a table to store the data for pagination. Let's assume you have created a table called "users" with the following structure:

    CREATE TABLE users (
      id int(11) NOT NULL AUTO_INCREMENT,
      name varchar(50) NOT NULL,
      age int(11) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

2. Writing the Pagination Code

1. Connecting to the Database:
   First, create a function to connect to the database with the appropriate connection details. Here's an example:

    <?php
    function connectDB() {
        $servername = "localhost";
        $username = "root";
        $password = "password";
        $dbname = "database";

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

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

        return $conn;
    }
    ?>
    

2. Getting the Total Record Count:
   Next, write a function to get the total number of records from the table. Here's an example:

    <?php
    function getTotalCount() {
        $conn = connectDB();
        $sql = "SELECT COUNT(*) AS count FROM users";
        $result = $conn->query($sql);
        $row = $result->fetch_assoc();
        $count = $row["count"];
        $conn->close();

        return $count;
    }
    ?>
    

3. Fetching Data for Pagination:
   Write a function that will retrieve the data based on the page number and the number of records per page. Here's an example:

    <?php
    function getData($page, $pageSize) {
        $conn = connectDB();
        $start = ($page - 1) * $pageSize;
        $sql = "SELECT * FROM users LIMIT $start, $pageSize";
        $result = $conn->query($sql);
        $data = [];

        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
        }
        $conn->close();

        return $data;
    }
    ?>
    

4. Displaying Pagination Navigation:
   Finally, create a function to display the pagination navigation, helping users switch between pages. Here's an example:

    <?php
    function showPagination($page, $pageSize, $totalCount) {
        $totalPage = ceil($totalCount / $pageSize);
        $prevPage = $page - 1;
        $nextPage = $page + 1;

        echo "<div class='pagination'>";

        if ($page > 1) {
            echo "<a href='?page=$prevPage'>Previous</a>";
        }

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

        if ($page < $totalPage) {
            echo "<a href='?page=$nextPage'>Next</a>";
        }

        echo "</div>";
    }
    ?>
    

3. Using the Pagination Function

Now, integrate the functions into a single PHP file and add the following code to display the pagination:

    <?php
    $page = isset($_GET['page']) ? $_GET['page'] : 1;
    $pageSize = 10;

    $totalCount = getTotalCount();
    $data = getData($page, $pageSize);

    // Display data
    foreach ($data as $row) {
        echo sprintf("ID: %s, Name: %s, Age: %s<br>", $row['id'], $row['name'], $row['age']);
    }

    // Display pagination navigation
    showPagination($page, $pageSize, $totalCount);
    ?>
    

Conclusion

By following these steps, we've successfully implemented a simple data pagination feature using PHP and MySQL. You can modify or extend these codes to fit more complex pagination requirements. We hope this guide helps developers efficiently apply data pagination in their projects.