What is Pagination in PHP? Understanding The Fundamentals

What is Pagination in PHP?

Pagination is the process of displaying the data on multiple pages rather than showing them on a single page. You usually do pagination when there is a database with numerous records. Dividing those records increases the readability of the data. It can retrieve this data as per the user’s requests. The most common example of pagination is the result page of a Google search. A search query entered into the search bar can fetch thousands of results. And it divides these results into multiple pages. You can click on any page number given at the bottom to visit that page. 

Advantages of Pagination

The process of pagination in PHP serves the following advantages:

• Pagination in PHP is best suited for projects that require a sizeable amount of data because it adds professionalism to the work. Apart from that, it increases the readability of the project by segregating the code into pages and makes it look clean.
• It makes the web pages work more efficiently. Pagination in PHP makes web pages load in much lesser time, thus saving a lot of time and data. You will understand this point better with a real-life example. 

Example: Suppose you want to access a website of images and there are two websites in front of you: the first one has 2000 images on a single web page while the other one has 20 web pages with 100 images on each page. Here, the website with a single page takes a lot more time to load as compared to the other website, because the server has to make a lot of HTTP requests and the page could become unresponsive. Whereas, pagination allows you to display a limited amount of data on a single page that in return limits the HTTP requests. Thus putting a lesser load on the server.

• Since pagination allows the data to be distributed on several pages, it creates a great opportunity to generate huge revenue by increasing the advertisements on each page of the website.

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Disadvantages of Pagination

Although pagination serves many powerful advantages, it has some downfalls as well. It is common to face the following disadvantages, it has some downfalls as well. The following disadvantages are faced while using pagination in PHP.

• Since pagination distributes the data and creates several pages, it may result in a low-rank page. This is because if a page is not directly linked to the home page and requires several clicks, the search engine ranks it on a lower level.
• One of the main reasons why pagination is avoided in many cases is that it is considered an enormous overhead in PHP. It adds unnecessary indirect costs of styling, markup, data segregation, and logic to a project. Projects with small databases mostly ignore pagination.
• It can also make the search for specific data difficult. To understand this point much more clearly,  consider this real-life example. 

Example: Suppose an exam has been conducted at your university and the results were uploaded to the university website. Now, you want to check your result specifically. It is already understood that a university has thousands of students segregated according to the branch and class. In this case, if the university has done pagination of the data then it must distribute the data on several pages and you have to check every page for your name unless the site has a “search by” algorithm. The addition of such an algorithm seems hard to implement most times and can not be achieved so easily. Therefore, it is best to avoid pagination in such cases.

Our Free Courses with Certificate

Introduction to PHP Basics	Enroll Now!
JavaScript for Beginners	Enroll Now!
Getting Started with Full Stack Java Development	Enroll Now!
Fundamentals of MERN Stack	Enroll Now!
Fundamentals of Software Development	Enroll Now!

Implementing Pagination with PHP and MySQL

The first and the most important step to implement pagination is to create a large set of databases. So, you will first create a database on which you will apply pagination. After this, you will segregate the records as tables so that you can distribute the data over several pages.

The steps to follow to implement pagination with PHP and MySQL are:

STEP1: Create a large-scale database.

STEP2: Create a table and insert the list of records in it.

STEP3: Create a link for pagination to segregate/distribute the data over several different pages and add it to the bottom of the table created in step 2.

STEP4: Retrieve the data from the table and show it on the different pages. 

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Examples 

Now, understand pagination in PHP with the help of the following examples. The first example is the basic version illustrating pagination in PHP without using CSS styling. The second example shows a complete pagination example using CSS and bootstrap. 

Example 1

In this example, you will create a simple database that has the names of some countries. You will paginate the results fetched from this database and display them on a webpage.

Step 1: Create a Database

Create a database in phpMyAdmin using the XAMPP server control panel. Name the database as “Pagination” and the table as “Countries”. Create 2 columns:

1. ID - int 
2. Country - varchar(20)

Populate the table with random country names and insert up to 20 rows.

