PHP Custom Filter in DataTable using Ajax Example
Apr 13, 2022 . Admin
Hello dev,
I am going to explain you PHP Custom Filter in DataTable using Ajax Example. You will learn How to add Custom Filter in DataTable-AJAX and PHP.
This article will give you simple example of Add Custom Search and Filter to DataTables Server-side Processing with PHP. We will use Add Server-side DataTables Custom Filter using PHP with Ajax.
I will give you simple Example of How to add custom filter in DataTable Ajax and PHP?.
So, let's see bellow solution:
connection.php
<?php
$connect = new PDO("mysql:host=localhost;dbname=aatman", "root", "root");
?>
index.php
<?php
include('connection.php');
$country = '';
$query = "SELECT DISTINCT country FROM students ORDER BY country ASC";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row){
$country .= '<option value="'.$row['country'].'">'.$row['country'].'</option>';
}
?>
<html>
<head>
<title>PHP Custom Filter in DataTable using Ajax Example - mywebtuts.com</title>
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap5.min.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" integrity="sha512-894YE6QWD5I59HgZOGReFYm4dnWc1Qt5NtvYSaNcOP+u1T9qYdvdihz0PPSiiqn/+/3e7Jo4EaG7TubfWGUrMQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js" integrity="sha384-cVKIPhGWiC2Al4u+LWgxfKTRIcfu0JTxR+EQDz/bgldoEyl4H0zUF0QKbrJ0EcQF" crossorigin="anonymous"></script>
</head>
<body>
<div class="container mt-4">
<div class="card">
<div class="card-header">
<h1 class="text-center">PHP Custom Filter in DataTable using Ajax Example</h1>
</div>
<div class="card-body">
<div class="row d-flex justify-content-center mb-4">
<div class="col-md-4">
<select name="filter_gender" id="filter_gender" class="form-control" required>
<option value="">Select Gender</option>
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
</div>
<div class="col-md-4">
<select name="filter_country" id="filter_country" class="form-control" required>
<option value="">Select Country</option>
<?php echo $country; ?>
</select>
</div>
<div class="col-md-2">
<button type="button" name="filter" id="filter" class="btn btn-success"><i class="fa fa-search" aria-hidden="true"></i> Search</button>
</div>
</div>
<table id="customer_data" class="table table-bordered table-striped mt-4">
<thead>
<tr>
<th width="20%">Customer Name</th>
<th width="10%">Gender</th>
<th width="25%">Address</th>
<th width="15%">City</th>
<th width="15%">Postal Code</th>
<th width="15%">Country</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
fill_datatable();
function fill_datatable(filter_gender = '', filter_country = ''){
var dataTable = $('#customer_data').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"searching" : false,
"ajax" : {
url:"fetch.php",
type:"POST",
data:{
filter_gender:filter_gender, filter_country:filter_country
}
}
});
}
$('#filter').click(function(){
var filter_gender = $('#filter_gender').val();
var filter_country = $('#filter_country').val();
if(filter_gender != '' && filter_country != ''){
$('#customer_data').DataTable().destroy();
fill_datatable(filter_gender, filter_country);
}else{
alert('Select Both filter option');
$('#customer_data').DataTable().destroy();
fill_datatable();
}
});
});
</script>
fetch.php
<?php
include('connection.php');
$column = array('first_name', 'gender', 'address', 'city', 'postalcode', 'country');
$query = "SELECT * FROM students ";
if(isset($_POST['filter_gender'], $_POST['filter_country']) && $_POST['filter_gender'] != '' && $_POST['filter_country'] != '')
{
$query .= 'WHERE gender = "'.$_POST['filter_gender'].'" AND country = "'.$_POST['filter_country'].'" ';
}
if(isset($_POST['order']))
{
$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}else {
$query .= 'ORDER BY id DESC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$statement = $connect->prepare($query . $query1);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['first_name'];
$sub_array[] = $row['gender'];
$sub_array[] = $row['address'];
$sub_array[] = $row['city'];
$sub_array[] = $row['postalcode'];
$sub_array[] = $row['country'];
$data[] = $sub_array;
}
function count_all_data($connect)
{
$query = "SELECT * FROM students";
$statement = $connect->prepare($query);
$statement->execute();
return $statement->rowCount();
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => count_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
Output:
I hope it will help you...