Simple way to create pagination in PHP with MySQL DB

In this tutorial, you will learn how can create a simple pagination component for a site using PHP and a MySQL database.

Follow the below steps to create a simple pagination in PHP

1. Database Set-up

First, create a new database into your MySQL DB called whatever you wish, but I named my database is pagination.

Now, use the following SQL code to create the posts table and the structure of the table into the pagination DB.

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

2. Creation of folders and files for the PHP pagination app

After successfully configuring the database setup, go to your server public directory (www, htdocs) and create a new folder called php-pagination, this is the application root folder.

2.1. dbConnection.php

Now inside the php-pagination folder, create the dbConnection.php file for making the database connection.

Please modify the following DB information according to your DB info.

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

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

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

2.2. index.php

<?php
require 'dbConnection.php';

$page_num = 1;
$page_limit = 2;

if(isset($_GET['page'])){
    // Checking that it is a valid number
    $page_num = filter_var($_GET['page'], FILTER_VALIDATE_INT,[
        'options' => [
            'default' => 1,
            'min_range' => 1
        ]
    ]);
}

$page_offset = $page_limit * ($page_num - 1);

$query = mysqli_query($conn,"SELECT * FROM `posts` ORDER BY id LIMIT $page_limit OFFSET $page_offset");
$total_posts = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM `posts`"));
$total_page = ceil($total_posts / $page_limit);
$next_page = $page_num+1; 
$prev_page = $page_num-1; 

if($total_page < $page_num){
    header('Location: '.$_SERVER['PHP_SELF']);
    exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Pagination in PHP with MySQLi</title>
    <link rel="stylesheet" href="./style.css">
</head>
<body>

<div class="container">
    <h1>Posts</h1>
    <div class="list">
    <?php
    if(mysqli_num_rows($query) > 0):
        while($row = mysqli_fetch_array($query)):
            echo '<div class="item"><h3>'.$row['title'].'</h3><p>'.$row['body'].'</p></div>';
        endwhile;
    ?>
    <ul class="pagination">
        <?php
            if($page_num > 1):
                echo '<li><a href="?page='.$prev_page.'" class="page_link">Prev</a></li>';
            endif;

            for($i = 1; $i <= $total_page; $i++):
                if($i == $page_num){
                    echo '<li><span>'.$i.'</span></li>';
                }else{
                    echo '<li><a href="?page='.$i.'">'.$i.'</a></li>';
                }
            endfor;

            if($total_page+1 != $next_page):
                echo '<li><a href="?page='.$next_page.'" class="page_link">Next</a></li>';
            endif;
        ?>
    </ul>
    <?php endif; ?>
    </div>
</div>
    
</body>
</html>

2.3. style.css

@import url('https://fonts.googleapis.com/css2?family=Roboto:[email protected];700&display=swap');

*,
*::before,
*::after {
    box-sizing: border-box;
}

body {
    font-family: 'Roboto', sans-serif;
    padding: 20px;
    margin: 0;
    background-color: #f7f7f7;
    color: #222222;
}

.container {
    max-width: 700px;
    margin: 0 auto;
    background-color: #ffffff;
    padding: 20px;
    border-radius: 3px;
    box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1), 0 1px 2px 0 rgba(0, 0, 0, 0.06);
}

.container h1 {
    text-align: center;
}

.list {
    padding: 0 20px;
}

.list .item {
    border-bottom: 1px solid #cccccc;
}

.pagination {
    display: flex;
    flex-wrap: wrap;
    list-style: none;
    justify-content: center;
}

.pagination a,
.pagination span {
    display: block;
    padding: 5px;
    margin: 3px;
    text-decoration: none;
    outline: none;
    color: inherit;
    border: 1px solid #cccccc;
    background-color: #f7f7f7;
    border-radius: 2px;
}

.pagination a:hover,
.pagination span {
    background-color: #222831;
    color: #ffffff;
    border-color: rgba(0, 0, 0, 0.1);
}
Simple pagination using PHP and MySQL
Simple PHP Pagination

PHP Pagination with dots

If you want to add dots between the page numbers to reduce the pagination size, you can use the following code.

<?php
require 'dbConnection.php';

$page_num = 1;
$page_limit = 2;

if (isset($_GET['page'])) {
    $page_num = filter_var($_GET['page'], FILTER_VALIDATE_INT, [
        'options' => [
            'default' => 1,
            'min_range' => 1
        ]
    ]);
}

$page_offset = $page_limit * ($page_num - 1);

$query = mysqli_query($conn, "SELECT * FROM `posts` ORDER BY id LIMIT $page_limit OFFSET $page_offset");
$total_posts = mysqli_num_rows(mysqli_query($conn, "SELECT * FROM `posts`"));

$total_page = ceil($total_posts / $page_limit);
$show_dots = false;
$next_page = $page_num + 1;
$prev_page = $page_num - 1;

if($total_page < $page_num){
    header('Location: '.$_SERVER['PHP_SELF']);
    exit;
}
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Pagination in PHP with MySQLi</title>
    <link rel="stylesheet" href="./style.css">
</head>

<body>

    <div class="container">
        <h1>Posts</h1>
        <div class="list">
            <?php
            if (mysqli_num_rows($query) > 0) :
                while ($row = mysqli_fetch_array($query)) :
                    echo '<div class="item"><h3>' . $row['title'] . '</h3><p>' . $row['body'] . '</p></div>';
                endwhile;
            ?>
            <ul class="pagination">
                <?php
                if ($page_num > 1) :
                    echo '<li><a href="?page=' . $prev_page . '" class="page_link">Prev</a></li>';
                endif;

                for ($i = 1; $i <= $total_page; $i++) {

                    if ($i == $page_num) {

                        echo '<li><span>' . $i . '</span></li>';
                        $show_dots = true;

                    } else {

                        if ($i <= 1 || ($page_num && $i >= $page_num - 1 && $i <= $page_num + 1) || $i > $total_page - 1) {

                            echo '<li><a href="?page=' . $i . '">' . $i . '</a></li>';
                            $show_dots = true;

                        } elseif ($show_dots) {

                            echo '<li><span>&hellip;</span></li>';
                            $show_dots = false;

                        }
                    }
                    
                }

                if ($total_page + 1 != $next_page) :
                    echo '<li><a href="?page=' . $next_page . '" class="page_link">Next</a></li>';
                endif;
                ?>
            </ul>
            <?php endif; ?>
        </div>
    </div>

</body>

</html>
PHP Pagination with dots

Leave a Reply