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 using PHP and MySQL. Table sorting is very important functionality. It provides much more benefits to the users, they can easily sort the data column wise in different order as per their requirement. Here is the process to sort MYSQL Table data by column name in ascending and descending order.
At the starting of this article, we have divided the complete code into chunks and explain 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);
}
?>
Now to make the table column header sort-able, we need to provide 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 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 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.
<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 -
How to encrypt password in PHP
How to get current directory, filename and code line number in PHP
