6 Steps to Create Simple Pagination Script in PHP & MySQL

Simple Pagination Script in PHP & MySQL, this script will explain you how pagination system is created in PHP & MySQL. Pagination is a sequence of pages to break number of results into small bytes in each page. We can limit these results based on our requirement.

Here I’m not working with a huge list, I’m working with simple data from the database. I’m going to display these results in table, which is styled with bootstrap styles. And also I’m using bootstrap pagination styles to display our pagination system.

First of all, we need to create a database table. I’m using crud table from crud application & also I’m working on view.php file from crud application in PHP & MySQL. If you don’t know, you can revise it from the URL. But, still I’ll provide necessary code here.

1. Create Database table with dummy data

CREATE TABLE `crud` (
  `id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `age` varchar(255) NOT NULL,
  `email_id` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `crud` (`id`, `first_name`, `last_name`, `gender`, `age`, `email_id`) VALUES
(1, 'vivek', 'vengala', 'male', '25', 'vivek@codingcyber.com'),
(3, 'vivek 1', 'v', 'male', '20', 'vivek1@codingcyber.com'),
(4, 'vivek 2', 'v', 'male', '21', 'vivek2@codingcyber.com'),
(5, 'vivek 3', 'v', 'male', '22', 'vivek3@codingcyber.com'),
(6, 'vivek 4', 'v', 'male', '23', 'vivek@codingcyber.com'),
(7, 'vivek 5', 'v', 'male', '24', 'vivek5@codingcyber.com'),
(8, 'vivek 6', 'v', 'male', '25', 'vivek6@codingcyber.com');

ALTER TABLE `crud`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `crud`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

CREATE TABLE `crud` (

`id` int(11) NOT NULL,

`first_name` varchar(255) NOT NULL,

`last_name` varchar(255) NOT NULL,

`gender` varchar(255) NOT NULL,

`age` varchar(255) NOT NULL,

`email_id` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `crud` (`id`, `first_name`, `last_name`, `gender`, `age`, `email_id`) VALUES

(1, 'vivek', 'vengala', 'male', '25', 'vivek@codingcyber.com'),

(3, 'vivek 1', 'v', 'male', '20', 'vivek1@codingcyber.com'),

(4, 'vivek 2', 'v', 'male', '21', 'vivek2@codingcyber.com'),

(5, 'vivek 3', 'v', 'male', '22', 'vivek3@codingcyber.com'),

(6, 'vivek 4', 'v', 'male', '23', 'vivek@codingcyber.com'),

(7, 'vivek 5', 'v', 'male', '24', 'vivek5@codingcyber.com'),

(8, 'vivek 6', 'v', 'male', '25', 'vivek6@codingcyber.com');

ALTER TABLE `crud`

ADD PRIMARY KEY (`id`);

ALTER TABLE `crud`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

2. Connecting to database using PHP

If you don’t know connecting to database in PHP, you can learn from this post -> Connect to MySQL database using PHP

<?php
$connection = mysqli_connect('localhost', 'root', '');
if (!$connection){
    die("Database Connection Failed" . mysqli_error($connection));
}
$select_db = mysqli_select_db($connection, 'project');
if (!$select_db){
    die("Database Selection Failed" . mysqli_error($connection));
}

<?php

$connection = mysqli_connect('localhost', 'root', '');

if (!$connection){

die("Database Connection Failed" . mysqli_error($connection));

}

$select_db = mysqli_select_db($connection, 'project');

if (!$select_db){

die("Database Selection Failed" . mysqli_error($connection));

}

3.view.php file from our CRUD Application

Save this file as pagination.php, we are going to modify this code by adding pagination system in PHP & MySQL. Here I’m not going to explain this view.php file. This is simple HTML table fetching data from database and displaying it in HTML page.

<?php
require_once('connect.php');
$ReadSql = "SELECT * FROM `crud`";
$res = mysqli_query($connection, $ReadSql);
?>
<!DOCTYPE html>
<html>
<head>
	<title>Simple CRUD Application - READ Operation</title>
	<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
 
<!-- Optional theme -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" >
 
<link rel="stylesheet" href="styles.css" >

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>

<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>


</head>
<body>
<div class="container">
	<div class="row">
	<h2>Read Operation in CRUD applicaiton</h2>
		<table class="table "> 
		<thead> 
			<tr> 
				<th>#</th> 
				<th>Full Name</th> 
				<th>E-Mail</th> 
				<th>Age</th> 
				<th>Gender</th> 
				<th>Extras</th>
			</tr> 
		</thead> 
		<tbody> 
		<?php 
		while($r = mysqli_fetch_assoc($res)){
		?>
			<tr> 
				<th scope="row"><?php echo $r['id']; ?></th> 
				<td><?php echo $r['first_name'] . " " . $r['last_name']; ?></td> 
				<td><?php echo $r['email_id']; ?></td> 
				<td><?php echo $r['gender']; ?></td> 
				<td><?php echo $r['age']; ?></td> 
				<td>
					<a href="update.php?id=<?php echo $r['id']; ?>"><span class="glyphicon glyphicon-edit" aria-hidden="true"></span></a>
				</td>
			</tr> 
		<?php } ?>
		</tbody> 
		</table>
	</div>
</div>
</body>
</html>

<?php

require_once('connect.php');

$ReadSql = "SELECT * FROM `crud`";

$res = mysqli_query($connection, $ReadSql);

<!DOCTYPE html>

<html>

<head>

<title>Simple CRUD Application - READ Operation</title>

</head>

<body>

<h2>Read Operation in CRUD applicaiton</h2>

<thead>

<tr>

<th>Gender</th>

<th>Extras</th>

</tr>

</thead>

<tbody>

<?php

while($r = mysqli_fetch_assoc($res)){

<tr>

<td>

</td>

</tr>

<?php } ?>

</tbody>

</table>

</div>

</body>

</html>

4. Adding HTML pagination buttons in Bootstrap

Here is the HTML code to add pagination buttons in bootstrap. Add this code below the table. If you reload this page in browser, you should see pagination button after table.

  <ul class="pagination">
    <li class="page-item">
      <a class="page-link" href="#" tabindex="-1" aria-label="Previous">
        <span aria-hidden="true">&laquo;</span>
        <span class="sr-only">First</span>
      </a>
    </li>
    <li class="page-item"><a class="page-link" href="#">1</a></li>
    <li class="page-item active"><a class="page-link" href="#">2</a></li>
    <li class="page-item"><a class="page-link" href="#">3</a></li>
    <li class="page-item">
      <a class="page-link" href="#" aria-label="Next">
        <span aria-hidden="true">&raquo;</span>
        <span class="sr-only">Last</span>
      </a>
    </li>
  </ul>
</nav>

<span class="sr-only">First</span>

</a>

</li>

</a>

</li>

</ul>

</nav>

Now we have to make these pagination buttons dynamic.

5. Writing Code to make Pagination work

First of all, we have to define some variables. Create variable to display number of results.

$perpage = 2;

1	$perpage = 2;

Next, if the GET superglobal is set then assign this value to a variable. Otherwise, create a variable with default value.

if(isset($_GET['page']) & !empty($_GET['page'])){
	$curpage = $_GET['page'];
}else{
	$curpage = 1;
}

if(isset($_GET['page']) & !empty($_GET['page'])){

$curpage = $_GET['page'];

}else{

$curpage = 1;

}

Then modify ReadSql query with LIMIT clauses, here is the updated SQL

$ReadSql = "SELECT * FROM `crud` LIMIT 0, 2";

1	$ReadSql = "SELECT * FROM `crud` LIMIT 0, 2";

After using this above query, we will get 2 results. We will make these numbers dynamic using variabels. For that we have to create some more variables.

Here I’m calculating starting value that is used LIMIT clause.

$start = ($curpage * $perpage) - $perpage;

1	$start = ($curpage * $perpage) - $perpage;

To get number of results in database, we have to use this below code. This is to calculate number of pages in pagination

$PageSql = "SELECT * FROM `crud`";
$pageres = mysqli_query($connection, $PageSql);
$totalres = mysqli_num_rows($pageres);

$PageSql = "SELECT * FROM `crud`";

$pageres = mysqli_query($connection, $PageSql);

$totalres = mysqli_num_rows($pageres);

Next, we have to calculate start page, end page, next page, previous page.

$endpage = ceil($totalres/$perpage);
$startpage = 1;
$nextpage = $curpage + 1;
$previouspage = $curpage - 1;

$endpage = ceil($totalres/$perpage);

$startpage = 1;

$nextpage = $curpage + 1;

$previouspage = $curpage - 1;

Modify ReadSql query with variables to pass data.

$ReadSql = "SELECT * FROM `crud` LIMIT $start, $perpage";

1	$ReadSql = "SELECT * FROM `crud` LIMIT $start, $perpage";

6. Making pagination links dynamic

Here, in pagination buttons, I’ve five buttons.
First page button, Last page button. Previous page button, Current page button, Next page button.

First page pagination button.
If the current page is not equal to first page then only we are going to display this first page button.

  <?php if($curpage != $startpage){ ?>
    <li class="page-item">
      <a class="page-link" href="?page=<?php echo $startpage ?>" tabindex="-1" aria-label="Previous">
        <span aria-hidden="true">&laquo;</span>
        <span class="sr-only">First</span>
      </a>
    </li>
    <?php } ?>

<?php if($curpage != $startpage){ ?>

<a class="page-link" href="?page=<?php echo $startpage ?>" tabindex="-1" aria-label="Previous">

<span class="sr-only">First</span>

</a>

</li>

<?php } ?>

Last page pagination button.
If the last page is not equal to current page then only we are going to dispaly this last page button.

    <?php if($curpage != $endpage){ ?>
    <li class="page-item">
      <a class="page-link" href="?page=<?php echo $endpage ?>" aria-label="Next">
        <span aria-hidden="true">&raquo;</span>
        <span class="sr-only">Last</span>
      </a>
    </li>
    <?php } ?>

<?php if($curpage != $endpage){ ?>

<a class="page-link" href="?page=<?php echo $endpage ?>" aria-label="Next">

</a>

</li>

<?php } ?>

Previous page pagination button
If the current page is greater than or equal to 2, then only we will display this previous page pagination button.

    <?php if($curpage >= 2){ ?>
    <li class="page-item"><a class="page-link" href="?page=<?php echo $previouspage ?>"><?php echo $previouspage ?></a></li>
    <?php } ?>

<?php if($curpage >= 2){ ?>

<?php } ?>

Current page pagination button
I’m not going to add any conditions here. Because, we will always display this current page link.

<li class="page-item active"><a class="page-link" href="?page=<?php echo $curpage ?>"><?php echo $curpage ?></a></li>

1	<li class="page-item active"><a class="page-link" href="?page=<?php echo $curpage ?>"><?php echo $curpage ?></a></li>

Next page pagination button

<?php if($curpage != $endpage){ ?>
    <li class="page-item"><a class="page-link" href="?page=<?php echo $nextpage ?>"><?php echo $nextpage ?></a></li>
<?php } ?>

<?php if($curpage != $endpage){ ?>

<?php } ?>

We can combine this code to both next page pagination button and last page pagination button. You can customize number of results on per page.

I hope now you understand the Pagination system in PHP & MySQL. If you have any problem in implementing the let me know through the comment form below.

Complete Code

If you have any problem arranging above pieces of code, you can use this complete code below.

Database SQL code with dummy records

You can get this code from above step1.

Connecting to Database

You can create a file connect.php from above code step2.

pagination.php file code

save this code as pagination.php

<?php
require_once('connect.php');
$perpage = 2;
if(isset($_GET['page']) & !empty($_GET['page'])){
	$curpage = $_GET['page'];
}else{
	$curpage = 1;
}
$start = ($curpage * $perpage) - $perpage;
$PageSql = "SELECT * FROM `crud`";
$pageres = mysqli_query($connection, $PageSql);
$totalres = mysqli_num_rows($pageres);

$endpage = ceil($totalres/$perpage);
$startpage = 1;
$nextpage = $curpage + 1;
$previouspage = $curpage - 1;

$ReadSql = "SELECT * FROM `crud` LIMIT $start, $perpage";
$res = mysqli_query($connection, $ReadSql);
?>
<!DOCTYPE html>
<html>
<head>
	<title>Simple CRUD Application - READ Operation</title>
	<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
 
<!-- Optional theme -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" >
 
<link rel="stylesheet" href="styles.css" >

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>

<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>


</head>
<body>
<div class="container">
	<div class="row">
	<h2>Read Operation in CRUD applicaiton</h2>
		<table class="table "> 
		<thead> 
			<tr> 
				<th>#</th> 
				<th>Full Name</th> 
				<th>E-Mail</th> 
				<th>Age</th> 
				<th>Gender</th> 
				<th>Extras</th>
			</tr> 
		</thead> 
		<tbody> 
		<?php 
		while($r = mysqli_fetch_assoc($res)){
		?>
			<tr> 
				<th scope="row"><?php echo $r['id']; ?></th> 
				<td><?php echo $r['first_name'] . " " . $r['last_name']; ?></td> 
				<td><?php echo $r['email_id']; ?></td> 
				<td><?php echo $r['gender']; ?></td> 
				<td><?php echo $r['age']; ?></td> 
				<td>
					<a href="update.php?id=<?php echo $r['id']; ?>"><span class="glyphicon glyphicon-edit" aria-hidden="true"></span></a>
				</td>
			</tr> 
		<?php } ?>
		</tbody> 
		</table>
	</div>

	<nav aria-label="Page navigation">
  <ul class="pagination">
  <?php if($curpage != $startpage){ ?>
    <li class="page-item">
      <a class="page-link" href="?page=<?php echo $startpage ?>" tabindex="-1" aria-label="Previous">
        <span aria-hidden="true">&laquo;</span>
        <span class="sr-only">First</span>
      </a>
    </li>
    <?php } ?>
    <?php if($curpage >= 2){ ?>
    <li class="page-item"><a class="page-link" href="?page=<?php echo $previouspage ?>"><?php echo $previouspage ?></a></li>
    <?php } ?>
    <li class="page-item active"><a class="page-link" href="?page=<?php echo $curpage ?>"><?php echo $curpage ?></a></li>
    <?php if($curpage != $endpage){ ?>
    <li class="page-item"><a class="page-link" href="?page=<?php echo $nextpage ?>"><?php echo $nextpage ?></a></li>
    <li class="page-item">
      <a class="page-link" href="?page=<?php echo $endpage ?>" aria-label="Next">
        <span aria-hidden="true">&raquo;</span>
        <span class="sr-only">Last</span>
      </a>
    </li>
    <?php } ?>
  </ul>
</nav>
</div>

</body>
</html>

100

101

102

103

<?php

require_once('connect.php');

$perpage = 2;

if(isset($_GET['page']) & !empty($_GET['page'])){

$curpage = $_GET['page'];

}else{

$curpage = 1;

}

$start = ($curpage * $perpage) - $perpage;

$PageSql = "SELECT * FROM `crud`";

$pageres = mysqli_query($connection, $PageSql);

$totalres = mysqli_num_rows($pageres);

$endpage = ceil($totalres/$perpage);

$startpage = 1;

$nextpage = $curpage + 1;

$previouspage = $curpage - 1;

$ReadSql = "SELECT * FROM `crud` LIMIT $start, $perpage";

$res = mysqli_query($connection, $ReadSql);

<!DOCTYPE html>

<html>

<head>

<title>Simple CRUD Application - READ Operation</title>

</head>

<body>

<h2>Read Operation in CRUD applicaiton</h2>

<thead>

<tr>

<th>Gender</th>

<th>Extras</th>

</tr>

</thead>

<tbody>

<?php

while($r = mysqli_fetch_assoc($res)){

<tr>

<td>

</td>

</tr>

<?php } ?>

</tbody>

</table>

</div>

<?php if($curpage != $startpage){ ?>

<a class="page-link" href="?page=<?php echo $startpage ?>" tabindex="-1" aria-label="Previous">

<span class="sr-only">First</span>

</a>

</li>

<?php } ?>

<?php if($curpage >= 2){ ?>

<?php } ?>

<?php if($curpage != $endpage){ ?>

<a class="page-link" href="?page=<?php echo $endpage ?>" aria-label="Next">

</a>

</li>

<?php } ?>

</ul>

</nav>

</div>

</body>

</html>

If you have any problem in above code, let me know through comment form below.

6 Steps to Create Simple Pagination Script in PHP & MySQL

1. Create Database table with dummy data

2. Connecting to database using PHP

3.view.php file from our CRUD Application

4. Adding HTML pagination buttons in Bootstrap

5. Writing Code to make Pagination work

6. Making pagination links dynamic

Complete Code

Database SQL code with dummy records

Connecting to Database

pagination.php file code

Related Posts

Vivek Vengala

Leave a Reply:

6 Steps to Create Simple Pagination Script in PHP & MySQL

1. Create Database table with dummy data

2. Connecting to database using PHP

3.view.php file from our CRUD Application

4. Adding HTML pagination buttons in Bootstrap

5. Writing Code to make Pagination work

6. Making pagination links dynamic

Complete Code

Database SQL code with dummy records

Connecting to Database

pagination.php file code

Share this:

Related Posts

Vivek Vengala

Leave a Reply: