Pagination in php

Syed M Kashif September 08, 2021
simple pagination in php

Welcome dear readers to another article. We are going to build pagination in PHP from scratch. It's very essential functionality and almost all websites use it. It's because if our SQL select query returns hundreds or thousands of records, we can not display them all on a single page. Because it can cause a decline in our website performance as well as user experience. So, the best way is to split the records into different pages and then display them to the user to enhance the site performance as well as user engagement. Now let's learn simple pagination in PHP.

How to build simple pagination using PHP MYSQL?

It's very easy to create pagination in PHP. We will use SQL LIMIT and OFFSET clauses to split the records into different pages. Let's First understand what is LIMIT and OFFSET clauses in SQL. 

What are LIMIT and OFFSET?

Let's understand this concept with the help of an example. Consider we want to fetch 10 records per page from a database containing a large number of records. We will use the following SQL query with LIMIT constraint. 

SELECT * FROM TableName LIMIT 10

But the only limit clause is not sufficient. It will just return the first 10 records every time. But what about the next records from 10 to 20 or from 30 to 40. So, we need something to define where to start fetching the limited records. Here we need the OFFSET clause. 

Now consider another example to understand the OFFSET clause. If we want to fetch records from 20 to 30, then we will use both LIMIT and OFFSET clauses. The SQL query will be as follows. 

SELECT * FROM Tablename LIMIT 10 OFFSET 20

In this query, the OFFSET clause will skip the first 20 records, and then the next 10 records will be fetched because of the LIMIT clause.
  
Now Let's use this concept to build pagination in PHP. 

Steps to build simple pagination in PHP:

  • Create an HTML table to fetch records from a database.
  • Fetch the current page number from the $_GET array. 
  • Calculate the limit and offset and execute your SQL SELECT query.
  • Calculate total pages by dividing total records in the database by records per page.
  • Print the pagination buttons to navigate between different pages.
  • Print three dots if pages are greater than 5.
  • Add active class to page number buttons.
Now let's discuss all these steps one by one in detail.

How to create a table to fetch records from a database?

  • Create a table using an HTML table tag. Use bootstrap 5 classes to beautify your table.
  • Write the titles of all the table columns inside thead tag. 
  • Write tbody tag to fetch dynamic data here. PHP code will be written here later in this article.
    <table class="table table-bordered">
   <thead class="thead">
      <tr>
         <th scope="col">Title </th>
         <th scope="col">Author</th>
         <th scope="col">Description</th>
         <th scope="col">Date</th>
      </tr>
   </thead>
   <tbody>
   </tbody>
</table>

How to fetch the current page number?

  • First check if the $_GET variable exists with a parameter "page" using PHP isset() function.
  • If it exists, set the value of the $_GET['page'] to the variable page.
  • Otherwise, set the page variable to 1.
    • <?php
if (isset($_GET['page']))
{
    $page = $_GET['page'];
}
else
{
    $page = 1;
}

?>

 How to write SQL query with LIMIT and OFFSET clauses?

  • Start PHP script inside the tbody tag.
  • As always, connect to the database using the "db.inc.php" file.
  •  Set the limit of results per page in a variable "LimitPerPage".
  • Calculate the OFFSET value in a variable "offset".
  • Now write the SQL SELECT query containing LIMIT and OFFSET clauses.
  • Execute the query and display the records returned in a while loop.
    <?php
require 'includes/db.inc.php';

$LimitPerPage = 3;
$offset = ($page - 1) * $LimitPerPage;

$query = "SELECT * From book LIMIT $LimitPerPage OFFSET  $offset";
$result = mysqli_query($conn, $query);
while ($Data = mysqli_fetch_assoc($result)) {
?>
    <tr>
        <td><?php
            echo $Data['title'];
            ?></td>
        <td><?php
            echo $Data['author'];
            ?></td>
        <td><?php
            echo $Data['description'];
            ?></td>
        <td><?php
            echo $Data['date'];
            ?></td>
    </tr>

<?php
}

?>

How to calculate the total number of pages?

  • Write an SQL query to count all the rows of data present in the database using the count function.
  • Execute this query and fetch the number returned in a variable "records".
  • Calculate the total number of pages by dividing the records by the limit per page.
  • Use ceil() function here to round off the total pages to the nearest greater integer. 
    • <?php

    $CountQuery = "SELECT count(*) FROM book";
    $res = mysqli_query($conn, $CountQuery);
    $records = mysqli_fetch_array($res)[0];
    $totalpages = ceil($records / $LimitPerPage);
    mysqli_close($conn);

?>


How to print pagination buttons using PHP and bootstrap?

In this part, we will create three types of buttons. Next and previous, first and last, and page number buttons.
Next and Previous buttons: 
  • Create a button prev using HTML anchor tag. This button will decrease the variable page value by 1. If the page variable value is less than or equal to 1, this button will be disabled by adding bootstrap disabled class and setting the href attribute to "#".
    <a class="btn btn-primary <?php if($page <= 1){ echo 'disabled'; } ?>"
    href="<?php if($page <= 1){ echo '#'; } else { echo "?page=".($page - 1); } ?>">
    &lt;&lt;
 </a>
  • Use anchor tag and create the Next button. This button will increase the page variable by 1. It will be disabled if the page variable's value is equal to or greater than the "totalpages" variable.  
    • <a class="btn btn-primary <?php if($page >= $totalpages){ echo 'disabled'; } ?>"
    href="<?php if($page >= $totalpages){ echo '#'; } else { echo "?page=".($page + 1); } ?>">
    &gt;&gt;
