my-programming-tutorials-logo

My Programming Tutorials

How to create Pagination with PHP and MySql

Last updated on by , 71 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 a 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. The 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.

How to create Pagination with PHP and MySql
4.5 (90.22%) 45 votes

Advertisement
71 Responses
  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. Buy Contact Lenses says:

    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?

    • Paritosh Pandey says:

      you just have to pass your searched variable in pagination buttons (in URI GET param),
      append this in pagination buttons &q=$searchedVariable

  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?

    • Paritosh Pandey says:

      Hello @Souradeep,

      Thank you for such valuable comments. And yeah its a good idea, I definitely gonna implement your suggestion.

  7. edrine says:

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

    • Paritosh Pandey says:

      Hello @Edrine,

      please make sure to place below code at the top of page

      
      if (isset($_GET['pageno'])) {
          $pageno = $_GET['pageno'];
      } else {
          $pageno = 1;
      }
      
      
  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!

    • Paritosh Pandey says:

      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>
      
      
  10. Hamza jawaid says:

    Bro, you did a great job..
    Really appreciated,
    Thanks

  11. Mudiaga Obriki says:

    Good day. The paginator is displaying at the bottom but when I click next, it returns empty results.

    • Paritosh Pandey says:

      could it be possible to provide a demo link for your paginator, it helps me to understand better.

  12. Ben says:

    Am getting an error that a non numeric value was encountered on the line holding the next page code am new to php help

  13. Mubarak Auwal says:

    Nice tutorial overall, how do I re write the URL in to something like this
    https://www.myprogrammingtutorials.com/page/2
    ? Any tip will be of great help

    • Paritosh Pandey says:

      you can use RewriteRule ^(.*)$ index.php?params=$1 [NC] in your .htaccess file, so that your old url
      http://url.com/index.php?params=value
      be like
      http://url.com/params/value

      • Ebrinix says:

        Hi Paritosh, I am trying to echo $result but I get this message: Resource id #17 when I do. Please what is the cause?

  14. maliksanwal says:

    good posting good website

  15. Scarlet says:

    Aaaaaa, thanks sir!! just now i got it xD

  16. Ebrinix says:

    Hi, in the process of writing the code I try to echo $result before I proceed but got this message: Resource id #17. Please how do i sort it out?

    • Paritosh Pandey says:

      please let us see the whole error if possible, it’ll help me to figure the problem out.

      • Ebrinix says:

        I didn’t get an error, I only got the message: Resource id #17. please I need help, I’ve been battling with this for day…

    • Paritosh Pandey says:

      after echo the $result variable you should get
      Object of mysqli_result couldn’t be converted to string
      error

  17. roan says:

    dude when i clicked on next, its always launch me to other page, how can i fix this? thanks

  18. cmarisa says:

    Hi,

    how do I get page numbers to show?

  19. Faisal Aziz says:

    Very Informative.
    Make a tutorial on nested comments in php

    • Paritosh Pandey says:

      Thank you for appreciating @Faisal
      sure I will post a tutorial on nested comments, meanwhile, you can use
      “https://www.myprogrammingtutorials.com/multi-level-nested-category-system-codeigniter-mysql.html” concept for your nested comments.

  20. Gerald says:

    Thank you for this tutorial, I encountered some problem
    my url is already ?pageno=2 but the table is still at page 1.

  21. Ajemuta Isaac says:

    not displaying data
    i don’t know if i should add the field of table
    Thank

  22. Sahan Nimesh says:

    Thanks man. It was really helpful. Thank you very much

  23. rajendra kandel says:

    i dont understand the pageno meanining in the first isset line is it a functio or what and please describe about $offset = ($pageno-1) * $no_of_records_per_page;

    • Paritosh Pandey says:

      The OFFSET clause skips the offset rows before beginning to return the rows. Maybe this image can help you understand better

  24. Ayesha says:

    while($row = mysqli_fetch_array($res_data)){
    //here goes the data
    }

    What data goes in while loop?????

  25. oge says:

    Good Job, really appreciate the script…been having a hard time implementing serial numbering that increments on every page

  26. Khizar says:

    The Code is working great

    any possibility to put the pagination buttons on top of the table ?

    when i do, they don’t function

  27. Ehtasham says:

    it’s really helpful thanks a lot.

  28. Amna says:

    i want to know how to show the result of search query using pagination

  29. Imran says:

    Sir,
    THANKS A LOT
    Very good work
    thanks

  30. Arjan says:

    Hey,
    I am using exactly the same code as yours, but not getting the desired result.
    I am not sure how to modify the href according to my web page. Can you please check my code and see if you can help me modify it and make it work?

    • Paritosh Pandey says:

      You should use AJAX pagination here, loading both views (List & Grid) is not a good practice, let me show you pointwise flow

      – serve view buttons (List & Grid) initially and make one default view
      – put “Next Button” downside
      – when a user clicks on “Next Button”, find his/her selected view (List & Grid) and make an AJAX request along with the selected view value
      – according to the view value, you can return its relevant html codes.
      – at last append response into the final container

      here is a good tutorial about ajax pagination

  31. rajesh says:

    Sir,
    THANKS A LOT
    Very good work
    thanks

  32. Bloserda says:

    This was really simple, great tutorial.
    But, why my page not show the number?
    Just :
    FirstPrevNextLast

    Thanks before.

  33. bloserda says:

    I try to use with nested loop, but iam stuck, could you help me?
    This is my query :

    query($query);
    $total_records = $result->num_rows;
    while($row = $result->fetch_array()) {
    $datarows[] = $row;
    }

    $num_cols = 3;
    $num_rows = ceil($total_records / $num_cols);
    $num = 0;
    echo “\n”;
    // next the loop for the table rows
    for ($rows = 0; $rows < $num_rows; $rows++) { echo "\n”; // this is the loop for the table columns for ($cols = 0; $cols < $num_cols; $cols++) { if ($num < $total_records) { // first create variables with the values of the current record $1 = $datarows[$num]['1']; $2 = $datarows[$num]['2']; $3 = $datarows[$num]['3']; echo "“.$1.” – “.$2.” – “.$3.”\n”; } else { // show an empty cell echo ” \n”; } $num++; } echo “\n”; } echo “\n”; ?>

  34. Stefan says:

    Nice tutorial but I believe your pagination example can be improved by also disabling the First and Last pagination buttons if you are currently on one of those pages.
    This is easily done so I won’t insult you by posting the code here! 🙂

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.