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);
}
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>…</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>
