How to Sort Table Data in PHP and MySQL
This article describes a good example of how to sort HTML table data in ascending and descending order on clicking the header using the PHP programming language and MySQL.
Table sorting is a very important functionality. It provides functionality for users to easily sort the data in ascending and descending order as per their requirements. Here is the process of sorting MySQL data in the HTML table by column name in ascending and descending order.
At the start of this article, we divided the complete code into chunks, explaining in detail the sorting process, and at last, we merged them all so that we could 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, pass the sorting table header name and order(ASC/DESC) in the GET and write a select query to fetch employee data order by the given header name and asc/desc order. In the given code, we have stored the GET value of sort and order in the $field and the $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 fetched 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 to 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 when you 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
PHP calculate percentage of totalHow to lock a file using PHP
PHP remove last character from string
Cross site request Forgery prevention PHP
PHP 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
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
Upload multiple files 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
Insert in database without page refresh PHP
