How to Sort Table Data in PHP and MySQL
This article describes a good example to sort HTML table data in ascending and descending order on click header using PHP and MySQL. Table sorting is very important functionality. It provides functionality to the users to easily sort the data in ascending and descending order as per their requirement. Here is the process to sort MySQL data in HTML table by column name in ascending and descending order.
At the start of this article, we have divided the complete code into chunks and explained in detail to understand the all sorting process and at last, we have merged them all so that we can get the complete code.
For this, let's first create a PHP file name 'sortingtbl.php' and write the MySQL database connection code using PHP MySQLi at the top. Please make sure to replace the hostname, username, password and database with your database credentials.
<?php
$conn = new mysqli('localhost', 'root', '', 'company');
if($conn->connect_error){
die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);
}
?>
Next, we will make the column header clickable so that the user can toggle the column for sorting. This can be done by providing links on the sorting column headers. So let's provide the anchor tag and pass the sorting table header name and order(ASC/DESC) in the GET and write select query to fetch employee data order by the given header name and asc/desc order. In the below code, we have stored the GET value of sort and order in $field and $ordertype variables respectively.
<?php
$field = $_GET['sort'];
if($field == ''){
$field = 'created_date';
}
$ordertype = ($_GET['order'] == 'desc')? 'asc' : 'desc';
if($_GET['order'] == 'asc'){
$sort_arrow = '<img src="sorting-arrow-desc.png" />';
}
else if($_GET['order'] == 'desc'){
$sort_arrow = '<img src="sorting-arrow-asc.png" />';
}
else{
$sort_arrow = '<img src="sorting-arrow-desc.png" />';
}
$select = "SELECT * FROM `employee` ORDER BY $field $ordertype ";
$result = $conn->query($select);
?>
Now, display the fetch employee data in an HTML table format and for sorting, put links in the table header as we discussed. The table header links pass sort and order parameters on the same file.
<?php
if($result->num_rows > 0){
echo '<table class="table table-striped" >';
echo '<tr>';
echo '<th><a href="sortingtbl.php?sort=emp_id&order='.$ordertype.'">Id ';
if($field == 'emp_id') { echo $sort_arrow; }
echo '</a></th>';
echo '<th><a href="sortingtbl.php?sort=emp_name&order='.$ordertype.'">Employee Name ';
if($field == 'emp_name') { echo $sort_arrow; }
echo '</a></th>';
echo '<th>Email</th>';
echo '<th>Phone</th>';
echo '<th><a href="sortingtbl.php?sort=created_date&order='.$ordertype.'">Registered Date ';
if($field == 'created_date') {
echo $sort_arrow;
}
echo '</a></th>';
echo '</tr>';
while($row = $result->fetch_object()){
echo '<tr>';
echo '<td>'.$row->emp_id.'</td>';
echo '<td>'.$row->emp_name.'</td>';
echo '<td>'.$row->email.'</td>';
echo '<td>'.$row->phone.'</td>';
echo '<td>'.date('Y-m-d', strtotime($row->created_date)).'</td>';
echo '</tr>';
}
echo '</table>';
}
?>
Complete Code: To Sort Table Data in PHP and MySQL
Here is the complete code to sort table data in ascending and descending order on toggle the table header.
<html>
<head>
<link rel="stylesheet" href="bootstrap.min.css" />
<style type="text/css">
.table { width: 40%; border: 2px solid #edf7b7 ; }
th {background: #edf7b7 none repeat scroll 0 0 !important; }
</style>
</head>
<body>
<?php
$conn = new mysqli('localhost', 'root', '', 'company');
if($conn->connect_error){
die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);
}
$field = $_GET['sort'];
if($field == ''){
$field = 'created_date';
}
$ordertype = ($_GET['order'] == 'desc')? 'asc' : 'desc';
if($_GET['order'] == 'asc'){
$sort_arrow = '<img src="sorting-arrow-desc.png" />';
}
else if($_GET['order'] == 'desc'){
$sort_arrow = '<img src="sorting-arrow-asc.png" />';
}
else{
$sort_arrow = '<img src="sorting-arrow-desc.png" />';
}
$select = "SELECT * FROM `employee` ORDER BY $field $ordertype ";
$result = $conn->query($select);
if($result->num_rows > 0){
echo '<table class="table table-striped" >';
echo '<tr>';
echo '<th><a href="sortingtbl.php?sort=emp_id&order='.$ordertype.'">Id ';
if($field == 'emp_id') { echo $sort_arrow; }
echo '</a></th>';
echo '<th><a href="sortingtbl.php?sort=emp_name&order='.$ordertype.'">Employee Name ';
if($field == 'emp_name') { echo $sort_arrow; }
echo '</a></th>';
echo '<th>Email</th>';
echo '<th>Phone</th>';
echo '<th><a href="sortingtbl.php?sort=created_date&order='.$ordertype.'">Registered Date ';
if($field == 'created_date') {
echo $sort_arrow;
}
echo '</a></th>';
echo '</tr>';
while($row = $result->fetch_object()){
echo '<tr>';
echo '<td>'.$row->emp_id.'</td>';
echo '<td>'.$row->emp_name.'</td>';
echo '<td>'.$row->email.'</td>';
echo '<td>'.$row->phone.'</td>';
echo '<td>'.date('Y-m-d', strtotime($row->created_date)).'</td>';
echo '</tr>';
}
echo '</table>';
}
?>
</body>
</html>
When we will execute the above code, the table look like this -
Related Articles
Cross site request Forgery prevention PHPPHP code to send email using Gmail SMTP server
Simple pagination in PHP with mysqli example
PHP cache example
PHP script to upload file to FTP server
PHP create Word document from HTML Send HTML form data to email using PHP
Forgot password code in PHP mysqli
Retrieve your Gmail emails using PHP and IMAP
How to store emoji in MySQL PHP
How to display PDF file in PHP from database
PHP create Word document from HTML
PHP SplFileObject Standard Library
File upload in PHP MySQL database