Step 2: Connect the Database

Now establish a connection between your PHP file and the database. After you make the connection, you can access the records of the database by using the PHP code.

To connect your PHP file with the database, use the following code. You can insert this code directly into your project file or you can also insert it into a separate file.

$conn = mysqli_connect('localhost', 'root', '');      // root is the default username     // ' ' is the default password    if (! $conn) {               die("Connection failed" . mysqli_connect_error());      }      else {               // connect to the database named Pagination             mysqli_select_db($conn, 'Pagination');      }

The mysqli_connect() function accepts the following arguments:

• Name of the server: The default server is localhost.

• User name of the database: By default, the username is ‘root’ for all the databases.

• The password of the database: By default, the password is blank. So, pass a blank string to the function. 

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Step 3: Retrieve the Currently Active Page Number

Retrieve the page number that is being visited currently. It assigns a value of 1 to the default page number.

 if (!isset ($_GET['page']) ) {          $page_number = 1;      } else {          $page_number = $_GET['page'];      } 

Step 4: The Formula for Pagination

Set the limit of rows to be displayed per page. You have 20 rows, so you will divide these rows into 5 rows per page. The total required pages will be 4. The following code sets the required formula for pagination in PHP:

// variable to store the number of rows per page$limit = 5;       // get the initial page number      $initial_page = ($page_number-1) * $limit; 

The variable $initial_page stores the initial page number. This will retrieve the required number of rows. 

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Step 5: Get the Total Number of Pages

To retrieve the total number of pages required to display the database with a limit of 5 rows per page, use the code mentioned below:

 // query to retrieve all rows from the table Countries    $getQuery = "select *from Countries";      // get the result    $result = mysqli_query($conn, $getQuery);      $total_rows = mysqli_num_rows($result);     // get the required number of pages    $total_pages = ceil ($total_rows / $limit);

You can determine the total number of pages by using the formula: 

=(number of rows in database / rows per page)

So, in this case the total number of pages required will be:

=(20 / 5)

= 4 pages.

Step 6: Fetch the Required Data and Display it on a Webpage

Now display the database with its rows being divided into multiple web pages. A query can be used with the LIMIT clause along with specifying the limit of the rows to be displayed per page. The following code fetches the data of the specified rows from the database and displays the result on a webpage.

//retrieve the selected results from database       $getQuery = "SELECT *FROM Countries LIMIT " . $initial_page . ',' . $limit;      $result = mysqli_query($conn, $getQuery);          //display the retrieved result on the webpage      while ($row = mysqli_fetch_array($result)) {          echo $row['ID'] . ' ' . $row['Country'] . '</br>';      }

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Step 7: Show the Page Number in the URL 

Display the page numbers at the bottom of each page and update the URL with the page numbers. The following code inserts the page numbers with a link to each page:

 // show page number with link   for($page_number = 1; $page_number<= $total_pages; $page_number++) {          echo '<a href = "index.php?page=' . $page_number . '">' . $page_number . ' </a>';      }

The Complete Code

The above code snippets can be combined in a single PHP file. The following is the combined code to do pagination in PHP. Save this code as a PHP file named “index.php” and run it in localhost.

<html>  <head>  <title> Pagination in PHP </title>  </head>  <body>   <?php      $conn = mysqli_connect('localhost', 'root', '');      // root is the default username     // ' ' is the default password    if (! $conn) {               die("Connection failed" . mysqli_connect_error());      }      else {               // connect to the database named Pagination             mysqli_select_db($conn, 'Pagination');      }      // variable to store number of rows per page    $limit = 5;      // query to retrieve all rows from the table Countries    $getQuery = "select *from Countries";        // get the result    $result = mysqli_query($conn, $getQuery);      $total_rows = mysqli_num_rows($result);        // get the required number of pages    $total_pages = ceil ($total_rows / $limit);        // update the active page number    if (!isset ($_GET['page']) ) {          $page_number = 1;      } else {          $page_number = $_GET['page'];      }        // get the initial page number    $initial_page = ($page_number-1) * $limit;       // get data of selected rows per page        $getQuery = "SELECT *FROM Countries LIMIT " . $initial_page . ',' . $limit;      $result = mysqli_query($conn, $getQuery);           //display the retrieved result on the webpage      while ($row = mysqli_fetch_array($result)) {          echo $row['ID'] . ' ' . $row['Country'] . '</br>';      }        // show page number with link       for($page_number = 1; $page_number<= $total_pages; $page_number++) {          echo '<a href = "index.php?page=' . $page_number . '">' . $page_number . ' </a>';      }    ?>  </body>  </html> 

