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.

To Sort MySQL Table Data In PHP

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

Preventing Cross Site Request Forgeries(CSRF) in PHP
PHP code to send email using SMTP
Simple pagination in PHP
Simple PHP File Cache
PHP Connection and File Handling on FTP Server
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
How to Retrieve Emails from Gmail using PHP IMAP
How to store Emoji character in MySQL using PHP
How to display PDF file in PHP from database




Read more articles


General Knowledge



Learn Popular Language