Pagination in PHP MySQLi

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

Posts Navigation


Leave a Reply

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