How to create Pagination with PHP and MySql

Last updated on October 31st, 2017 by Paritosh Pandey, 4 comments


In this tutorial, we are going to create pagination with PHP and MySql. It is probably possible that your SQL SELECT query may return result into thousand and millions of records. And it is obviously not a good idea to display all those results on one page. So we can split this result into multiple pages.

What is Pagination?

Paging means displaying all your fetched results in multiple pages instead of showing them all on one page. It makes that page so long and would take so much time to load.

How to create Pagination with PHP and MySql

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

Follow these simple steps to create pagination with PHP and MySql – 

1. Get the current page number

This code will get the current page number with the help of $_GET Array. Note that if it is not present it will set the default page number to 1.


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

 

2. Formula for pagination


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

 

3. Get the number of total 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

Here I am using bootstrap’s pagination button


<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.

You may also like


4 responses to “How to create Pagination with PHP and MySql”

  1. Rohit says:

    very nice way to describe things…keep doing the good work

  2. Jass says:

    hi Paritosh
    dude you doing the best job for the beginners who start a career in web development

Leave a Reply

Your email address will not be published. Required fields are marked *