How to add a custom column in Server-side DataTable
This tutorial is on How to add a custom column in Server-side DataTable. I have shown basic server-side data integration with datatable.js through PHP, Ajax, and MySQL. By default server-side DataTable provides a global search box. I will implement custom column search functionality for each column.
index.php
<!DOCTYPE html>
<html>
<title>How to add Custom column in Server side DataTable</title>
<head>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<style>
h2{
text-align: center;
}
.container {
margin: 0 auto;
max-width: 800px;
}
</style>
</head>
<body>
<h2>How to add Custom column in Server side DataTable</h2>
<div class="container">
<table id="student" class="display" width="100%" border="0" cellpadding="0" cellspacing="0">
<thead>
<tr>
<th>Student name</th>
<th>Student fee</th>
<th>Student fee</th>
</tr>
</thead>
<thead>
<th><input type="text" data-column="0" class="input-search"></th>
<th><input type="text" data-column="1" class="input-search"></th>
<th>
<select data-column="2" class="select-input">
<option value="">(Select a age)</option>
<option value="16-26">16 - 26</option>
<option value="27-37">27 - 37</option>
<option value="38-48">38 - 48</option>
<option value="49-59">49 - 59</option>
</select>
</th>
</thead>
</table>
</div>
<script type="text/javascript">
$(document).ready(function() {
var dataTable = $('#student').DataTable({
"processing": true,
"serverSide": true,
"ajax":{
url :"student-data.php",
type: "post",
error: function(){
$(".student-error").html("");
$("#student").append('<tbody class="student-error"><tr><th colspan="3">No data found</th></tr></tbody>');
$("#student_processing").css("display","none");
}
}
});
$("#student_filter").css("display","none"); // hide global search box
$('.input-search').on('keyup click', function (){
var data_column =$(this).attr('data-column');
var input_val =$(this).val();
dataTable.columns(data_column).search(input_val).draw();
});
$('.select-input').on('change', function (){
var data_column =$(this).attr('data-column');
var select_val =$(this).val();
dataTable.columns(data_column).search(select_val).draw();
});
});
</script>
</body>
</html>
PHP script
I have sent four parameters in the JSON output.
- draw
- totalRecords
- filteredRecords
- data
student-data.php
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "devnote";
$conn = mysqli_connect($host,$username,$password,$database) or die("Connection failed: " . mysqli_connect_error());
$request = $_REQUEST;
$columns = array(
0 => 'student_name',
1 => 'student_fee',
2 => 'student_age'
);
$sql = "SELECT student_name, student_fee, student_age";
$sql .= " FROM student";
$res = mysqli_query($conn, $sql);
$totalData = mysqli_num_rows($res);
$totalFilteredData = $totalData;
$sql = " SELECT student_name, student_fee, student_age ";
$sql .= "FROM student WHERE 1 = 1";
if(!empty($request['columns'][0]['search']['value'])){ // student name column
$sql .= " AND student_name LIKE '%".$request ['columns'][0]['search']['value']."%' ";
}
if(!empty($request['columns'][1]['search']['value'])){ // student fee column
$sql.=" AND student_fee LIKE '".$request ['columns'][1]['search']['value']."%' ";
}
if(!empty($request ['columns'][2]['search']['value'])){ // student age column
$rangeArray = explode("-",$request ['columns'][2]['search']['value']);
$minRange = $rangeArray[0];
$maxRange = $rangeArray[1];
$sql .= " AND ( student_age >= '".$minRange."' AND student_age <= '".$maxRange."' ) ";
}
$res = mysqli_query($conn, $sql);
$totalFilteredData = mysqli_num_rows($res);
$sql .= " ORDER BY ". $columns[$request ['order'][0]['column']] ." ". $request['order'][0]['dir'] ." LIMIT ". $request ['start'] ." ,". $request ['length'] ." ";
$res = mysqli_query($conn, $sql);
$data = array();
while($row=mysqli_fetch_array($res) ) {
$nestedArray = array();
$nestedArray[] = $row["student_name"];
$nestedArray[] = $row["student_fee"];
$nestedArray[] = $row["student_age"];
$data[] = $nestedArray;
}
$json_data = array(
"draw" => intval($request['draw']),
"recordsTotal" => intval($totalData),
"recordsFiltered" => intval($totalFilteredData),
"data" => $data
);
echo json_encode($json_data);
?>
