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.cssanddatatables.min.jsin<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.