How to create pagination system using PHP and MYSQL?

How to create pagination system using PHP and MYSQL?

In this tutorial, we are creating pagination using PHP and MySql. Your SQL SELECT query will probably result in thousands and millions of records returning. And presenting all those findings on one page is obviously not a good idea. So we can split the result into several pages.

What is Pagination?

Paging means presenting all the findings you have collected in multiple pages instead of showing them all on one page. This makes the page so long and would take so long to load.

How to create Pagination with PHP and MySql

MySQL’s LIMIT clause helps us to create a pagination feature. This uses two arguments First argument as OFFSET and second argument the number of records which will be returned from the database.

To build pagination in PHP, follow these simple steps-

use $GET array to get the current page number.

1. Get the current page number

With the help of $ GET Array , we are getting current page number. Notice that if it is not present the default page number will be set to 1.

if (isset($_GET['pageno'])) {
    $pageno = $_GET['pageno'];
} else {
    $pageno = 1;
}

2. The Php Pagination method

Through adjusting the value of variable $no of records per page, you can always control the number of records to be shown in a page.

$no_of_records_per_page = 10;
$offset = ($pageno-1) * $no_of_records_per_page; 

3. Get the number of total number of pages

$total_pages_sql = "SELECT COUNT(*) FROM table";
$result = mysqli_query($conn,$total_pages_sql);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page);

4. Constructing the SQL Query for pagination

$sql = "SELECT * FROM table LIMIT $offset, $no_of_records_per_page";

5. Pagination buttons

Such buttons are used as Next Page & Previous Page for users to navigate quickly through your pages. Here I create the pagination button using the bootstrap, you can use your own buttons if you wish.

<ul class="pagination">
    <li><a href="?pageno=1">First</a></li>
    <li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
        <a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
    </li>
    <li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
        <a href="<?php if($pageno >= $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
    </li>
    <li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
</ul>

6. Let's assemble all the codes in one page

<html>
<head>
    <title>Pagination</title>
    <!-- Bootstrap CDN -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
    <?php

        if (isset($_GET['pageno'])) {
            $pageno = $_GET['pageno'];
        } else {
            $pageno = 1;
        }
        $no_of_records_per_page = 10;
        $offset = ($pageno-1) * $no_of_records_per_page;

        $conn=mysqli_connect("localhost","my_user","my_password","my_db");
        // Check connection
        if (mysqli_connect_errno()){
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
            die();
        }

        $total_pages_sql = "SELECT COUNT(*) FROM table";
        $result = mysqli_query($conn,$total_pages_sql);
        $total_rows = mysqli_fetch_array($result)[0];
        $total_pages = ceil($total_rows / $no_of_records_per_page);

        $sql = "SELECT * FROM table LIMIT $offset, $no_of_records_per_page";
        $res_data = mysqli_query($conn,$sql);
        while($row = mysqli_fetch_array($res_data)){
            //here goes the data
        }
        mysqli_close($conn);
    ?>
    <ul class="pagination">
        <li><a href="?pageno=1">First</a></li>
        <li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
            <a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
        </li>
        <li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
            <a href="<?php if($pageno >= $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
        </li>
        <li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
    </ul>
</body>
</html>

That’s all!

If you find this article helpful, don’t forget to share it with your friends. And stay updated with us by subscribing our blog.

Leave comment

Comments (1)

// SEARCH

What are you searching for....?

// SUBSCRIBE

Subscribe to us on youtube for updates of our videos first on youtube.

// SUBSCRIBE

To receive updates on the latest news, tutorials and special offers, subscribe to our email newsletter today!

// CATEGORIES

x
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Privacy Policy That's Fine