</a>
First and Last buttons:
  • Create the first button using the anchor tag. Set the page parameter to 1 using the "href" attribute. This page will take the user to the first page of the fetched results. 
    • echo '<a class="btn btn-primary" href ="?page=1">First</a>';
  • Create one more button called last. This time set the page parameter to the "totalpages" variable. This button will the user to the last page. 
    • echo '<a class="btn btn-primary" href ="?page='.$totalpages.'">Last</a>';
Page number buttons:
  • Now use a for loop to display the page number buttons. The Loop will iterate until the loop counter  "num" becomes equal to the "totalpages" variable. 
    • <?php
for($num = 1; $num<= $totalpages; $num++) { 
     echo '<a class="btn btn-primary mx-2" href = "?page=' . $num . '">' . $num . ' </a>';
}
?>
  • These buttons will set the page parameter in the URL to the value of num.

How to print three dots if total pages are greater than 5?

This functionality is important because if we have more than 100 pages of data, we can not create a button for each of them. We will create just 5 buttons and then print 3 dots. The user will navigate through all the pages using the next and previous buttons. now let's see how to create them.
  • Check if the value of loop counter variable "num" is greater than 5 or not.
  • If it is greater than 5, create three dots using an anchor tag.  
    • if($num>5){
    echo '<a class="btn"> . . . </a>';
    break;
}
you can do lots of other variations but I am just giving you an idea.

How to add active class page number buttons?

  • Apply a check and see whether the loop count variable num is equal to the page variable containing the current page number.
  • Add active and btn dark bootstrap classes to the number buttons by embedding PHP inside the class attribute. 
    • if($page==$num){
echo '<a class="btn btn-dark active mx-2" href="?page=' . $num . '">' . $num . ' </a>';
} else{
echo '<a class="btn btn-primary mx-2" href="?page=' . $num . '">' . $num . ' </a>';
}
Now I am sharing the complete code of all the above-mentioned steps at once.

<!doctype html>
<html lang="en">

<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">

    <title>Pagination article</title>
</head>

<body>

    <?php

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

    ?>
    <div class="row justify-content-center my-5">
    <div class="col-8 text-center">
    <table class="table table-bordered">
        <thead class="thead">
            <!--writing column names -->
            <tr>
                <th scope="col">Title </th>
                <th scope="col">Author</th>
                <th scope="col">Description</th>
                <th scope="col">Date</th>
            </tr>
        </thead>
        <tbody>
            <?php
            require 'includes/db.inc.php';

            $LimitPerPage = 3;
            $offset = ($page - 1) * $LimitPerPage;

            $query = "SELECT * From book LIMIT $LimitPerPage OFFSET  $offset";
            $result = mysqli_query($conn, $query);
            while ($Data = mysqli_fetch_assoc($result)) {
            ?>
                <tr>
                    <td><?php
                        echo $Data['title'];
                        ?></td>
                    <td><?php
                        echo $Data['author'];
                        ?></td>
                    <td><?php
                        echo $Data['description'];
                        ?></td>
                    <td><?php
                        echo $Data['date'];
                        ?></td>
                </tr>

            <?php
            }


            ?>
        </tbody>
    </table>

    <?php

    $CountQuery = "SELECT count(*) FROM book";
    $res = mysqli_query($conn, $CountQuery);
    $records = mysqli_fetch_array($res)[0];
    $totalpages = ceil($records / $LimitPerPage);
    mysqli_close($conn);
    ?>
    <p><?php echo $page." of ".$totalpages." pages "; ?></p>
    <a class="btn btn-primary <?php if($page <= 1){ echo 'disabled'; } ?>"
    href="<?php if($page <= 1){ echo '#'; } else { echo "?page=".($page - 1); } ?>">
    &lt;&lt;
    </a>
    <?php
    echo '<a class="btn btn-primary" href = "?page=1">First</a>';
    for($num = 1; $num<= $totalpages; $num++) { 
         
        if($num>5){
            echo '<a class="btn"> . . . </a>';
            break;
        }
        if($page==$num){
        echo '<a class="btn btn-dark active mx-2" href = "?page=' . $num . '">' . $num . ' </a>';
        } else{
        echo '<a class="btn btn-primary mx-2" href = "?page=' . $num . '">' . $num . ' </a>';
        }  
    } 
    
    echo '<a class="btn btn-primary" href = "?page='.$totalpages.'">Last</a>';
    ?>
    <a class="btn btn-primary <?php if($page >= $totalpages){ echo 'disabled'; } ?>"
    href="<?php if($page >= $totalpages){ echo '#'; } else { echo "?page=".($page + 1); } ?>">
    &gt;&gt;
    </a>
    
    </div>
    </div>

</body>

</html>

Now let's see the result of all the above-explained code. I know you are curious about this part. 
pagination in phpRecommended:  User registration in PHP.

Here is the end of today's article. I hope you liked and enjoyed this special article. Thanks for your time and support.

 
Tags

Post a Comment

0 Comments