Simple pagination in PHP with MySQL
In this article, you will learn how to create simple pagination using PHP and MySQL. Pagination means retrieving and displaying your information on different pages rather than a single page. Pagination is important when we have to display large amounts of data on a single page. If you list all the data on the same page, that will create issues such as browser hang, high page loading time, and long vertical scroll. It may also create confusion for readers. So, the best approach is to split the records into chunks and display them 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 flows are mentioned step by step, which will make it 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 given code into your MySQL database-
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. 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 created another PHP file named '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 increases by one, and if the user clicks on the previous page, this counter decreases by one. We have passed these variables as WHERE clauses to the SELECT statement. So exactly those amounts of data will be fetched 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 created an 'index.php' file. This is the main file that we will call in 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
PHP7.3 New Features, Functions and Deprecated FunctionsPHP CURL Cookie Jar
How to lock a file using PHP
PHP remove last character from string
How to create search filter in PHP
How to display PDF file in PHP from database
How 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
