Create Simple Pagination Using PHP and MySQLi


Demo Download

In this tutorial, I will explain that how can we create simple pagination using PHP and MySQLi. If you are working with SQL and fetching multiple records from database, then you have seen that this is not good idea to list all the records in a single page specially when your records are thousands in number. So the best approach is to split these records into multiple pages so that user can easily view or read these records.

What is Pagination?

Pagination mean fetching and displaying your data into multiple pages rather than single page. You have already seen this on various blogs, even on my blog homepage, you can see that I am only displaying 4 to 5 blog posts and older posts are accessible via pagination.

Readers Also Read:ย Laravel 10 User Roles and Permissions

How to Create Simple Pagination Using PHP and MySQLi

Basically we need to fetch limited records on each page, this mean we need to limit the number of records to be fetched. For this purpose, MySQL provides a LIMIT clause, it simply fetch the limited number of records. I have created a sample table named `pagination_table` which you can download from the download button and import into your database. Lets see how LIMIT clause works, just run the following query.

SELECT * FROM `pagination_table` LIMIT 3;

It will give you the following result.

As you can see above that it only returns 3 records, however table contain total 36 records in it. But if we need to fetch the records starting from 5th to 7th then we will use OFFSET in MySQL query, there are two different ways to use the OFFSET, however both will return the same records, you can use anyone that you like.

SELECT * FROM `pagination_table` LIMIT 3 OFFSET 4;

Or you can use shorthand method as well.

SELECT * FROM `pagination_table` LIMIT 4, 3;

These both queries will return the following records.

The difference between both queries that in first method you write LIMIT value after LIMIT and OFFSET value after OFFSET, but in shorthand method you write OFFSET value after LIMIT clause and LIMIT value after comma (,).

OFFSET simply skip the previous records, as you can see in the above image that OFFSET 4, skipped records till 4 and started from 5th records.

Now i hope you got the picture in your mind that what we actually need to do to create a pagination, we just need to change the offset value on each split page.

Steps to Create Simple Pagination Using PHP and MySQLi

  1. Create a Database and Table with Dummy Data
  2. Create a Database Connection
  3. Get the Current Page Number
  4. SET Total Records Per Page Value
  5. Calculate OFFSET Value and SET other Variables
  6. Get the Total Number of Pages for Pagination
  7. SQL Query for Fetching Limited Records using LIMIT Clause and OFFSET
  8. Showing Current Page Number Out of Total
  9. Creating Pagination Buttons

1. Create a Database and Table with Dummy Data

To create database run the following query.

CREATE DATABASE allphptricks;

To create a table run the following query. Note: I have already attached the SQL file of this table with dummy data, just download the complete zip file of this tutorial.

