DataTables makes pagination implementation easier.
For allowing searching it adds a search box and also adds an up & down arrow on the column header for sorting.
By default, sorting has been added to all columns.
This can remove from a column where it is not required with the columnDefs
option while initializing.
Contents
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 configuration.
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
anddatatables.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
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.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. 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 ajaxfile.php
file to handle AJAX requests.
Read DataTable POST values and assign them to variables. Prepare the search condition if $searchValue
is not empty.
Count the total number of records from employee
table with and without 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.
Initialize $response
Array with required parameters and return in JSON format.
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.