How to Create Custom Filter with Multiple Export Options in DataTable – AJAX, and PHP
2 min read
How to Create Custom Filter with Multiple Export Options in DataTable - AJAX, and PHP
How to Create Custom Filter with Multiple Export Options in DataTable – AJAX, and PHP
Here we will understand the following points on How to Create Custom Filter with Multiple Export Options in DataTable using AJAX and PHP with easy and simple steps.
- 1- How to Binding Data in Datatable using Ajax and PHP.
- 2-How to Create Multiple Custom Filter in Datatable using Ajax and PHP.
- 3- How to Create Multiple Multiple Export Options in Datatable using Ajax and PHP.
- 4-How to Change Loading Image in Datatable
- 5-How to Export Datatable in PDF, CSV, EXCEL Format
- 6-How to Custom Filter using Dropdown in Datatable.
- 7-Create Live Search from Textbox in Datatable using Ajax
- 8-How to Print Datatable using ajax
How to Create Custom Filter with Multiple Export Options in DataTable – AJAX, and PHP View Demo
MySQL Table structure: Custom Filter with Multiple Export Options in DataTable – AJAX, and PHP View Demo
CREATE TABLE `mis_production` (
`sr_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`financial_year` varchar(15) NOT NULL,
`financial_quarter` int(11) NOT NULL,
`product_name` varchar(500) NOT NULL,
`production_unit` varchar(200) NOT NULL,
`total_production` int(11) NOT NULL,
`uploded_on` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Connect Database config.php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = "Do19"; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
How to add a Custom Filter with Multiple Export Options in DataTable - AJAX, and PHP
Product Page – Products.html
<!doctype html>
<html>
<head>
<title>How to Create Custom Filter with Multiple Export Options in DataTable - AJAX, and PHP</title>
<!-- Datatable CSS -->
<link href='DataTables/datatables.min.css' rel='stylesheet' type='text/css'>
<link rel='stylesheet' href='https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css'>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
<!-- jQuery Library -->
<script src="jquery-3.3.1.min.js"></script>
<!-- Datatable JS -->
<script src="DataTables/datatables.min.js"></script>
<script src='https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js'></script>
<script src='https://cdn.datatables.net/buttons/1.2.2/js/buttons.flash.min.js'></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js'></script>
<script src='https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js'></script>
<script src='https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js'></script>
<script src='https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js'></script>
<script src='https://cdn.datatables.net/buttons/1.2.2/js/buttons.print.min.js'></script>
</head>
<body>
<div><h1>How to Create Custom Filter with Multiple Export Options in DataTable - AJAX, and PHP</h1>
<!-- Custom Filter -->
<table Style="width:50%; padding:10px;">
<tr> <th> Product Name</th><th>Financial Year</th><th>Financial Quarter</th></tr>
<tr>
<td>
<input type='text' id="searchByName" class="form-control " placeholder='Enter name'></td><td>
<select id="searchBy_financial_quarter" class="form-control " required="">
<option value="">All</option>
<option value="1"> Q1 - April - June </option>
<option value="2"> Q2 - July - September </option>
<option value="3"> Q3 - October - December </option>
<option value="4"> Q4 - January - March </option>
</select>
</td>
<td>
<select id='searchByfinancial_year' class="form-control ">
<option value="">All</option>
<option value="2008-2009"> 2008-2009 </option>
<option value="2009-2010"> 2009-2010 </option>
<option value="2010-2011"> 2010-2011 </option>
<option value="2011-2012"> 2011-2012 </option>
<option value="2012-2013"> 2012-2013 </option>
<option value="2013-2014"> 2013-2014 </option>
<option value="2014-2015"> 2014-2015 </option>
<option value="2015-2016"> 2015-2016 </option>
</select>
</td>
</tr>
</table>
<br/>
<div id="buttons"></div>
<!-- Table -->
<table id='product' class='display dataTable'>
<thead>
<tr>
<th>User ID</th>
<th>Financial Year</th>
<th>Financial Quarter</th>
<th>Product Name</th>
<th>Production Unit</th>
<th>Total Production</th>
<th>Uploded On</th>
</tr>
</thead>
</table>
</div>
<!-- Script -->
<script>
$(document).ready(function () {
var dataTable = $('#product').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
"lengthMenu": [[10, 25, 50, 100,500,1000], [10, 25, 50,100,500,1000]],
'dom': 'Blfrtip',
'buttons': [
{
"extend": 'pdf',
"text": 'Export PDF',
"className": 'btn btn-primary btn-sm ml-1'
},
{
"extend": 'csv',
"text": 'Export CSV',
"className": 'btn btn-primary btn-sm ml-1'
},
{
'extend': 'excel',
"text": 'Export Excel',
'className': 'btn btn-primary btn-sm ml-1'
}, {
'extend': 'print',
"text": 'Print',
'className': 'btn btn-primary btn-sm ml-1'
}
], "language":
{
"processing": "<img style='width:300px; height:200px;' src='load.gif' />",
},
'ajax': {
'url': 'products.php',
'data': function (data) {
var searchByfinancial_year = $('#searchByfinancial_year').val();
var searchBy_financial_quarter = $('#searchBy_financial_quarter').val();
var searchByName=$('#searchByName').val();
data.searchByfinancial_year = searchByfinancial_year;
data.searchBy_financial_quarter = searchBy_financial_quarter;
data.searchByName = searchByName;
}
},
'columns': [
{ data: 'user_id' },
{ data: 'financial_year' },
{ data: 'financial_quarter' },
{ data: 'product_name' },
{ data: 'production_unit' },
{ data: 'total_production' },
{ data: 'uploded_on' },
]
});
$('#searchByName').keyup(function () {
dataTable.draw();
});
$('#searchBy_financial_quarter').change(function () {
dataTable.draw();
});
$('#searchByfinancial_year').change(function () {
dataTable.draw();
});
});
</script>
</body>
</html>
Product.php
<?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
## Custom Field value
$searchBy_financial_quarter = $_POST['searchBy_financial_quarter'];
$searchByfinancial_year = $_POST['searchByfinancial_year'];
$searchByName = $_POST['searchByName'];
## Search
$searchQuery = " ";
if($searchByName != ''){
$searchQuery .= " and (product_name like '%".$searchByName."%') ";
}
if($searchByfinancial_year != ''){
$searchQuery .= " and (financial_year ='".$searchByfinancial_year."' ) ";
}
if($searchBy_financial_quarter != ''){
$searchQuery .= " and (financial_quarter='".$searchBy_financial_quarter."') ";
}
if($searchValue != ''){
$searchQuery .= " and (user_id like '%".$searchValue."%' or
financial_year like '%".$searchValue."%' or
product_name like '%".$searchValue."%' or
uploded_on like '%".$searchValue."%' or
financial_quarter like'%".$searchValue."%' ) ";
}
## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from mis_production");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of records with filtering
$sel = mysqli_query($con,"select count(*) as allcount from mis_production WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from mis_production 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(
"user_id"=>$row['user_id'],
"financial_year"=>$row['financial_year'],
"financial_quarter"=>$row['financial_quarter'],
"product_name"=>$row['product_name'],
"production_unit"=>$row['production_unit'],
"total_production"=>$row['total_production'],
"uploded_on"=>$row['uploded_on']
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
How to add a Custom Filter with Multiple Export Options in DataTable - AJAX, and PHP
Output How to Create Custom Filter with Multiple Export Options in DataTable – AJAX, and PHP
Download Source Code How to Create Custom Filter with Multiple Export Options in DataTable – AJAX, and PHP
View Demo
How to Create Custom Filter with Multiple Export Options in DataTable – AJAX, and PHP
About Post Author
