Learn how to make pagination in PHP MySQLi

Pagination in PHP MySQLi
Screenshot

In this tutorial, we are going to create dynamic pagination in PHP MySQLi for a web page.

Pagination is the process of dividing or separating a document or digital content into discrete pages.


Related tutorial of Pagination


To create this Pagination follow the below steps

Step – 1

First, you go to your phpMyAdmin and create a new Database and name it pagination.

After that select the pagination database and go to the SQL section.

Then copy the below SQL code and paste into the SQL text-area and then click on Go button.

This SQL code creates a table called `posts` into the pagination Database and it also creates `posts` table structure.

CREATE TABLE `posts` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `body` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `posts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

After that please insert some records into the `posts` table.


Step – 2

After that go to your www directory or Xampp htdocs folder and here you create a new folder called whatever you want, Here I named this folder pagination.

After that go inside the newly created folder and here you create two files index.php and db_connection.php.


Creating files

db_connection.php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db_name = "pagination";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $db_name);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

index.php

<?php
// including database connection
require 'db_connection.php';

if(isset($_GET['page'])){
    // if get page number through url and check it is a valid number
    $page_num = filter_var($_GET['page'], FILTER_VALIDATE_INT,[
        'options' => [
            'default' => 1,
            'min_range' => 1
        ]
    ]); 
    
}else{
    //default page number
    $page_num = 1;
}
// set how much show posts in a single page
$page_limit = 2;
// Set Offset
$page_offset = $page_limit * ($page_num - 1);

function showPosts($conn, $current_page_num, $page_limit, $page_offset){
    
    // query of fetching posts
    $query = mysqli_query($conn,"SELECT * FROM `posts` ORDER BY id LIMIT $page_limit OFFSET $page_offset");
    
    // check database is not empty
    if(mysqli_num_rows($query) > 0){
        
        // fetching data
        while($row = mysqli_fetch_array($query)){ 
            echo '<li><h2>'.$row['title'].'</h2><p>'.$row['body'].'</p></li>';
        }
        
        // total number of posts
        $total_posts = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM `posts`"));
        
        // total number of pages
        $total_page = ceil($total_posts / $page_limit);
        // set next page number
        $next_page = $current_page_num+1; 
        // set prev page number
        $prev_page = $current_page_num-1; 
        
       echo "<li>";
        //showing prev button and check current page number is greater than 1
        if($current_page_num > 1){
           echo '<a href="?page='.$prev_page.'" class="page_link">Prev</a>';
        }
        // show all number of pages
        for($i = 1; $i <= $total_page; $i++){
            //highlight the current page number
            if($i == $current_page_num){
                echo '<a href="?page='.$i.'" class="page_link active_page">'.$i.'</a>';
            }else{
                echo '<a href="?page='.$i.'" class="page_link">'.$i.'</a>';
            }
            
        }
        // showing next button and check this is last page
        if($total_page+1 != $next_page){
           echo '<a href="?page='.$next_page.'" class="page_link">Next</a>';
        }
        
        echo "</li>";  
        
    }else{
        echo "No Data found !";
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP pagination</title>
    <style>
        body{
            padding:0;
            margin: 0;
            font-family: sans-serif;
        }
        .page_link{
            display: inline-block;
            color: #222;
            border: 1px solid #ddd;
            padding: 5px 10px;
            margin: 0 5px;
            text-decoration: none;
            cursor: pointer;
        }
        .active_page{
            background-color:dodgerblue;
            color: #FFF;
            outline: none;
            border: 1px solid rgba(0,0,0,.1);
        }
        .container{
            border: 5px solid #ccc;
            padding: 10px;
        }
        .posts{
            margin: 0;
            list-style: none;
            padding: 0;
        }
        .posts li{
            padding:10px 5px;
            margin: 0;
            border-bottom: 1px solid #ddd;
            
        }
        h2{
            margin: 0;
            padding: 0;
        }
        p{
            margin: 0;
            padding:10px 10px 0 10px;
            color: #444;
        }
        
    </style>
</head>

<body>
   <h1 style="text-align:center;">PHP Pagination</h1>
    <div class="container">
        <ul class="posts">
<?php 
// call showPosts function 
showPosts($conn, $page_num, $page_limit, $page_offset);
?>   
        </ul>
    </div> 
</body>
</html>

Read also

Leave a Reply

Your email address will not be published. Required fields are marked *