My Programming Tutorials

My Programming Tutorials

How to create Pagination with PHP and MySql

Last updated on by , 120 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.

Author Info

Paritosh Pandey

He loves Technology

Advertisement
120 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

      • Diogo says:

        So i’m trying to do what you said and its not going well.
        what is exactly the searched variable is it the $query=select * from xxx Like “search”? or something else?

  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! 🙂

  35. Ben says:

    How do I display/get the numbers between 0-10 instead of displaying prev and next button?

  36. Khamad Ali says:

    Thanks it’s helpfull. Nice blog 🙂

  37. Rémi Corso says:

    Hello and thx for this amazing tutoriel,

    I have a problem with this system, might be from my own code, could you help me?

    The pagination’s buttons working good, but the list limit isnt working.

    Could you help me in private mb?

    Thx a lot and nice job man, that’s amazing.

  38. Pritam Raghav says:

    i want to show some number of pages as well

  39. Abu bakar says:

    hi there i use this code for my listing page but i got error which icludes this message…

    Message: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

  40. ali says:

    hello
    i use your code for my search page but i have 2 error’s include this message :-

    mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in ..
    mysqli_query() expects parameter 1 to be mysqli, object given in …

    please help me , and thank you .

  41. ardrkn says:

    I’m new in PHP. And this article is so much helpfull people like me from beginning class. Thank you so much my firend! You did wonderfull job!

  42. Finntown says:

    Always the first line on the page is missing?
    $no_of_records_per_page = 5; and there is 14 rows total.
    The first page is 2…5
    The next page is 7…10
    The last page is 12…14.

  43. mauricio says:

    Excellent tutorial, shown from beginning to end, the way it should be.

  44. this is what I was looking for.
    thank you for your hard working.

  45. Basir Payenda says:

    Thank you so much, this solved my problem?
    Two questions?

    How do I make pagination for image galleries?
    How do I give a dynamic background color for active elements of pagination?
    Thank you

    • Paritosh Pandey says:

      you could use $total_pages in for loop

      
      for($i=1; $i< =$total_pages; $i++){
      
          if($pageno == $i){
              echo "selected";
          }
      
      }

      you could use above strategy in your pagination buttons

  46. Xwiit says:

    Thanks so much, this really helped me. I have searched all around for pagination like this, I really appreciate.

  47. Amjad says:

    Thanks, You are a Genius. God bless you!

  48. So far, I agree with you on much of the info you have written here. I will have to think some on it, but overall this is a wonderful article.

  49. Mae says:

    Thank you so much for sharing this pagination, very clear and easy to understand.
    God bless you, brother!

  50. Fahad Ahmed says:

    I paste that code in blog page in my blog page there are 3 content Image Title and Text but i pasted that code and change the number of page record means i want only 3 blog per page and go to next page but that code not work please reply my question as soon as fast

  51. C Vasanth says:

    That was useful code that I was looking for. I have added another column to the table to give it an edit capability with Edit and it works fine with an edit screen and n is dynamically generated.

    Thank you Paritosh ji for sharing this code which saved me a lot of time.

  52. Michael says:

    mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in line 52 where i have this code

    while($row = mysqli_fetch_array($query))
    {
    print_r($row);
    }

    Can you help me please?

  53. Laurentiu says:

    thank you this helped me alot

  54. Laurentiu says:

    while($row = mysqli_fetch_array($query))
    {
    $obj[] = $row;
    }
    foreach ($obj as $user){
    echo ”.$user[“username”].”;
    }

  55. Marc says:

    Tutoriel Simple et efficace, l’essentiel pour avoir une base solide. Puis chercher à l’améliorer. Merci pour cette aide, e me noyait dans les tutoriaux tellement nombreux sur ce sujet.
    (google translation)
    Tutorial Simple and effective, the essential to have a solid foundation. Then try to improve it. Thank you for this help, I was drowning in so many tutorials on this topic.
    Best reguards
    Marc

  56. Jick says:

    Great tutorial.
    How to show all pages instead of First,Previous,Next and Last?
    Example: I have 100 data to display which I would limit it to 10 per pages.
    I want to direct click on page 8 instead of keep clicking on Next button.
    Any guidelines? Thanks

  57. great tutorial, very usefull and solve my problem

  58. JS says:

    Thank you very much for this. Not only was the code useful but the step-by-step made it very easy to adapt

  59. Diego Torres says:

    Perfect!! just what I needed, Simple and easy

  60. Abbass Sharara says:

    Thank you very much I was searching for the sql query I was lost in it and you really helped me.

    Thank You So Much Again!!

  61. Sneha Biswas says:

    I understood the logic

  62. Roland says:

    Bless you! blended well with my script

  63. A good tutorial for learning. Thanks

  64. baba says:

    Thanks alot. Using your insturction i could simply insert the pagination to my data.
    I have another question:
    There are only First, Previous, Next and Last buttons.
    Is it possible to add the nummber buttons for pages besid these buttons. As the “pagination” of Google?

  65. Supriya says:

    good Tutorial for learning thanks

  66. karim says:

    love u man thank u very much for this tuto .

  67. Ajo says:

    I am getting an error i like this,
    mysql_fetch_array() expects parameter 1 to be resource, boolean given in…..
    I can’t use mysqli,because,in my project am using the earlier version.
    Here is my code:

    $link = mysql_connect($Host, $User, $Password) or die(‘Could not connect: ‘ . mysql_error());
    mysql_select_db($DBName) or die(‘Could not select database’);

    $total_pages_sql=”SELECT * from table”;
    $result = mysql_query($total_pages_sql);
    $total_rows = mysql_fetch_array($result,MYSQLI_NUM)[0];
    $total_pages = ceil($total_rows / $no_of_records_per_page);

  68. mahendra says:

    sir i have created a blog page. and it is working properly but 1 and 2 page post properly show . but when i click next page(3) it is showing blank and other page (like 4,5,6….) also.. i can not find where is my error.please help me why it can not show after 2 page..

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.