In this article, we going to learn how to implement pagination with PHP and MySQL. This guide covers basics of pagination and includes step-by-step instructions, code examples, and screenshots. It's perfect for web developers of beginners levels who want to learn how to implement pagination in their PHP and MySQL projects. I have already shard the front-end of pagination. So with that design we going to make the backend.
Pagination is use to show the limited number of data per page to user and if need more then user can simply go to next page by navigating pagination to get further data. Pagination can make page load faster. As it shows the limited amount of data per page.
Video Tutorial of Pagination Using PHP & MySQL:
Pagination Using php and MySQL.
In this article we going to make pagination which show's 5 records or data or rows per page. Total number of rows in database is 50. It has next and previous button to go next and previous pages. Read further for more details. So, let’s start making it.
First let’s make the frontend of pagination. I have shared the front end of pagination using HTML and CSS. We just make some changes in it.
Below are the steps which I have created for you to make this pagination using PHP.
Step 1: create files and basic structure of html.
First create two files with .html and .css extension (I have created files with name index.html and index.css). Link css file with html file. With link tag (Means make the basic setup of files.).
HTML
<!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>Document</title>
<link rel="stylesheet" href="index.css">
</head>
<body>
</body>
</html>
code
HTML
*{
padding: 0px;
margin: 0px;
}
body{
width: 100%;
height: 100vh;
background-color: #1a004b;
display: flex;
align-items: center;
justify-content: center;
}
Step 2: Create frontend of pagination.
I have shared frontend of pagination. We just make some changes and add the record or rows box above it. In which rows will be display. Below is the full frontend of pagination.
HTML
<!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>Document</title>
<link rel="stylesheet" href="index.css">
</head>
<body>
<div class="container">
<div class="record_box">
<p>Record</p>
<p>Record</p>
<p>Record</p>
<p>Record</p>
<p>Record</p>
</div>
<div class="box">
<button type="button"><a href="#">Prev</a></button>
<ul>
<li><a href="#">1</a></li>
<li><a href="#">2</a></li>
<li class="active_li_tag"><a href="#" class="active_a_tag">3</a></li>
<li><a href="#">4</a></li>
<li><a href="#">5</a></li>
</ul>
<button type="button"><a href="#">Next</a></button>
</div>
</div>
</body>
</html>
CSS
*{
padding: 0px;
margin: 0px;
}
body{
width: 100%;
height: 100vh;
background-color: #1a004b;
display: flex;
align-items: center;
justify-content: center;
}
.container{
text-align: center;
}
.record_box{
width: 500px;
background-color: #fff;
border-radius: 20px;
height: auto;
overflow: hidden;
overflow-y: auto;
max-height: 350px;
margin: 20px 0px;
}
.record_box p{
margin: 10px 5px;
padding: 10px;
font-size: 25px;
cursor: pointer;
border-radius: 10px;
transition: 0.2s;
}
.record_box p:hover{
background-color: #f2f2f2;
}
.box{
background-color: #fff;
padding: 10px;
border-radius: 200px;
box-shadow: 0px 10px 30px -15px rgb(0 0 0);
display: inline-flex;
align-items: center;
}
.box ul{
display: flex;
margin: 0px 10px;
}
.box ul li{
list-style: none;
margin: 0px 5px;
width: 40px;
height: 40px;
line-height: 40px;
border-radius: 50%;
text-align: center;
transition: 0.2s;
}
.box ul .active_li_tag{
background-color: #000;
}
.box ul li:hover,
.box button:hover{
background-color: #000;
}
.box ul li a{
font-size: 25px;
text-decoration: none;
color: #000;
display: block;
transition: 0.2s;
}
.box ul li .active_a_tag{
color: #fff;
}
.box ul li a:hover,
.box button a:hover{
color: #fff;
}
.box button{
font-size: 20px;
font-weight: bold;
background-color: #f1f1f1;
border: none;
cursor: pointer;
border-radius: 200px;
transition: 0.2s;
}
.box button a{
text-decoration: none;
padding: 10px 20px;
display: block;
color: #000;
transition: 0.2s;
}
Output:
Step 3: Create database.
I hope you know how to run php on computer. And how to open and run php files and create database.
Let’s first create database and add the records or rows. I have added 50 rows. Given the database name pagination and table name pages. Added two columns (id, records).
Step 4: Connect database.
After creating database let's connect it. So, let's create another file with name config.php and write code for establishing connection with database.
config.php
<?php
$server = 'localhost';
$username = 'root';
$password = '';
$database = 'pagination';
$con = mysqli_connect($server , $username , $password , $database);
if(!$con){
echo 'Connection Error';
}
?>
Step 5: Add backend for pagination.
Now let’s change the .html file extension to .php (index.html to index.php) so that we can write php in it.
First, we'll define the variables.
$record_error is to show the error when record not found from database.
$current_page is for current page. On which page user is currently on. (Value is in integer).
$result_per_page is for how many rows want to show per page. (Here we’ll show 5 rows or record per page).
$total_record is to store total number of rows from database.
$page is to store, page number from pagination user have clicked. We’ll get that page number from $_GET and make $current_page value equal to this $page value.
PHP
<?php
include 'config.php';
$record_error = '';
$current_page = 1;
$result_per_page = 2;
$total_records = '';
$page = 0;
?>
Getting total number of rows from database.
Total number of rows helps to calculate how many page number to form in pagination (througn forloop), by dividing it through total number of rows to number of rows to show per page (here we’ll show 5 rows).
We going to store total number of rows in $total_record variable which we created above.
PHP
$get_total_records = "SELECT records FROM pages";
$query = mysqli_prepare($con, $get_total_records);
if (mysqli_stmt_execute($query)) {
mysqli_stmt_store_result($query);
if (mysqli_stmt_num_rows($query) > 0) {
$total_records = mysqli_stmt_num_rows($query);
}
}
Calculate the number of pages to form in pagination.
After getting total rows or total records. Now let’s Calculate number of pages to form in pagination through forloop. It’s simple to calculate just divide total number of rows to how many rows want to show per page.
We have created 50 rows in database and want to show 5 rows per page. so, it looks like this ceil (50 / 5).
The answer comes is 10. So, we need to create 10 pages. I have stored this answer in $total_pages variable.
See the below code.
PHP
$total_pages = ceil($total_records / $result_per_page);
Ceil () function is used for round up the number. From division if answer come in decimal than this function round the number up.
After calculating the pages lets create pagination.
Creating page numbers.
After getting page numbers that we have to create. Now let's start creating it. So right now page number looks like this.
HTML
<div class="box">
<button type="button"><a href="#">Prev</a></button>
<ul>
<li><a href="#">1</a></li>
<li><a href="#">2</a></li>
<li class="active_li_tag"><a href="#" class="active_a_tag">3</a></li>
<li><a href="#">4</a></li>
<li><a href="#">5</a></li>
</ul>
<button type="button"><a href="#">Next</a></button>
</div>
But we have to create pagination according to the total pages ($total_pages = Ceil ($total_records / $result_per_page)) So let’s create it with for loop in php.
So we create the pages, like this 1 2 3 4 5 6.... . We make the pages through for loop. First, we pick the current page number ($current_page) let's take 3. So through for loop we show the two pages before and two pages after the current page. As current page number is 3. So, two pages before is 1,2 and after is 4,5.
So in for loop we make i variable which is equal to two pages before current page (that is 1) and check, is it less than or equal to two pages after current page (that’s is 5) or not. So its not, so for loop make 1 to 5 pages with li tag and a tag.
After the page is created. we'll show the current page number.
Now to show that on which page currently the user on. We make it by checking the current page and variable i (of for loop) if current page is equal to i variable, then We add a class to li tag and a tag (you can say that class an active class) that add the background colour to that page number to show as an active page.
Now from where we get the current page $current_page)? Above we have manually defined the value of current page (that is 3) But it should be according to page number selected by user. So, when user click on pages that is created from for loop. We send the page value or page number to URL and through $_GET variable we get that page value or page number. And we make that page value equal to that current page number ($current_page). That’s how we get the current page.
Below image is of url after clicked on 3rd page of pagination. This page=3 value we take by $_GET .
PHP
<?php
if (isset($_GET['page'])) {
$page = $_GET['page'];
$current_page = $page;
}
?>
<!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>Document</title>
<link rel="stylesheet" href="index.css">
</head>
<body>
<?php
<div class="box">
<button type="button"><a href="#">Prev</a></button>
?>
<ul>
<?php
$page_before = $current_page - 2;
$page_after = $current_page + 2;
for ($i = $page_before; $i <= $page_after; $i++) {
$active_li_tag = '';
$active_a_tag = '';
if ($current_page == $i) {
$active_li_tag = 'active_li_tag';
$active_a_tag = 'active_a_tag';
}
?>
<li class="<?php echo $active_li_tag ?>"><a href="?page=<?php echo $i ?>" class="<?php echo $active_a_tag ?>"><?php echo $i ?></a></li>
<?php
}
?>
<!-- <li><a href="#">1</a></li>
<li><a href="#">2</a></li>
<li class="active_li_tag"><a href="#" class="active_a_tag">3</a></li>
<li><a href="#">4</a></li>
<li><a href="#">5</a></li> -->
</ul>
<button type="button"><a href="#">Next</a></button>
</div>
</body>
</html>
So, for loop is created for page number formation, but till where we show the pages? Because we don’t want to show extra pages. Above after calculation (ceil (50 / 5)) we get 10 pages to show, if we show 5 record per page and the total rows in database is 50. So, we don’t want to show extra pages after 10 and before 1.
As here for loop create two pages before and two pages after the current page. So, there is a problem when user is on one of these pages (1st, 2nd, last second and last page).
When user is on 1st page for loop create two pages before too (its 0 and -1) So we don’t want these 0 and -1 pages and if user is on 2nd page for loop create two pages before (that is 0 and 1) but here we only want 1st page.
And when user is on last second page (here that is 9 as the total pages is 10) and for loop create two pages after (which is 10 and 11) so here we only have to form one page after last second page that is last page and when user is on last page (which is 10) and for loop create two pages after it (which is 11 and 12). So, from here (from 10th) we don’t want further pages.
So below I have added if conditions just above the for loop to fix this. See below code.
PHP
if ($current_page == 1) {
$page_before = $current_page;
}
if ($current_page == 2) {
$page_before = $current_page - 1;
}
if ($current_page == $total_pages) {
$page_after = $current_page;
}
if ($current_page == $total_pages - 1) {
$page_after = $current_page + 1;
}
Pagination is created. After creating pagination let's display rows or records from database according to page number.
Displaying rows or records from database.
So, our record box looks like this right now.
HTML
<div class="record_box">
<p>Record</p>
<p>Record</p>
<p>Record</p>
<p>Record</p>
<p>Record</p>
</div>
We have to show records according to page number selected by user.
Now let’s show the records or rows according to page number selected by user. Number of rows we going to show per page is 5.
To show the rows or records, multiply the page number (that is given by user that we get from $_GET) with 5. For example, if page number is 1st then multiply it with 5 (5 because no. of rows we have to show per page is 5) (1 * 5 = 5). The answer is 5. This the answer of multiplication.
Then we run the SQL query that show the rows from table with limit of answer of multiplication, rows per page (means $result_per_page variable value, which we added 5). Here query will give 5 rows from 6th row (means it give rows from 6 to 10).
For example, if user click on 2nd page number, we take that page number 2 from $_GET, that we multiply with 5, so answer comes 10 (answer of multiplication) and when SQL query runs It shows 5 rows from 11th row (means it show rows from 11 to 15). But it should gives rows from 6 to 10, on 2nd page as we show 5 record per page.
In above case also when user clicked on 1st page query gives 5 rows from 6th row from database (means 6 to 11) as we have to show 1 to 5 at 1st page.
So, to fix it we decrement the page number by 1. And then apply for multiplication. Means if user click on 2nd page, then we get the page number 2 ok. That we decrement it by one so it will become 1 (2 - 1 = 1). Then we multiply it by 5, we get 1 * 5 = 5. So, it will give 5 rows from 6th rows from database (means it show rows from 6 to 10) on 2nd page number.
Like wise when user click on 3rd page then we get the page number 3, decrement it by one then we get answer 2. Multiply it with 5 we get 10 and when SQL query run, we get 5 rows from 11 (means it show rows from 11 to 15) on 3rd page.
PHP
<?php
if (isset($_GET['page'])) {
$page = $_GET['page'];
$current_page = $page;
$page--;
$page = $page * $result_per_page;
}
?>
<!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>Document</title>
<link rel="stylesheet" href="index.css">
</head>
<body>
<div class="container">
<div class="record_box">
<?php
$sql = "SELECT records FROM pages limit ?,?";
$stmt = mysqli_prepare($con, $sql);
if ($stmt) {
mysqli_stmt_bind_param($stmt, 'ii', $param_page, $param_result_per_page);
$param_page = trim($page);
$param_result_per_page = trim($result_per_page);
if (mysqli_stmt_execute($stmt)) {
mysqli_stmt_store_result($stmt);
if (mysqli_stmt_num_rows($stmt) > 0) {
mysqli_stmt_bind_result($stmt, $result_record);
while (mysqli_stmt_fetch($stmt)) {
?>
<p><?php echo $result_record ?></p>
<?php
}
} else {
$record_error = 'No record found';
if (!empty($record_error)) {
?>
<p><?php echo $record_error ?></p>
<?php
}
}
}
}
?>
<!-- <p>Record</p>
<p>Record</p>
<p>Record</p>
<p>Record</p>
<p>Record</p> -->
</div>
</div>
</body>
</html>
Now let’s add functionality to next and previous button
PHP
<?php
if (isset($_GET['page'])) {
$page = $_GET['page'];
$current_page = $page;
$page--;
$page = $page * $result_per_page;
}
$next_page = $current_page + 1;
$previous_page = $current_page - 1;
?>
<!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>Document</title>
<link rel="stylesheet" href="index.css">
</head>
<body>
<div class="container">
<div class="box">
<?php
if($current_page > 1){
?>
<button type="button"><a href="?page=<?php echo $previous_page ?>">Prev</a></button>
<?php
}
?>
<ul>
<?php
$page_before = $current_page - 2;
$page_after = $current_page + 2;
if($current_page == 1){
$page_before = $current_page;
}
if($current_page == 2){
$page_before = $current_page - 1;
}
if($current_page == $total_pages){
$page_after = $current_page;
}
if($current_page == $total_pages - 1){
$page_after = $current_page + 1;
}
for ($i = $page_before; $i <= $page_after; $i++) {
$active_li_tag = '';
$active_a_tag = '';
if ($current_page == $i) {
$active_li_tag = 'active_li_tag';
$active_a_tag = 'active_a_tag';
}
?>
<li class="<?php echo $active_li_tag ?>"><a href="?page=<?php echo $i ?>" class="<?php echo $active_a_tag ?>"><?php echo $i ?></a></li>
<?php
}
?>
</ul>
<?php
if($current_page < $total_pages){
?>
<button type="button"><a href="?page=<?php echo $next_page ?>">Next</a></button>
<?php
}
?>
</div>
</div>
</body>
</html>
So that's it simple pagination using PHP and MySQL is ready.
You may like:
- Login & Registration Form Using PHP & MySQL.
- Registration form With Prepared Statement Using PHP & MySQL.
- Login form with PHP & MySQL with prepared statement.
- How to create dynamic pagination using JavaScript.
- How to create simple pagination using HTML and CSS.
Final code:
So that’s how you can create pagination using PHP & MySQL. If you have any suggestions or questions you can write in the comment section.
Share your thoughts.