Simple pagination in PHP with MySQL
In this article, you will learn how to create simple pagination using PHP and MySQL. Pagination mean retrieving and displaying your information into different pages rather than a single page. Pagination is important when we have to display large data on a single page. If you will be listed all the data on the same page, that will create issues like browser hang, high page loading time, long vertical scroll. It may also create confusion for readers. So, the best approach to split the records into chunks and displaying on multiple pages.
Here is the PHP pagination example, in which we have fetched the data from the database using the latest PDO (PHP Data Object) and written PHP logic to set pagination on the fetched records. All the coding flow is mentioned step by step, that will make you easier to understand and implement.
Database Connection
STEP 1: In the first step, we have written database connection code. For this, we have created a MySQL table 'students' and inserted data in it as shown below. You can either use your existing database or copy & paste the below code in MySQL -
CREATE TABLE IF NOT EXISTS `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;INSERT INTO `students` (`id`, `first_name`, `last_name`, `email`) VALUES
(1, 'John', 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(2, 'Soyam', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(3, 'Sohn', 'Gaga', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(4, 'Rita', 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(5, 'Rohn', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(6, 'Sayam', 'Mitra', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(7, 'Shyam', 'Mishra', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(8, 'Ryan', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(9, 'Rohan', 'Soy', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(10, 'Mita', 'Dahl', This email address is being protected from spambots. You need JavaScript enabled to view it.');
Database Configuration File
Next, we have created a PHP file configuration.php, where we have written database connection code using PHP PDO. It is a lightweight interface for accessing databases and provides a data access abstraction layer for working with databases in PHP. Copy and paste this code in your configuration file. Only you will have to change the database, hostname, username and password with your database credentials and name.
configuration.php<?php
// define database related variables
$database = 'db';
$host = 'hostname';
$user = 'username';
$pass = 'password';
// try to connect to database
$db = new PDO("mysql:dbname={$database};host={$host};port={3306}", $user, $pass);
if(!$db){
echo "Error in database connection";
}
?>
data.php
STEP 2: In the second step, we have created another PHP file name 'data.php'. In this page, we have set the data records limit on each page, start counter variable, next counter variable, previous counter variable. Like- on each page, we need to set the record limit to 4, so we have stored this in a variable $per_page, on the first page the page counter is 0, which is stored in $page_counter. If the user clicks on the next page, this counter increased by one and if the user clicks on the previous page, this counter decreased by one. We have passed these variables as WHERE clause to the SELECT statement. So that, exactly those amounts of data will fetch from the database that we have to display on the pagination page.
<?php
//include configuration file
require 'configuration.php';
$start = 0; $per_page = 4;
$page_counter = 0;
$next = $page_counter + 1;
$previous = $page_counter - 1;
if(isset($_GET['start'])){
$start = $_GET['start'];
$page_counter = $_GET['start'];
$start = $start * $per_page;
$next = $page_counter + 1;
$previous = $page_counter - 1;
}
// query to get messages from messages table
$q = "SELECT * FROM students LIMIT $start, $per_page";
$query = $db->prepare($q);
$query->execute();
if($query->rowCount() > 0){
$result = $query->fetchAll(PDO::FETCH_ASSOC);
}
// count total number of rows in students table
$count_query = "SELECT * FROM students";
$query = $db->prepare($count_query);
$query->execute();
$count = $query->rowCount();
// calculate the pagination number by dividing total number of rows with per page.
$paginations = ceil($count / $per_page);
?>
index.php
STEP 3: In the third step, we have created an 'index.php' file. This is the main file that we will call on the browser. This file contains mostly HTML code to display the records and pagination links with the pagination page numbers.
<html>
<head>
<title>Pagination</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<?php include_once 'data.php'; ?>
<div class="table-responsive" style="width: 600px; border: 1px solid black; margin: 10px;">
<table class="table table-striped" class="table table-hover">
<thead class="table-info">
<th scope="col" class="bg-primary" >Id</th>
<th scope="col" class="bg-primary">First Name</th>
<th scope="col" class="bg-primary">Last Name</th>
<th scope="col" class="bg-primary">Email</th>
</thead>
<tbody>
<?php
foreach($result as $data) {
echo '<tr>';
echo '<td>'.$data['id'].'</td>';
echo '<td>'.$data['first_name'].'</td>';
echo '<td>'.$data['last_name'].'</td>';
echo '<td>'.$data['email'].'</td>';
echo '</tr>';
}
?>
</tbody>
</table>
<center>
<ul class="pagination">
<?php
if($page_counter == 0){
echo "<li><a href=?start='0' class='active'>0</a></li>";
for($j=1; $j < $paginations; $j++) {
echo "<li><a href=?start=$j>".$j."</a></li>";
}
}else{
echo "<li><a href=?start=$previous>Previous</a></li>";
for($j=0; $j < $paginations; $j++) {
if($j == $page_counter) {
echo "<li><a href=?start=$j class='active'>".$j."</a></li>";
}else{
echo "<li><a href=?start=$j>".$j."</a></li>";
}
}if($j != $page_counter+1)
echo "<li><a href=?start=$next>Next</a></li>";
}
?>
</ul>
</center>
</div>
</body>
</html>
Screenshot: Pagination in PHP
Related Articles
How to display PDF file in PHP from databaseHow to read CSV file in PHP and store in MySQL
Create And Download Word Document in PHP
PHP SplFileObject Standard Library
Simple File Upload Script in PHP
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
Php file based authentication
Preventing Cross Site Request Forgeries(CSRF) in PHP
PHP code to send email using SMTP
PHP MYSQL Advanced Search Feature
Simple PHP File Cache
PHP Connection and File Handling on FTP Server
Php file based authentication
Simple way to send SMTP mail using Node.js
