Simple pagination in PHP

In this article, you will learn how to create simple pagination in PHP and PDO. Pagination is important when we have to display large data on a single page. If you will listed all the data on the same page, that will become more confusing and also have high page loading time. Here is the PHP pagination example, in which we have fetched the data from the database using the latest PHP PDO and written PHP logic to set pagination on the fetched record. All the coding flow is mentioned step by step, that will make you easier to understand and implement.

STEP 1: In the first step, we have written database connection code. For this, we have created a MySQL table 'students' and inserted data in it as shown below. You can either use your existing database or copy & paste the below code in MySQL -

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `students` (`id`, `first_name`, `last_name`, `email`) VALUES
(1, 'John', 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(2, 'Soyam', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(3, 'Sohn', 'Gaga', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(4, 'Rita', 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(5, 'Rohn', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(6, 'Sayam', 'Mitra', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(7, 'Shyam', 'Mishra', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(8, 'Ryan', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(9, 'Rohan', 'Soy', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(10, 'Mita', 'Dahl', This email address is being protected from spambots. You need JavaScript enabled to view it.');

Next, we have created a PHP page configuration.php, where we have written database connection code using PHP PDO. You can either create this manually or copy paste this code. Only you will have to change the database, hostname, username and password with your database credentials and name.

<?php
   // define database related variables
   $database = 'db';
   $host = 'hostname';
   $user = 'username';
   $pass = 'password';

   // try to connect to database
   $db = new PDO("mysql:dbname={$database};host={$host};port={3306}", $user, $pass);

   if(!$db){

      echo "Error in database connection";
   }
?>

STEP 2: In the second step, we have created another PHP file name 'data.php'. In this page, we have set the data records limit on each page, start counter variable, next counter variable, previous counter variable. Like- on each page, we need to set the record limit to 4, so we have stored this in a variable $per_page, on the first page the page counter is 0, which is stored in $page_counter. If the user clicks on the next page, this counter increased by one and if the user clicks on the previous page, this counter decreased by one. We have passed these variables as WHERE clause to the SELECT statement. So that, exactly those amounts of data will fetch from the database that we have to display on the pagination page.

<?php 
    //include configuration file
    require 'configuration.php';

    $start = 0;  $per_page = 4;
    $page_counter = 0;
    $next = $page_counter + 1;
    $previous = $page_counter - 1;
    
    if(isset($_GET['start'])){
     $start = $_GET['start'];
     $page_counter =  $_GET['start'];
     $start = $start *  $per_page;
     $next = $page_counter + 1;
     $previous = $page_counter - 1;
    }
    // query to get messages from messages table
    $q = "SELECT * FROM students LIMIT $start, $per_page";
    $query = $db->prepare($q);
    $query->execute();

    if($query->rowCount() > 0){
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
    }
    // count total number of rows in students table
    $count_query = "SELECT * FROM students";
    $query = $db->prepare($count_query);
    $query->execute();
    $count = $query->rowCount();
    // calculate the pagination number by dividing total number of rows with per page.
    $paginations = ceil($count / $per_page);
?>

STEP 3: In the third step, we have created an 'index.php' file. This is the main file that we will call on the browser. This file contains mostly HTML code to display the records and pagination links on the pagination page numbers.

<html>
    <head>
        <title>Pagination</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
    </head>
    <body>
        <?php include_once 'data.php'; ?>
        <div class="table-responsive" style="width: 600px; border: 1px solid black; margin: 10px;">
            <table class="table table-striped" class="table table-hover">
                <thead class="table-info">
                 <th scope="col" class="bg-primary" >Id</th>
                 <th scope="col" class="bg-primary">First Name</th>
                 <th scope="col" class="bg-primary">Last Name</th>
                 <th scope="col" class="bg-primary">Email</th>
                </thead>
                <tbody>
                <?php 
                    foreach($result as $data) { 
                        echo '<tr>';
                        echo '<td>'.$data['id'].'</td>';
                        echo '<td>'.$data['first_name'].'</td>';
                        echo '<td>'.$data['last_name'].'</td>';
                        echo '<td>'.$data['email'].'</td>';
                        echo '</tr>';
                    }
                 ?>
                </tbody>
            </table>
            <center>
            <ul class="pagination">
            <?php
                if($page_counter == 0){
                    echo "<li><a href=?start='0' class='active'>0</a></li>";
                    for($j=1; $j < $paginations; $j++) { 
                      echo "<li><a href=?start=$j>".$j."</a></li>";
                   }
                }else{
                    echo "<li><a href=?start=$previous>Previous</a></li>"; 
                    for($j=0; $j < $paginations; $j++) {
                     if($j == $page_counter) {
                        echo "<li><a href=?start=$j class='active'>".$j."</a></li>";
                     }else{
                        echo "<li><a href=?start=$j>".$j."</a></li>";
                     } 
                  }if($j != $page_counter+1)
                    echo "<li><a href=?start=$next>Next</a></li>"; 
                } 
            ?>
            </ul>
            </center>    
        </div>  
    </body>
</html>

Screenshot: Pagination in PHP

Related Articles