Pagination in PHP MySQLi

📆 Last updated May 12, 2019 👷 By, Chandan Tudu

Learn how to make pagination in PHP MySQLi

Pagination in PHP MySQLi

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;


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


$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());


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

    // 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
    //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>';
                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>";  
        echo "No Data found !";
<!DOCTYPE html>
<html lang="en">
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP pagination</title>
            margin: 0;
            font-family: sans-serif;
            display: inline-block;
            color: #222;
            border: 1px solid #ddd;
            padding: 5px 10px;
            margin: 0 5px;
            text-decoration: none;
            cursor: pointer;
            color: #FFF;
            outline: none;
            border: 1px solid rgba(0,0,0,.1);
            border: 5px solid #ccc;
            padding: 10px;
            margin: 0;
            list-style: none;
            padding: 0;
        .posts li{
            padding:10px 5px;
            margin: 0;
            border-bottom: 1px solid #ddd;
            margin: 0;
            padding: 0;
            margin: 0;
            padding:10px 10px 0 10px;
            color: #444;

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

Read also

Leave a Reply

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