PHP Pagination

Pagination is a system that select data and puts them on more pages. It is very important because if you want to display a list from a database with all records and it has over one hundred or one thousand of records will be a big problem to see all this. This problem maybe solved with the aid of a pagination system.


The follow exemple will get fifteen users from a database and puts it in a table with three pages. To create a pagination system we need to go  through the following tasks:

  1. Crate a file called "db.php" where we make a connection to database to get users.

  2. Create a file called "pagination.php" where we make  a pagination system .

  3. Create a file called "index.php" where we display the results.

Let's do it!

In the "db.php" file :
 

In this file we make a connection to database to get users. For this connection we will use PDO(PHP Data Object) connection to MySQL.

$servername = "localhost";
$username = "root";
$password = "";
try {
    $conn = new PDO("mysql:host=$servername;dbname=test", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e){
        echo "Connection failed: " . $e->getMessage();
        exit;
    }

Ass you can see, the database used is "test".  If connection will has errors this will be showed.

 

In the "pagination.php" file :


 In this file we make a pagination system that put all records into table with three pages. Firstly we need to include the "db.php" file  here to can get information from database. We will do this with "require" function:

require "db.php";

The next step is to  define a variable that contain the limit of rows in table on a page and a another variable that will get number of the page.

$limit = 5;
$page;

The variable "$limit"  contain  limit of rows from a table and the variable "$page" in the following step will get the number of the page.

The next step is to check if "$_GET['p']" is not set. If this condition is true  this variable will get value "1" and we will define a new variable called "$start" that get the value "0" because this values will help us at next steps. If condition is false  the variable "$start" will contain a operation.

if(!isset($_GET['p'])){
    $page     = 1;
    $start    = 0;
}else{
    $page = $_GET['p'];
    $start = $limit*($page - 1);
}

Now, what means this code ?

If the condition is true "$page" will be equal with "1". That means that  the table will show the first page and the variable "$start"will be equal with "0", that means that we will select  information from database starting with the first row of this. If the condition is false the variable "$start" will contain the following code:

$limit*($page - 1); 

This code will get  the number of rows from which we will get information from database. For exemple:  $limit =5;   $page =2;   and in database we have 15 rows recorded,  the result of this operation will be 5. In other words we select from database starting with the row five.

Now that we defined this condition we will need get  all  rows from database to count it.

$tot = $conn->prepare("SELECT * FROM users"); 
$tot->execute();
$RowCount = $tot->rowCount();

The variable "$tot" contain the query for  database and "$RowCount"  will get the number of rows.

In the next step we need to division the number of rows from database at limit of rows in table. In our case we will division the number of rows from database for exemple 15 at limit of rows in table for exemple 5.

$number_of_pages =  ceil($RowCount / $limit);

As you can see, the variable "$number_of_pages" contain the number  of page that wee will display in the next stap. The function "ceil()" is used to round up the value because do not can exist  a page like 2.5 or 1.3.

In this file we need to go  through the following tasks:

  1. Create a query that select rows from database with a limit of five rows starting from a defined value
  2. Create a function that show the list with all page of table

 

1) The query :


This query must select a limited part of rows from database, in our case five rows.


$users =  $conn->prepare("SELECT * FROM users LIMIT $start, $limit"); 
$users->execute();
$users = $users->fetchAll(PDO::FETCH_ASSOC);

As you can see, this query will select all columns from our table but limited rows. We limit the rows with clause  "LIMIT". This clause can be used to specify the number of records to return or number of records with the position from which to select a part of rows. For exemple we want to select five rows  starting on record 10.

 

2) Function :


This function must show a list with all page of table, in our case three page because we have 15 records in our database and this records will be divide  with our variable (five). The function should look like this:

function paginate($page,$num_page){
    echo "<ul>";
    for($i=1;$i<=$num_page;$i++){
        if($i == $page){
            echo "<li class='active'><a href='index.php?p=$i'>".$i."</a></h2></li>";
        }else{
           echo "<li><a href='index.php?p=$i'>".$i."</a></li>";
        }
    }
    echo "</ul>";
}

This function return a list with all pages of table. As you can see this has a condition that must verify if the variable "$i" is equal with "$page". If condition is true, it means that we are on this page, else we are not on those pages and we can custom  the page  that we are, and the other pages can also be custom.

 

In the "index.php" file :


In this file we will to display the table. In the first step we must  to include the "pagination.php" file. After that we can create a table and  we can add the value with the variable "$users". At the end of our body we must call back function "paginate()". Your file must look like this:

<!DOCTYPE html>
<html>
<head></head>
<body>
    <h1>Users</h1>
    <?php 
        require "pagination.php";
        echo "<table>";
        echo "<tr>";
        echo "<th>Id</th>";
        echo "<th>Name</th>";
        echo "<th>Age</th>";
        echo "</tr>";
                
        foreach($users as $user){
            echo "<tr>";
            echo "<td>".$user['id']."</td>";
            echo "<td>".$user['name']."</td>";
            echo "<td>".$user['age']."</td>";
            echo "</tr>";
        }     
        echo "</table>";
        paginate($page,$num_page);
    ?>
</body>
</html>

Now we can add code for our CSS3 style in  the  tag "<head>" .

<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th, td {
            text-align: left;
            padding: 8px;
            font-size:20px;
        }
        tr:nth-child(even){background-color: #f2f2f2}
        th {
            background-color: #4484ce;
            color: white;
        }
        ul > li{
            list-style:none;
            display:inline-block;
            margin-left:0px;
        }
        ul > li > a{
            padding:5px 10px 5px 10px;
            border:1px solid #e2e2e2;
            color:#333333;
        }
        .active > a{
            background-color:#733fe2 ;
            color:#e2e2e2;
        }
</style>

Share on: