Remove Sorting from Specific Column – DataTables

DataTables makes pagination implementation easier.

For allowing searching it adds a search box and also adds up & down arrow on the column header for sorting.

By default, sorting has added on all columns.

This can remove from a column where it is not necessary with the columnDefs option while initializing.

Remove sorting from specific column - DataTables


Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. jQuery
  6. PHP
  7. Demo
  8. Conclusion

 


1. Table structure

Create employee table.

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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Create a config.php for 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. Download & Include

  • Download Datatables from here.
  • Include datatables.min.css and datatables.min.js in <head> section and also include 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.3.1/jquery.min.js"></script>

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

4. HTML

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

Specify header columns in <thead >.

Completed Code

<!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.3.1/jquery.min.js"></script>

    <!-- Datatable JS -->
    <script src="//cdn.datatables.net/1.10.19/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. jQuery

Initialize DataTable on $('#empTable') selector.

Set serverMethod to post and ajax url to ajaxfile.php.

Specify column names in columns option.

Remove sorting –

Use columnDefs option to remove sorting from a column.

Pass column index in targets within [] (Indexing starting from 0) and set orderable to false.

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

Completed Code

$(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. PHP

Create a new ajaxfile.php file.

Prepare the search condition if $searchValue is not empty.

Count total records and record with the search filter.

Fetch records from employee table.

Loop on fetched record and initialize $data Array.

In Array, the key name will be similar to the values defined in columns option while Datatable initialization.

Completed Code

<?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


8. Conclusion

Add columnDefs option while initializing where set orderable to false and define column index positions in targets where you want to remove sorting.

The indexing starts from 0.

If you found this tutorial helpful then don't forget to share.
Spread the love
  • 1
  • 6
  •  
  •  
  • 1
  •  

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *