Pagination in PHP & MySQL

Pagination in PHP & MySQL

In this blog, you will learn how to create pagination in PHP. Pagination is very useful when you want to show limited data on a page and you want to show the remaining data on a button click, it is mostly used for blogs, eCommerce products, and web applications.

Let’s see the following steps to create pagination:

  • Create a Database.
  • Create a table.
  • Create a connection file to connect the database.
  • Create a PHP file where you want to show data.
 


Create a Database:

CREATE DATABASE  php_pagination;


Create a Table:

CREATE TABLE `blogs` (
   `id` int(11) NOT NULL,
   `addeddate` date DEFAULT NULL,
   `title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `updatedat` date DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


Create a Connection:


<?php
$servername = "localhost";
$username = "root";
$password = "";
$db="php_pagination";
 
$conn = mysqli_connect($servername, $username, $password,$db);
?>
 


Create a index.php file or file where you want to print data:


<!DOCTYPE html>
<html lang="en">
<head>
  <title>Pagination</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
 
<div class="container">
  <h2>Pagination in PHP</h2>
  <table class="table table-striped">
    <thead>
      <tr>
        <th>Added Date</th>
        <th>Title</th>
        <th>Description</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>John</td>
        <td>Doe</td>
        <td>[email protected]</td>
      </tr>
    </tbody>
  </table>
</div>
 
</body>
</html>


Now we have completed the steps, lets combine the steps to create a complete code.


Complete Code:


<?php
$servername = "localhost";
$username = "root";
$password = "";
$db="php_pagination";
 
$conn = mysqli_connect($servername, $username, $password,$db);
 
$limit = 3; 
if (isset($_GET["page"])) {
            $page  = $_GET["page"];
            }
            else{
            $page=1;
            }; 
$start_from = ($page-1) * $limit; 
$result = mysqli_query($conn,"SELECT * FROM blogs ORDER BY addeddate DESC LIMIT $start_from, $limit");
 
 
?>
 
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Pagination</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
 
<div class="container">
  <h2>Pagination in PHP & Mysql</h2>       
  <table class="table table-striped">
    <thead>
      <tr>
        <th>Added Date</th>
        <th>Title</th>
        <th>Description</th>
      </tr>
    </thead>
    <tbody>
           
           
            <?php
$i=1;   
while ($row = mysqli_fetch_array($result)) { 
?> 
      <tr>
        <td><?php echo $row['addeddate'];?></td>
        <td><?php echo $row['title'];?></td>
        <td><?php echo $row['description'];?></td>
      </tr>
    
<?php $i++; } ?>
             
    </tbody>
  </table>
 
  <?php 
 
$result_db = mysqli_query($conn,"SELECT COUNT(id) FROM blogs");
$row_db = mysqli_fetch_row($result_db); 
$total_records = $row_db[0]; 
$total_pages = ceil($total_records / $limit);
/* echo  $total_pages; */
$pagLink = "<ul class='pagination'>"; 
for ($i=1; $i<=$total_pages; $i++) {
              $pagLink .= "<li class='page-item'><a class='page-link' href='index.php?page=".$i."'>".$i."</a></li>";  
}
echo $pagLink . "</ul>"; 
?>
 
 
</div>
 
</body>
</html>