Sorting the table by clicking header with AJAX

With sorting the list it is easier for the user to locate items in a sorted list than unsorted. It rearranges the data in specific order (ascending or descending).

You can either use PHP or AJAX to get the sorted data.

I am using AJAX to sort the list in this tutorial.

In this tutorial, I am displaying the employee’s list and attach click event on the table column header. Whenever header gets clicked then send an AJAX request with column name to sort with.

Sorting the Table by clicking Header with AJAX

Download

Table structure
Configuration
HTML
PHP
jQuery
Demo
Conclusion

1. Table structure

I am using employee table in the example.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(100) NOT NULL,
  `salary` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(100) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php, to define database connection.

Completed Code

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

3. HTML

Listing employee records in <table id='empTable'> and adding onclick in table header <span>. Calling sortTable() function which takes column name and for sorting order creating a hidden field id='sort' which default value is asc.

Completed Code

<?php 
include "config.php";
?>
<div class='container'>
  <input type='hidden' id='sort' value='asc'>
  <table width='100%' id='empTable' border='1' cellpadding='10'>
    <tr>
      <th><span onclick='sortTable("emp_name");'>Name</span></th>
      <th><span onclick='sortTable("salary");'>Salary</span></th>
      <th><span onclick='sortTable("gender");'>Gender</span></th>
      <th><span onclick='sortTable("city");'>City</span></th>
      <th><span onclick='sortTable("email");'>Email</a></th>
    </tr>
    <?php 
    $query = "SELECT * FROM employee ORDER BY id ASC";
    $result = mysqli_query($con,$query);
    while($row = mysqli_fetch_array($result)){
      $name = $row['emp_name'];
      $salary = $row['salary'];
      $gender = $row['gender'];
      $city = $row['city'];
      $email = $row['email'];

    ?>
    <tr>
      <td><?php echo $name; ?></td>
      <td><?php echo $salary; ?></td>
      <td><?php echo $gender; ?></td>
      <td><?php echo $city; ?></td>
      <td><?php echo $email; ?></td>
    </tr>
    <?php
    }
    ?>
  </table>
</div>

4. PHP

Create a new fetch_details.php file.

Using $_POST values in the SELECT query, and storing the result in $html and return it.

Completed Code

<?php

include "config.php";

$columnName = $_POST['columnName'];
$sort = $_POST['sort'];

$select_query = "SELECT * FROM employee ORDER BY ".$columnName." ".$sort." ";

$result = mysqli_query($con,$select_query);

$html = '';
while($row = mysqli_fetch_array($result)){
  $name = $row['emp_name'];
  $salary = $row['salary'];
  $gender = $row['gender'];
  $city = $row['city'];
  $email = $row['email'];

  $html .= "<tr>
    <td>".$name."</td>
    <td>".$salary."</td>
    <td>".$gender."</td>
    <td>".$city."</td>
    <td>".$email."</td>
  </tr>";
}

echo $html;

5. jQuery

Sending AJAX request when table header column gets clicked where pass column name and sort order. On the successful callback update table list with response and update sort order value in the hidden field (<input type='hidden' id='sort'>).

Completed Code

function sortTable(columnName){
 
 var sort = $("#sort").val();
 $.ajax({
  url:'fetch_details.php',
  type:'post',
  data:{columnName:columnName,sort:sort},
  success: function(response){
 
   $("#empTable tr:not(:first)").remove();
 
   $("#empTable").append(response);
   if(sort == "asc"){
     $("#sort").val("desc");
   }else{
     $("#sort").val("asc");
   }
 
  }
 });
}

6. Demo

Click on the column name to sort records.

7. Conclusion

I showed how you can sort table content based on column header clicked. For sorting, I have used AJAX and update table list on successfully callback.

If you found this tutorial helpful then don't forget to share.

Contents

1. Table structure

2. Configuration

3. HTML

4. PHP

5. jQuery

6. Demo

7. Conclusion