Create Simple Pagination Using PHP and MySQLi

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.

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.

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

1	SELECT * FROM `pagination_table` LIMIT 3 OFFSET 4;

Or you can use shorthand method as well.

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

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

1 2 3 4 5 6 7

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.

1 2 3 4 5 6

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

1 2 3 4 5

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.

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

1 2 3 4

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

1 2 3 4 5 6 7 8

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

1 2 3 4 5 6 7 8 9 10 11 12 13

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

1 2 3

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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

1 2 3 4 5 6 7 8 9

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

1 2 3

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

1 2 3 4 5 6 7 8 9 10 11 12

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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.

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.