Output 

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Example 2

In this example, you will create a database containing the names of some items and their categories. You will format the database table and the web pages using CSS. Then, you must paginate this database and display the result on a webpage.

Step 1: Create a Database.

Create a database in phpMyAdmin using the XAMPP server control panel. Name the database as “myDatabase” and the table as “Items”. Create 4 columns:

1. ID - int 
2. Name - varchar(20)
3. Category - varchar(20)
4. Price - int

Insert 15 rows and populate data in the table. 

Step 2: Connect the Database

The same code used in the above example can be used to establish the connection. The following code connects the database “myDatabase” to the PHP file.

 // Connect to the database        $conn = mysqli_connect('localhost', 'root', '');          if (! $conn) {              die("Connection failed" . mysqli_connect_error());              }                  else {              mysqli_select_db($conn, 'myDatabase');          }

Step 3: Retrieve the Currently Active Page Number

Get the page number of the current page using the isset() method. 

 // update the active page number        if (isset($_GET["page"])) {                $page_number  = $_GET["page"];            }            else {              $page_number=1;            }

Step 4: Display the Table Records

Display the table records of your database.

<?php                 while ($row = mysqli_fetch_array($result)) {                      // Table head            ?>                 <tr>                 <td><?php echo $row["ID"]; ?></td>                 <td><?php echo $row["Name"]; ?></td>               <td><?php echo $row["Category"]; ?></td>               <td><?php echo $row["Price"]; ?></td>                                                       </tr>                 <?php                     };            ?>   

Step 5: Show Page Number in the URL

Display the page numbers in the URL of each page.  The above code updates the URL with page numbers. It also inserts a “previous” and a “next” link with the page numbers.

if($page_number>=2){               echo "<a href='index.php?page=".($page_number-1)."'>  Prev </a>";           }                                 for ($i=1; $i<=$total_pages; $i++) {             if ($i == $page_number) {                 $pageURL .= "<a class = 'active' href='index.php?page="                                                  .$i."'>".$i." </a>";             }                         else  {                 $pageURL .= "<a href='index.php?page=".$i."'>                                                   ".$i." </a>";               }           };             echo $pageURL;            if($page_number<$total_pages){               echo "<a href='index.php?page=".($page_number+1)."'>  Next </a>";           }

The Complete Code

The following code is the combined code with the CSS to format the webpage and the table. It fetches the data of the 15 rows from the database and displays 5 rows per page. 

<html>     <head>       <title>Pagination in PHP</title>       <link rel="stylesheet"      href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">       <style>       table {          border-collapse: collapse;      }          .inline{               display: inline-block;               float: right;               margin: 20px 0px;           }                    input, button{               height: 34px;           }        .items {           display: inline-block;       }       .items a {           font-weight:bold;           font-size:18px;           color: black;           float: left;           padding: 8px 16px;           text-decoration: none;           border:1px solid black;          margin: 2px;     }       .items a.active {               background-color: rgba(175, 201, 244, 0.97);       }       .items a:hover:not(.active) {           background-color: #87ceeb;       }           </style>     </head>   <body>     <center>      <?php              // Connect to the database        $conn = mysqli_connect('localhost', 'root', '');          if (! $conn) {              die("Connection failed" . mysqli_connect_error());              }                  else {              mysqli_select_db($conn, 'myDatabase');          }               // variable to store number of rows per page        $limit = 5;            // update the active page number        if (isset($_GET["page"])) {                $page_number  = $_GET["page"];            }            else {              $page_number=1;            }               // get the initial page number        $initial_page = ($page_number-1) * $limit;               // get data of selected rows per page         $getQuery = "SELECT * FROM items LIMIT $initial_page, $limit";             $result = mysqli_query ($conn, $getQuery);        ?>         <div class="container">         <br>         <div>           <h1> Pagination in PHP </h1>            <table class="table table-striped table-condensed                                              table-bordered">             <thead>               <tr>                 <th width="10%">ID</th>                 <th>Name</th>                 <th>Category</th>                 <th>Price</th>               </tr>             </thead>             <tbody>       <?php                 while ($row = mysqli_fetch_array($result)) {                      // Table head            ?>                 <tr>                 <td><?php echo $row["ID"]; ?></td>                 <td><?php echo $row["Name"]; ?></td>               <td><?php echo $row["Category"]; ?></td>               <td><?php echo $row["Price"]; ?></td>                                                       </tr>                 <?php                     };                ?>               </tbody>           </table>         <div class="Items">          <?php          $getQuery = "SELECT COUNT(*) FROM Items";             $result = mysqli_query($conn, $getQuery);             $row = mysqli_fetch_row($result);             $total_rows = $row[0];                  echo "</br>";                    // get the required number of pages        $total_pages = ceil($total_rows / $limit);             $pageURL = "";                     if($page_number>=2){               echo "<a href='index.php?page=".($page_number-1)."'>  Prev </a>";           }                                  for ($i=1; $i<=$total_pages; $i++) {             if ($i == $page_number) {                 $pageURL .= "<a class = 'active' href='index.php?page="                                                  .$i."'>".$i." </a>";             }                         else  {                 $pageURL .= "<a href='index.php?page=".$i."'>                                                   ".$i." </a>";               }           };             echo $pageURL;            if($page_number<$total_pages){               echo "<a href='index.php?page=".($page_number+1)."'>  Next </a>";           }           ?>          </div>          <div class="inline">         <input id="page" type="number" min="1" max="<?php echo $total_pages?>"         placeholder="<?php echo $page_number."/".$total_pages; ?>" required>         <button onClick="go2Page();">Go</button>        </div>        </div>     </div>  </center>     <script>       function go2Page()       {           var page = document.getElementById("page").value;           page = ((page><?php echo $total_pages; ?>)?<?php echo $total_pages; ?>:((page<1)?1:page));           window.location.href = 'index.php?page='+page;       }     </script>    </body>   </html>  

Output

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

How to Implement Ajax-Based Pagination?

Pagination in PHP can also be done using an Ajax-based method. Using Ajax, you don’t have to refresh the page every time you go to a different page. So, for example, if you have your data divided into 4 pages. Then you can visit any page without refreshing it. In this method, you need to create the following three files:

1. index.php
2. database.php
3. pagination.php

Step 1: Create an index.php file

Create an index.php file to include AJAX which will allow you to visit a page without refreshing it. It will trigger the click event and load the page without refreshing it. This also makes the loading of the web pages faster. Insert the following code into the index.php file.

<?phpinclude('database.php'); $limit = 4;$query = "SELECT COUNT(*) FROM items";  $result = mysqli_query($conn, $query);  $row = mysqli_fetch_row($result);  $total_rows = $row[0];  $total_pages = ceil($total_rows / $limit); ?><!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1"><title>PHP Pagination AJAX</title><link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round"><link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons"><link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css"><link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"><link rel="stylesheet" href="css/style.css"><script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script><script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script></head><body>    <div class="container">        <div class="table-wrapper">            <div class="table-title">                <div class="row">                    <div class="col-sm-12">                       <h2 align = "center">Pagination in PHP using AJAX</h2>                                   </div>                </div>            </div>            <div id="target-content">loading...</div>                       <div class="clearfix">                                  <ul class="pagination">                    <?php                     if(!empty($total_pages)){                        for($i=1; $i<=$total_pages; $i++){                                if($i == 1){                                    ?>                                <li class="pageitem active" id="<?php echo $i;?>"><a href="JavaScript:Void(0);" data-id="<?php echo $i;?>" class="page-link" ><?php echo $i;?></a></li>                                                                                           <?php                                 }                                else{                                    ?>                                <li class="pageitem" id="<?php echo $i;?>"><a href="JavaScript:Void(0);" class="page-link" data-id="<?php echo $i;?>"><?php echo $i;?></a></li>                                <?php                                }                        }                    }                                ?>                    </ul>                           </div>        </div>    </div>    <script>    $(document).ready(function() {        $("#target-content").load("pagination.php?page=1");        $(".page-link").click(function(){            var id = $(this).attr("data-id");            var select_id = $(this).parent().attr("id");            $.ajax({                url: "pagination.php",                type: "GET",                data: {                    page : id                },                cache: false,                success: function(dataResult){                    $("#target-content").html(dataResult);                    $(".pageitem").removeClass("active");                    $("#"+select_id).addClass("active");                                  }            });        });    });</script></body></html>

Step 2: Create a database.php file

Create a file to connect your PHP files to the database “myDatabase”. It creates a separate file, to simply import it into all other files instead of inserting the connection code in each file. Insert the following code into this file and save it.

<?php    $server_name = "localhost";    $user_name = "root";    $password = "";    $db="myDatabase";    $conn = mysqli_connect($server_name, $user_name, $password,$db);    if (!$conn) {        die("Connection failed: " . mysqli_connect_error());    }?>

Step 3: Create a pagination.php file

Create a pagination.php file which will contain the code to fetch the table records and paginate them to display the data into multiple pages. Insert the following code into this file:

<?phpinclude('database.php');$limit = 5;  if (isset($_GET["page"])) { $page_number  = $_GET["page"]; } else { $page_number=1; };  $initial_page = ($page_number-1) * $limit;  $sql = "SELECT * FROM items LIMIT $initial_page, $limit";  $result = mysqli_query($conn, $sql);  ?><table class="table table-bordered table-striped">  <thead>  <tr>  <th>ID</th>  <th>Name</th>  <th>Category</th>  <th>Price</th>  </tr>  </thead>  <tbody>  <?php  while ($row = mysqli_fetch_array($result)) {  ?>              <tr>              <td><?php echo $row["ID"]; ?></td>              <td><?php echo $row["Name"]; ?></td>            <td><?php echo $row["Category"]; ?></td>            <td><?php echo $row["Price"]; ?></td>            </tr>  <?php  };  ?>  </tbody>  </table>

To display the output in a webpage, save all these files in a project folder and run the “index.php” file in a localhost server. 

Output

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Final Thoughts!

To sum up, in this article, you learned the ins and outs of pagination in PHP. You looked at how to implement pagination using PHP and MySQL using hands-on examples. You understood the advantages and disadvantages of using PHP in your website. In the end, you glanced at a simple solution to implement Ajax-based pagination as well.

If you want to know more about PHP and start your comprehensive PHP learning journey, you can refer to this video as a guide. Moreover, Simplilearn’s online course on PHP Training will definitely help you understand the fundamental concepts of PHP and you will be able to create advanced dynamic web apps instructed by industry experts and professionals. 

If you are already a PHP professional, we have a specially crafted Full Stack Developer - MERN Stack that you leverage to learn high-level PHP concepts such as sessions, cookies, etc.

Why stop here? To give yourself a chance to work as a professional developer, you should definitely try out our MEAN stack development and give yourself a chance to work for top tech giants. Check out our course on Full Stack Java Developer. This will help you to learn some of the most trending skills like Node, Mongo, etc. If you want to get a complete list of free courses offered by Simplilearn, visit Free Courses.

If you have any questions for us regarding this “Pagination in PHP” article, please mention them in the comments section and our experts will answer them for you.

Happy Learning!