I've created a data table using the following codes :-
main.php - it contain the script to call data table and the php script which is fetching the data from sql database
<!DOCTYPE html>
<html>
<head>
<title>Data Table | Server Side | Basic | Zero Level</title>
</head>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<body>
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>Name</th><th>Gender</th><th>Age</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Name</th><th>Gender</th><th>Age</th>
</tr>
</tfoot>
</table>
</body>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script type="text/javascript" src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.flash.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.print.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#example').DataTable( {
dom: 'Bfrtip',
buttons: [
'copy', 'csv', 'excel', 'pdf', 'print'
],
"bProcessing": true,
"sAjaxSource": "dtServerSideBasicScript.php",
"aoColumns": [{
mData: 'name','gender','age'
}]
} );
} );
</script>
</html>
and dtServerSideBasicScript.php - It is the script which is fetching the data from sql server :-
<?php
header('Content-Type: application/json');
$con = mysqli_connect("localhost","root","","work");
$sql = "SELECT name,gender,age from test ";
$r = mysqli_query($con,$sql);
$result = array();
while($row = mysqli_fetch_array($r)){
array_push($result,array(
"name"=>$row['name'],"gender"=>$row['gender'],"age"=>$row['age']
));
}
echo json_encode(array('data'=>$result));?>
Now, I've to apply advance filter section above the data table, which can be a form that consist of following fields - a name input, an age range input and a gender select input field. And on submission of this form the relevant search result should be displayed in the data table.
Let me give you example for search by name you can do same for other search field also.
add below input tag in your html for search field
<input type="text" name="user_name" id="user_name" />
now change your script as per below,
<script type="text/javascript">
$(document).ready(function() {
var table=$('#example').DataTable( {
dom: 'Bfrtip',
buttons: [
'copy', 'csv', 'excel', 'pdf', 'print'
],
"bProcessing": true,
"ajax": {
url: "dtServerSideBasicScript.php",
data: function (d) {
d.user_name = function () {
return $("#user_name").val();
};
},
},
"aoColumns": [{
mData: 'name','gender','age'
}]
} );
$('#user_name').keyup(function () {
table.draw();
});
});
</script>
on server side you will get user_name parameter in $_GET, like $_GET['user_name']. then you can use that value in your sql query with like.
Same way you can implement the age range and gender selection.