How to create Pagination with PHP and MySql

Last updated on by , 17 comments


create pagination in PHP

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 in PHP – 

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 php pagination

You can always manage the number of records to be displayed in a page by changing the value of $no_of_records_per_page variable.


$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

These Buttons are served to users as Next Page & Previous Page, so that they can easily navigate through you pages. Here I am using bootstrap’s pagination button, you can use your own buttons if you want.


<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



17 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

  3. Hey keep posting such good and meaningful articles.

  4. Emilio B says:

    When I use a submit form with post and the query with select * from xxx Like “search”, when I click to another page the post variable is gone. How to fix this?

  5. ntambi says:

    am getting an error of undefined variable
    pegno of get array

  6. Great Tutorial! This PHP pagination tutorial have all the options. But I think it would be better if you add an option to change the total number of results. What do you think about it?

  7. edrine says:

    nice tutorial but i have some problem i am gettng error
    undefined pageno

  8. ali says:

    Hey,

    Great tutorial mate.

    But I’ve got some GET variables in the URL, which are lost when I press the NEXT or any other button. It just edits the URL to “?pageno=2”.

    It’d great if you fixed that. Thanks!

  9. ali says:

    Hey Paritosh,

    Thanks for your reply. How do I do that? Any pointer?

    Thanks again!

    • suppose you have 2 get params (type & status) in your url

      then pagination code would be

      
      <ul class="pagination">
          <li><a href="?type=test&status=test&pageno=1">First</a></li>
          <li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
              <a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?type=test&status=test&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 "?type=test&status=test&pageno=".($pageno + 1); } ?>">Next</a>
          </li>
          <li><a href="?type=test&status=test&pageno=<?php echo $total_pages; ?>">Last</a></li>
      </ul>
      
      

Leave a Reply

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