CREATE TABLE IF NOT EXISTS `pagination_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `age` int(10) NOT NULL,
  `dept` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

2. Create a Database Connection

Just create a db.php file and paste the following database connection in it. Make sure that you update these credentials with your database credentials.

// Enter your Host, username, password, database below.
$con = mysqli_connect("localhost","root","","allphptricks");
    if (mysqli_connect_errno()){
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
	die();
	}

3. Get the Current Page Number

To get the current page number, we will use the $_GET .

if (isset($_GET['page_no']) && $_GET['page_no']!="") {
    $page_no = $_GET['page_no'];
    } else {
        $page_no = 1;
        }

4. SET Total Records Per Page Value

You can set any value to total records per page, i am showing only 3 records per page.

$total_records_per_page = 3;

5. Calculate OFFSET Value and SET other Variables

You can see that i have set offset value and calculating the next and previous page number, adjacent is also set here, we will use it soon.

$offset = ($page_no-1) * $total_records_per_page;
$previous_page = $page_no - 1;
$next_page = $page_no + 1;
$adjacents = "2";

6. Get the Total Number of Pages for Pagination

Now we need to calculate total number of pages for pagination, it depends on how many records we want to display on single page. We already have database connection so now getting the total number of pages and also setting the second last number in the below code.

$result_count = mysqli_query(
$con,
"SELECT COUNT(*) As total_records FROM `pagination_table`"
);
$total_records = mysqli_fetch_array($result_count);
$total_records = $total_records['total_records'];
$total_no_of_pages = ceil($total_records / $total_records_per_page);
$second_last = $total_no_of_pages - 1; // total pages minus 1

7. SQL Query for Fetching Limited Records using LIMIT Clause and OFFSET

We will use OFFSET and total records per page here, and display these results.

$result = mysqli_query(
    $con,
    "SELECT * FROM `pagination_table` LIMIT $offset, $total_records_per_page"
    );
while($row = mysqli_fetch_array($result)){
    echo "<tr>
	 <td>".$row['id']."</td>
	 <td>".$row['name']."</td>
	 <td>".$row['age']."</td>
	 <td>".$row['dept']."</td>
	 </tr>";
        }
mysqli_close($con);

This will just create table rows, so make sure that you also created a table header before writing all above PHP scripts, for CSS I am using the bootstrap table, make sure you include bootstrap in your head section.

<table class="table table-striped table-bordered">
<thead>
<tr>
<th style='width:50px;'>ID</th>
<th style='width:150px;'>Name</th>
<th style='width:50px;'>Age</th>
<th style='width:150px;'>Department</th>
</tr>
</thead>
<tbody>
<!--
All your PHP Script will be here
-->
</tbody>
</table>

8. Showing Current Page Number Out of Total

To display your current page number, i am using the following. It should be comes after the end of above table.

<div style='padding: 10px 20px 0px; border-top: dotted 1px #CCC;'>
<strong>Page <?php echo $page_no." of ".$total_no_of_pages; ?></strong>
</div>

9. Creating Pagination Buttons

Now i want your attention because this may be little difficult to understand if you are not focused otherwise it is a piece of cake as this is the main part which plays a vital role. Although in here we can make it simple by just creating a First Page, Next, Previous, and Last Page buttons, but this is not much useful when you have hundred of pages, so user may want to go on page number 50, so user have to click next next next too much.

But I will show you how can you achieve this, if you want to use it only. Again I am using bootstrap pagination for CSS.

<ul class="pagination">
<?php if($page_no > 1){
echo "<li><a href='?page_no=1'>First Page</a></li>";
} ?>
    
<li <?php if($page_no <= 1){ echo "class='disabled'"; } ?>>
<a <?php if($page_no > 1){
echo "href='?page_no=$previous_page'";
} ?>>Previous</a>
</li>
    
<li <?php if($page_no >= $total_no_of_pages){
echo "class='disabled'";
} ?>>
<a <?php if($page_no < $total_no_of_pages) {
echo "href='?page_no=$next_page'";
} ?>>Next</a>
</li>

<?php if($page_no < $total_no_of_pages){
echo "<li><a href='?page_no=$total_no_of_pages'>Last &rsaquo;&rsaquo;</a></li>";
} ?>
</ul>

The above code will generate very beautiful buttons.

If you like only these buttons so you can use the above pagination button script.

First let me explain you how is it working.

  • If current page number is greater than 1 then we display the First Page button otherwise we hide it, same we are doing with the last button, if current page number is equal to the last page number (which is total number of pages) then we hide it too.
  • In between we are creating next and previous buttons, so if current page is equal or less then 1 then we disable the previous button, same we are doing with next button, if current number is equal to the last page number (which is total number of pages) then we disabled it too, otherwise we enabled them.

But i am not satisfied with these button, i want something like this.

Believe me this is very easy, just focus on what I am explaining to you below.

To create pagination like above, previous, next and last page will be generated same as we did above, i don’t like keeping First Page button, as we can reach it using 1 button. After previous button add the following script.

if ($total_no_of_pages <= 10){  	 
	for ($counter = 1; $counter <= $total_no_of_pages; $counter++){
	if ($counter == $page_no) {
	echo "<li class='active'><a>$counter</a></li>";	
	        }else{
        echo "<li><a href='?page_no=$counter'>$counter</a></li>";
                }
        }
}

Now we are checking that if total number of pages are equal or less than 10 then we simply display all 10 pages. It will be something like below

But if total number of pages are greater than 10 then we are using other method. We are checking it using the following

elseif ($total_no_of_pages > 10){
// Here we will add further conditions
}

Now all condition will be inside the above condition, first we will check that if current page number is equal or less than 4 then do the following

if($page_no <= 4) {			
 for ($counter = 1; $counter < 8; $counter++){		 
	if ($counter == $page_no) {
	   echo "<li class='active'><a>$counter</a></li>";	
		}else{
           echo "<li><a href='?page_no=$counter'>$counter</a></li>";
                }
}
echo "<li><a>...</a></li>";
echo "<li><a href='?page_no=$second_last'>$second_last</a></li>";
echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>";
}

It will display pagination like this when you are on start of pagination.

Now we will check that if current page number is greater than 4 and less than (total number of pages -4) then do the following

elseif($page_no > 4 && $page_no < $total_no_of_pages - 4) {		 
echo "<li><a href='?page_no=1'>1</a></li>";
echo "<li><a href='?page_no=2'>2</a></li>";
echo "<li><a>...</a></li>";
for (
     $counter = $page_no - $adjacents;
     $counter <= $page_no + $adjacents;
     $counter++
     ) {		
     if ($counter == $page_no) {
	echo "<li class='active'><a>$counter</a></li>";	
	}else{
        echo "<li><a href='?page_no=$counter'>$counter</a></li>";
          }                  
       }
echo "<li><a>...</a></li>";
echo "<li><a href='?page_no=$second_last'>$second_last</a></li>";
echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>";
}

It will display pagination like this when you are reaching in the middle of pagination.

Now we will check that if current page number is greater than 4 but not less than (total number of pages -4) then do the following

else {
echo "<li><a href='?page_no=1'>1</a></li>";
echo "<li><a href='?page_no=2'>2</a></li>";
echo "<li><a>...</a></li>";
for (
     $counter = $total_no_of_pages - 6;
     $counter <= $total_no_of_pages;
     $counter++
     ) {
     if ($counter == $page_no) {
	echo "<li class='active'><a>$counter</a></li>";	
	}else{
        echo "<li><a href='?page_no=$counter'>$counter</a></li>";
	}                   
     }
}

It will display pagination like this when you are reaching at the end of pagination.

Finally we have complete a beautiful pagination using PHP and MySQLi, which will work any number of pages.

I try my best to explain this tutorial as simple as possible.

Demo Download

If you found this tutorial helpful, share it with your friends and developers group.

I spent several hours to create this tutorial, if you want to say thanks so like my page on Facebook and share it.

Facebook Official Page: All PHP Tricks

Twitter Official Page: All PHP Tricks

Article By
Javed Ur Rehman is a passionate blogger and web developer, he loves to share web development tutorials and blogging tips. He usually writes about HTML, CSS, JavaScript, Jquery, Ajax, PHP and MySQL.
  1. Hi,

    Thank you for the pagination tutorial! I was able to get it up without any problems, especially now that I have a working one for PHP 8. I was wondering if there’s a way to hide the “next” and “previous” if all records display on page 1 (assuming I don’t have a 10 records per page yet and I only have 8 records total in the database)?

      1. I was going to ask if you could provide a snippet of how the code should be executed. But after looking at my old pagination code that I had awhile back, I was able to implement it to your codes and changed a few variables. Thanks again.

  2. Hi there!
    Great code as always…

    Instead of showing 10 pages in the pagination, is it possible to show only 2 before and 2 after the current page?

    Thanks

    1. Hi Geoffrey,

      Yes you can show any number of pages in your pagination. As you can see I have explicitly coded each conditions to show number of pages in pagination. You can change these conditions as per your need.

  3. Javed Bhai, Please Share the code or method by which I can created search box (search from the table (database table)).

    Actually, I have applied your logic for pagination and it is working suppppppper fine.
    But I need Seach box for this.
    could you please do me this favour.

    1. Dear Mirza, I am glad that you found my tutorial helpful. Unfortunately, currently I do not have any tutorial on search, I hope I will share one soon. For now you can search it on Google.

  4. I tried implementing a pagination system but the issues is that the number of pagination links or numbers shown gets bigger as the current page number gets bigger. The problem with this is on mobile devices where the pagination links/numbers begin to be pushed beyond the border of the screen. Do you know how I could modify where the total number of pagination links/numbers shown never gets bigger than 5 or 6?

  5. I am using code in a WordPress instance. Pagination buttons show, but when clicked, I have no results displayed for clicked pages. What might I be missing?
    I have had issues with WP before because of no /index at the end of the URLs. Could this be an issue?

  6. Great tutorial. I converted your tutorial into a wordpress plugin for my web app so as to display custom data logs.

  7. Thanks a lot for script.. Its very simple and helpful.. I tried your script and the code ran successfully without errors.
    Really thanks

  8. I have to use pagination on the table along with the checkbox, like if we check this box this table will appear and so on….. please help me I got stuck in between.

  9. This is a great. However, am new to php etc.
    I have tried to include a ‘Search’ or ‘Filter’ to the script but without success.
    Any help would be greatly appreciated. I’ve spent hours trying.

  10. Great script! Very easy to use and implement. I searched for DAYS to find an easy one to use.

    Do have a question – I want to use the script to display multiple queries from a database, each with its own paginated table. What I’m finding is that if I click Next (or a page number) all tables on the page go to the page clicked instead of just the table I clicked on. Hope that makes sense.

    Is there a way to separate the tables so that the pagination links only work for the table they are under?

    Thanks again for the awesome script!

  11. How to resolve this?

    Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in /storage/emulated/0/htdocs/admin/index.php on line 42

    Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in /storage/emulated/0/htdocs/admin/index.php on line 48

    1. try this:
      $result_count = “SELECT COUNT(*) As total_records FROM `your_table`”;
      $results = $db->query($result_count);//this line missing
      $total_records = $db->fetch_array($results);

  12. Thanks so much for this, I implemented this in my project but the links doesn’t work? Any Help to get that done?

    1. Well, I suggest first try to download and run my demo on your localhost, if it is working fine then try to integrate it in your project, and for explanation kindly read my complete tutorial, each steps are clearly explained.

  13. I implemented this demo as it is.
    But I have an issue in show records per page. it’s making pages as per total records. But showing all records on one page.
    i think $total_records_per_page is not working here

  14. Hi, I’m doing a project in which i stored my input data in mysql and images in my directory. How to fetch data from mysql and images from directory using php as im unable to do it. Plz help

    1. Dear Awais,

      Have you tried to run the demo of my code before making any changes in it. Reduce the number of records per page such as from 3 to 1 records per page and then check your pages again.

      There is no such restriction on my code to stop working on any specific page.

  15. I cannot thank you enough for this code. I am currently an intern and was in search for this concept since 2-3 days, luckily I got your demo, its really very helpful for me as I am working on PHP and now I have implemented your demo in my own way. Thanks a lot! Keep sharing good stuffs!

  16. Thank you very much for the great tutorial.

    I have one question.
    The page buttons only work after second click and not first click.
    Could you help me understand why?

  17. Thank you from bottom of my heart, i am python back-end developer but for freelancing in php for my final production update only this feature was remaining , and i was having only 5 hours but after finding your post i could develop and customize as per my requirement , Thank you. #thanksFromIndia

  18. Thank you very much Sir for making it so simple to understand.

    I copied your idea in my project but made some changes as follows:

    ———— Your codes

    1){
    echo “First Page“;
    } ?>

    <li <?php if($page_no >
    <a 1){
    echo “href=’?page_no=$previous_page'”;
    } ?>>Previous

    <li = $total_no_of_pages){
    echo “class=’disabled'”;
    } ?>>
    <a <?php if($page_no >Next

    <?php if($page_no < $total_no_of_pages){
    echo "Last ››“;
    } ?>

    ——–>> I changed it to >>>>


    1)
    {
    echo “First Page“;
    echo “Previous“;
    } ?>
    <?php
    if($page_no < $total_no_of_pages)
    {
    echo "Next“;
    echo “Last ››“;
    } ?>

  19. Javed bhai is pagination code ma hum active button ka background color ksy display kren gy aur active button ki class kidr put karen gy.plzz help me.

  20. Nice work bro,
    I have a problem I changed the link of my pagination using .htaccess I changed
    ?page=$counter to page/$counter
    Now when I click next of button it’s works fine but when I try to click page 1 it has a brief of ../page/page/1 help

    1. Well i didn’t create the pagination along with htaccess so i do not recommend to rewrite URL, if you are doing so it will not work as we are getting data from the URL parameters which may not be available after changing URL.

  21. Thank you very much on this beautiful pagination example.

    HERE IS A LITTLE help to someone who wants to parse data with $_GET method.

    Look at href url, after counter in url just add ?value=datatoparse.

    <?php
    if ($total_no_of_pages <= 10){
    for ($counter = 1; $counter <= $total_no_of_pages; $counter++){
    if ($counter == $page_no) {
    echo "$counter“;
    }else{
    echo “$counter“;
    }
    }
    }
    elseif($total_no_of_pages > 10){

    if($page_no <= 4) {
    for ($counter = 1; $counter < 8; $counter++){
    if ($counter == $page_no) {
    echo "$counter“;
    }else{
    echo “$counter“;
    }
    }

    Also do the same on url for next_page and previous_page.

    To show proper range number of pages just use EXPLODE od page number variable.

    $number_of_pages_without parse data = explode(“?”, $page_no);
    echo $number_of_pages_without parse data[0];

    On start of page add $somevariable=$_GET[‘value’];
    echo $somevariable;

    For any further help feel free to ask!

  22. In the download version there is a commenting of :
    1){ echo “First Page“; } ?>

    With or without removing the comment when there is only 1 page the pagination shows.
    Previous1Next

  23. Hi Javed,

    I think this is the best tutorial here for pagination. You don’t know how many websites i explored to find something good but i think this is the easiest and best tutorial. With some logic from us and a good UI, we can make it better and better. Thank you

    Do you have patreon or payoneer? If you don’t mind, i would like to buy a coffee for you great tutorial ๐Ÿ™‚

  24. Bro, it’s clean and crisp . Thanks a lot . I was hitting my head for about two days in wall. No optimized solution was found. But yours work like smooth jelly. Kudos.

  25. Once again great tutorial. Just a heads up. I had issues getting the pagination styling to work on my site. My bootstrap.min.css din’t have all the pagination classes. So remember to check your css file if the styling does not apply.

  26. Hi Great Tutorial..This might be a silly query however can this pagination work without refreshing the whole page.

      1. Please update it with ajax without refreshing the page. Or add new tutorial for same pagination with ajax.
        thank you

    1. The calculations with the offset value was a little bit confusing. But in all thanks for the effort you put into this.

  27. Hi Javed,
    thanks for the clearly explained tutorial!

    Could you tell me how I could pass a value from a list using $_GET to query my db, eg. (SELECT FROM..WHERE $_get_value=’column_1′ …) and also use your $_GET $page_no for the pagination?
    When I try to use to use 2 $_Get the script breaks!
    Thanks,
    Martin

    1. Hi Martin,
      You will need to edit the pagination buttons, check a href in these link and update your parameter to use the GET to query your db and then it will not break the pagination.

  28. PLUS, if you want to order descending your result, only do this in the select:

    ORDER BY column DESC LIMIT $total_records_per_page OFFSET $offset

  29. Nice tutorial, I try running the script but having error. I am using a join table to display results on the page. I am having this error ”
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”contractors’ WHERE status = 1′ at line 1″ I don’t really know what to do do. Please I need your guide on if i was querying from two tables, how will the paging syntax like?

    1. Hi Jackson,
      Well i would recommend you to make sure that your join query is correct. As you are getting query related error which means you will need to run the query on MQSQL first then execute it on php page. This will help you to find the error of query.

  30. Thanks for this useful tool
    I have a problem:
    if total records in my mysqltable > $total_records_per_page*10
    pages number, next previous are not displayed correctly
    and targets links are not correct

    1. First simply try to run the tutorial without any modification if it is working then you can do modification as per your need.
      No matter how many records do you have, it will be manage itself, even if there is 1 record or 1000 records.

  31. I use this step to do my Pagination. But the problem I have is, if am clicking on page 2 it will not list the items for page 2 for me. Why?

    1. Download the entire tutorial and first try to run the same before making any editing in it. You will need to debug it if you are still not getting results, make sure you are using the updated PHP version.

  32. Hi, I like your tutorial, thank you.
    Question: Why there is a “…” between “2” and “3”.
    I select page 5 in your demo. Is this a bug?
    How can I change the code?
    best regards
    chris

    1. Hi Chris, this is not a bug, when you keep clicking the next button the gap will move on 2 and 4, 2 and 5 etc, this means that there is more numbers between then, yes it should not be between 2 and 3 but, i tried to keep it simple, you can use additionally check to remove this between 2 and 3.

  33. I was looking around for the pagination and found it best solution in PHP & MySQL. I have easily integrated all the code and added to my website. You really did a great job. Well done.

  34. Thank you for your knowledge, sir. Great to follow your web and your facebook Sir ๐Ÿ˜€
    Sir, can I ask you how to display numbers in your table in sequence even though it’s the next page?
    I tried but when the next page opened the number repeated 1 again..
    Thankyou

  35. AOA,Sir i am fetch data dynamically by using date as input when moved to next page it display undefined variable error

    Notice: Undefined variable: dt2 in C:\wamp\www\demo\index.php on line 43

    Notice: Undefined variable: dt2 in C:\wamp\www\demo\index.php on line 49
    ID Name Age Department
    Page 2 of 0
    PreviousNext

    1. WAS Nargis, well it seems like you are using some variable which is not defined, kindly try to set up first my demo tutorials, and check if this is working or not then create another copy of tutorial and makes on it as per your requirement. So that you can track error.

  36. Thanks for the script.

    I want to display News which will not be in the table,

    I will have the News Image and News Title.

    how can it be done?

  37. If I wanted to add this to a search engine, how can I go about it?
    If you can, please, can you do one with a search engine in it.

  38. Hi Javed
    very good your pagination tutorial, helped me a lot, lay in PHP … but in question, I tried to adapt and for lack of knowledge, I ask you :::

    https://www.allphptricks.com/create-simple-pagination-using-php-and-mysqli/

    know how to add, LIMIT the pagination in 10, type: 1 2 3 4 5 6 7 8 9 10,
    20 21 22 23 …..

    so that the pagination does not expand too much?

    if yes, I would like a return.
    if you have your example, I would like the link, ok.

  39. Hi, Javed,
    That was simply amazing. So many thanks. Deeply appreciate.

    You also know sir, that when search criteria come from user inputs, the code breaks. Could you advise on:
    1) how to fit the entire code within SQL prevention conditions.
    2) how the script can retain/remember search parameters during pagination.
    3) incorporate multiple search keywords from multiple fields in a Table.
    Looking forward to your help sir.

  40. This is the most beautiful, easy, well explained, very solid data grid I’ve found. And belieeeeeeeeeeeve me, I did research for a lot (I mean, like 4 days straight) of these components in the internet.

    So, congratulations sir. You are the man.

    May I request a little bit of help, Sir?

    Can you post a tutorial to add some search/crud functionalities in a responsive way, while keeping it’s simplicity and well explained code… ยฟ?

Leave a Reply

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