PHP pagination with sortable table on header click
In this article, you will learn how to create PHP pagination with sorting and searching on click the column headers using PHP programming language and MySQL.
Pagination is important when related content on a website has to be divided into several pages in a good layout. If you list all the data on the same page, it will become more confusing and also have more page loading time. Here is a simple PHP pagination example in which we have fetched the data from the MySQL database and written PHP logic to set pagination on the fetched results. All the coding flows are mentioned step by step, which will make it easier to understand and implement.
In addition to the pagination, we have also described a simple process to sort HTML table data in ascending order using PHP and MySQL. Table sorting provides many more benefits to users. They can easily sort the data column wise to analyse the data more effectively. Here is the process of sorting MySQL Table data by column name in ascending order.
Database
In the first step, we have written database connection code. For this, we have created a MySQL table 'user' and inserted data into it as shown below. You can either use your existing database or copy & paste the given code into MySQL-
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`gender` varchar(10) NOT NULL,
`city` varchar(80) NOT NULL,
`email` varchar(100) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;INSERT INTO `user` (`id`, `name`, `gender`, `city`, `email`, `timestamp`) VALUES
(1, 'Smith', 'male', 'Pune', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2020-07-26 12:34:11'),
(2, 'Priska', 'Female', 'New Delhi', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2020-07-26 12:34:11'),
(3, 'Arya', 'male', 'Kolkatta', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2020-07-26 12:35:58'),
(4, 'Jorz', 'male', 'Pune', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2020-07-26 12:35:58'),
(5, 'Aryan', 'male', 'Banglore', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2020-07-26 12:37:04'),
(6, 'Lussi', 'female', 'Goa', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2020-07-26 12:37:04');config.php
Next, we have created a PHP page 'config.php', where we have written database connection code using the PHP programming language. You can either create this manually or copy and paste this code. Only you will have to change the database, hostname, username and password with your database credentials and name.
<?php
$hostname = "localhost";
$username = "root";
$password = "";
$database= "demo";
// Connect to database
$conn = mysqli_connect($hostname, $username, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>
index.php
This is the main file that we will call in the browser. This file contains HTML and PHP code to display the records and Next and Previous buttons for pagination. For sorting table data, we have put links in the table header. When you click the table header, the sortorder() method will be called, which contains the column field name as a parameter to sort the table.
<!doctype html>
<html>
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" crossorigin="anonymous">
<?php
include("config.php");
$row_per_page = 4;
$row = 0;
// Previous Button
if(isset($_POST['prev'])){
$row = $_POST['row'];
$row -= $row_per_page;
if( $row < 0 ){
$row = 0;
}
}
// Next Button
if(isset($_POST['next'])){
$row = $_POST['row'];
$count = $_POST['count'];
$val = $row + $row_per_page;
if( $val < $count ){
$row = $val;
}
}
// Generate sorting url for table header
function sortorder($field){
$sorturl = "?order_by=".$field."&sort=";
$sorttype = "asc";
if(isset($_GET['order_by']) && $_GET['order_by'] == $field){
if(isset($_GET['sort']) && $_GET['sort'] == "asc"){
$sorttype = "asc";
}
}
$sorturl .= $sorttype;
return $sorturl;
}
?>
</head>
<body>
<div style="width: 40%; margin: 0 auto; text-align: center;">
<table class="table table-striped">
<thead class="table-info">
<th>Id</th>
<th><a href="/" class="sort">Name</a></th>
<th><a href="/" class="sort">Gender</a></th>
<th><a href="/" class="sort">City</a></th>
<th><a href="/" class="sort">Email</a></th>
<?php
// count total number of rows
$query = "SELECT COUNT(*) AS cntrows FROM user";
$result = mysqli_query($con,$query);
$fetchresult = mysqli_fetch_array($result);
$count = $fetchresult['cntrows'];
// selecting rows
$orderby = " ORDER BY id desc ";
if(isset($_GET['order_by']) && isset($_GET['sort'])){
$orderby = ' order by '.$_GET['order_by'].' '.$_GET['sort'];
}
// fetch user data
$query = "SELECT * FROM user ".$orderby." limit $row,".$row_per_page;
$result = mysqli_query($con,$query);
$sno = $row + 1;
while($fetch = mysqli_fetch_array($result)){
$name = $fetch['name'];
$gender = $fetch['gender'];
$city = $fetch['city'];
$email = $fetch['email'];
?>
<tr>
<td align='center'><?php echo $sno; ?></td>
<td align='center'><?php echo $name; ?></td>
<td align='center'><?php echo $gender; ?></td>
<td align='center'><?php echo $city; ?></td>
<td align='center'><?php echo $email; ?></td>
</tr>
<?php
$sno ++;
}
?>
</table>
<form method="post" action="">
<input type="hidden" name="row" value="<?php echo $row; ?>">
<input type="hidden" name="count" value="<?php echo $count; ?>">
<input type="submit" class="btn-primary" name="prev" value="Previous">
<input type="submit" class="btn-primary" name="next" value="Next">
</form>
</div>
</body>
</html>When we execute the above code, the table looks like this-
Related Articles
Ajax live data search using jQuery PHP MySQLHow to encrypt password in PHP
Remove duplicates from array PHP
PHP code to send SMS to mobile from website
How to encrypt password in PHP
PHP remove last character from string
PHP ftp server connection and file handling
PHP code to send email using SMTP
How to lock a file using 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
Simple PHP File Cache
PHP import Excel data to MySQL using PHPExcel
How to get current directory, filename and code line number in PHP
