How to Sort table by column in PHP & MySQL?

Introduction

In this tutorial, we will learn how to sort table by column in PHP.

For Example, When we click on table column headers to toggle data, it will sort table by column by field name using ORDER BY.

HTML table will contain data from a MySQL database table and we will use PHP & MySQL to solve this problem and sort table by column or sort table data by value.

MySQL Table

Let’s create a simple MySQL table “Students” in a sample database “School” with the field. We will sort this data using PHP by table value.

• Name
• Age
• Gender
• Admission Date

I am not going to show you, how to create a database table.

DB Connection

Let’s create a DB connection file dbconnect.php for MySQL database connection.

<?php
/* 
*Lets write a function for database connection
*/
function DbConnect()
 {
    $dbhost = "localhost";
    $dbuser = "root";
    $dbpass = "";
    $db = "sample";
    $conn = new mysqli($dbhost, $dbuser, $dbpass,$db) or die("Connect failed: %s\n". $conn -> error);
 
   return $conn;
 }
 // DB close connection
function CloseDbConnect($conn)
 {
   $conn -> close();
 }

PHP & MySQL Code to sort table by columns

Let’s create a PHP file “sorting.php“. We will write our HTML, PHP and MySQL code in this file. We will do this in a procedural way.

Must see , How to write PHP code in HTML.

Let’s create an HTML table header in PHP code. We will be sorting the table by Name, Age, Gender and Admission date columns.

Sample HTML table data for representation:

<table>
	<tr>
		<th><a href="sorting.php?sort=name">Name</a></th>
		<th><a href="sorting.php?sort=age">Age</a></th>
                <th><a href="sorting.php?sort=date">Admission Date</a></th>
		<th><a href="sorting.php?sort=gender">Gender></a></th>
	</tr>
        $tr
</table>

We can see that above code having table header with sorting enabled. And $tr is a dynamically generated table from the database using MySQL Select query.

Now, let’s handle sorting code in PHP.

if (isset($_GET['sort'])) {
 $column = trim(strip_tags($_GET['sort']));
 $orderby = "ORDER BY $column";
}
$query = "SELECT * FROM students" .  $orderby;
$qrh = mysqli_query($query);
while($row = mysql_fetch_assoc($qrh)) {
  $tr .= "<tr>
          <td> ".$row['Name']."   </td>
          <td> ".$row['Age']."   </td>
          <td> ".$row['date']."   </td>
          <td> ".$row['Gender']."   </td>
          </tr>
      ";
}
$table = "
<table>
	<tr>
		<th><a href="sorting.php?sort=name">Name</a></th>
		<th><a href="sorting.php?sort=age">Age</a></th>
                <th><a href="sorting.php?sort=date">Admission Date</a></th>
		<th><a href="sorting.php?sort=gender">Gender></a></th>
	</tr>
        $tr
</table>

";

Complete Code

<?php
include 'dbConnection.php';
$connection = DbConnect();

// Get sort
if (isset($_GET['sort'])) {
 $column = trim(strip_tags($_GET['sort']));
 $orderby = "ORDER BY $column";
} else {
  $orderby = "";
}
$query = "SELECT * FROM students" .  $orderby;
$qrh = mysqli_query($query);
while($row = mysql_fetch_assoc($qrh)) {
  $tr .= "<tr>
          <td> ".$row['Name']."   </td>
          <td> ".$row['Age']."   </td>
          <td> ".$row['date']."   </td>
          <td> ".$row['Gender']."   </td>
          </tr>
      ";
}
$table = "
<table>
	<tr>
		<th><a href="sorting.php?sort=name">Name</a></th>
		<th><a href="sorting.php?sort=age">Age</a></th>
                <th><a href="sorting.php?sort=date">Admission Date</a></th>
		<th><a href="sorting.php?sort=gender">Gender></a></th>
	</tr>
        $tr
</table>

";
echo $table;
?>

Conclusion

Hope the above code block will help to understand the process of sort table by column in PHP MySQL table data.

Solving any problem always have multiple ways. I always open to suggestion and improvements.