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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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
1 2 3 4 5 6 7 8 9 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | <?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> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <ul class="pagination"> <li class="page-item"> <a class="page-link" href="#" tabindex="-1" aria-label="Previous"> <span aria-hidden="true">«</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">»</span> <span class="sr-only">Last</span> </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.
1 | $perpage = 2; |
Next, if the GET superglobal is set then assign this value to a variable. Otherwise, create a variable with default value.
1 2 3 4 5 | 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
1 | $ReadSql = "SELECT * FROM `crud` LIMIT 0, 2"; |
Here I’m calculating starting value that is used LIMIT clause.
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
1 2 3 | $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.
1 2 3 4 | $endpage = ceil($totalres/$perpage); $startpage = 1; $nextpage = $curpage + 1; $previouspage = $curpage - 1; |
Modify ReadSql query with variables to pass data.
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.
1 2 3 4 5 6 7 8 | <?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">«</span> <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.
1 2 3 4 5 6 7 8 | <?php if($curpage != $endpage){ ?> <li class="page-item"> <a class="page-link" href="?page=<?php echo $endpage ?>" aria-label="Next"> <span aria-hidden="true">»</span> <span class="sr-only">Last</span> </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.
1 2 3 | <?php if($curpage >= 2){ ?> <li class="page-item"><a class="page-link" href="?page=<?php echo $previouspage ?>"><?php echo $previouspage ?></a></li> <?php } ?> |
Current page pagination button
I’m not going to add any conditions here. Because, we will always display this current page link.
1 | <li class="page-item active"><a class="page-link" href="?page=<?php echo $curpage ?>"><?php echo $curpage ?></a></li> |
Next page pagination button
1 2 3 | <?php if($curpage != $endpage){ ?> <li class="page-item"><a class="page-link" href="?page=<?php echo $nextpage ?>"><?php echo $nextpage ?></a></li> <?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.