Remove Sorting from Specific Column - DataTables

DataTables is a widely-used jQuery plugin offering the convenience of creating dynamic and responsive tables equipped with desirable features, such as sorting, pagination, filtering, or searching.

Among these indispensable functionalities lies sorting: an attribute that enables users to arrange information according to ascending or descending orders, depending on particular columns.

Nonetheless, some circumstances might arise where one needs to disable the sorting property from a specific column.

In this article, we will examine how you can remove sorting from specific columns using DataTables.

Demo Download

Table structure
Database Configuration
Download & Include DataTables
HTML structure
Initialize DataTable and Disable sorting
AJAX – Load DataTable data
Demo
Conclusion

1. Table structure

I am using employee table in the example. It has the following structure –

CREATE TABLE `employee` (
   `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
   `emp_name` varchar(80) NOT NULL, 
   `salary` varchar(20) NOT NULL,
   `gender` varchar(10) NOT NULL,
   `city` varchar(80) NOT NULL,
   `email` varchar(80) NOT NULL
);

2. Database Configuration

Create a config.php for database configuration.

<?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. Download & Include DataTables

Download Datatables from here.
Include datatables.min.css and datatables.min.js in <head> section and also include the jQuery Library.
You can also use CDN.

<!-- Datatable CSS -->
<link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'> 

<!-- jQuery Library --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- Datatable JS --> 
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>

4. HTML Structure

Create a <table > where add class='display datatable' and id='empTable'.

Specify header columns in <thead >.

<!doctype html>
<html>
<head>
    <title>Remove sorting from specific column - DataTables</title>
    <!-- Datatable CSS -->
    <link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

    <!-- jQuery Library -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

    <!-- Datatable JS -->
    <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>  
</head>
<body >

    <div >
         <!-- Table -->
         <table id='empTable' class='display dataTable'>
              <thead>
                   <tr>
                       <th>Employee name</th>
                       <th>Email</th>
                       <th>Gender</th>
                       <th>Salary</th>
                       <th>City</th>
                   </tr>
              </thead>

         </table>
    </div>
</body>
</html>

5. Initialize DataTable and Disable sorting

Initialize DataTable on $('#empTable'). Set serverMethod to post and ajax url to ajaxfile.php.

Specify column names in columns option.

Remove sorting –

To remove sorting, use the columnDefs option and pass the column index within [] (indexing starting from 0) to the targets parameter. Then, set orderable to false.

In the example, I removed sorting from email and salary column.

$(document).ready(function(){
    // Initialize
    $('#empTable').DataTable({
         'processing': true,
         'serverSide': true,
         'serverMethod': 'post',
         'ajax': {
               'url':'ajaxfile.php'
         },
         'columns': [
               { data: 'emp_name' }, // index - 0
               { data: 'email' }, // index - 1
               { data: 'gender' }, // index - 2
               { data: 'salary' }, // index - 3
               { data: 'city' } // index - 4
         ],
         'columnDefs': [ {
               'targets': [1,3], // column index (start from 0)
               'orderable': false, // set orderable false for selected columns
         }]
    });
});

6. AJAX – Load DataTable data

To handle AJAX requests, create an ajaxfile.php and read the POST values from DataTables.

Assign them to variables and prepare a search condition if $searchValue exists. Count the total number of records from the employee table with and without the search filter.

Fetch the records from the employee table and loop through them, initializing the $data Array. The key name in the Array should match the values in the columns option during Datatable initialization.

Finally, initialize the $response Array with the required parameters and return it in JSON format.

<?php
include 'config.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

## Search 
$searchQuery = " ";
if($searchValue != ''){
     $searchQuery = " and (emp_name like '%".$searchValue."%' or 
         email like '%".$searchValue."%' or 
         city like'%".$searchValue."%' ) ";
}

## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];

## Total number of records with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();

while ($row = mysqli_fetch_assoc($empRecords)) {
    $data[] = array(
         "emp_name"=>$row['emp_name'],
         "email"=>$row['email'],
         "gender"=>$row['gender'],
         "salary"=>$row['salary'],
         "city"=>$row['city']
    );
}

## Response
$response = array(
   "draw" => intval($draw), 
   "iTotalRecords" => $totalRecords,
   "iTotalDisplayRecords" => $totalRecordwithFilter,
   "aaData" => $data
);

echo json_encode($response);

7. Demo

View Demo

8. Conclusion

When initializing DataTables, use the columnDefs option to set orderable to false and define the column index positions in targets to remove sorting. Remember that indexing starts from 0.

By using this option, you can easily remove sorting from specific columns in your table and customize the sorting functionality to fit your needs.

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

Remove Sorting from Specific Column – DataTables

Contents