Datatables Ajax Custom Search Filter in CakePHP 3 Using jQuery, MySQL and Bootstrap 3
In this tutorial we are going to see how to implement Datatables Ajax Custom Search Filter in CakePHP 3 Using jQuery, MySQL and Bootstrap 3. This is follow up tutorial of the CakePHP 3 Ajax Pagination with Search Using Datatables, jQuery, MySQL and Bootstrap 3.
Note:
This Datatables Ajax Custom Search Filter in CakePHP 3 is greatly improve the searching capability performance by comparing global search filter. Because in the global search filter irrespective search text all the fields are added in the where condition, this greatly reduces search performance. This disadvantages removed in this Datatables ajax custom search filter by adding only searched field in the where condition.
Step 1: Create CakePHP 3 Controller and Actions for jQuery Datatables Custom Search Filter :
Now create two public functions ( customSearch & ajaxEmployeesCustomSearch ) and add required SQL query and logic’s in it.
<?php
namespace App\Controller;
use App\Controller\AppController;
use Cake\Datasource\ConnectionManager;
use Faker;
/**
* Employees Controller
*
* @property \App\Model\Table\EmployeesTable $Employees
*/
class EmployeesController extends AppController
{
public function customSearch()
{
$this->viewBuilder()->layout('datatables');
$query = array();
$query['count'] = "SELECT count( Employees.id) AS count FROM `employees` Employees LEFT JOIN departments Departments ON Employees.`department_id` = Departments.id LEFT JOIN designations Designations ON Employees.`designation_id` = Designations.id WHERE 1=1 ";
$query['detail'] = "SELECT Employees.id, Employees.`first_name`, Employees.`last_name`, Employees.`email`, Employees.uuid, Employees.`created`, Departments.dept_name, Designations.name FROM `employees` Employees LEFT JOIN departments Departments ON Employees.`department_id` = Departments.id LEFT JOIN designations Designations ON Employees.`designation_id` = Designations.id WHERE 1=1 ";
$this->request->session()->write('query', $query);
}
public function ajaxEmployeesCustomSearch(){
$this->autoRender = false;
$requestData= $this->request->data;
extract($this->request->session()->read('query'));
$cond = ' ';
// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){ //emp_no
$cond.=" AND Employees.id LIKE '".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){ //first_name
$cond.=" AND UPPER(Employees.first_name) LIKE '".strtoupper($requestData['columns'][1]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //last_name
$cond.=" AND UPPER(Employees.last_name) LIKE '".strtoupper($requestData['columns'][2]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][3]['search']['value']) ){ //email
$cond.=" AND UPPER(Employees.email) LIKE '".strtoupper($requestData['columns'][3]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][4]['search']['value']) ){ //Designations Name
$cond.=" AND UPPER(Designations.name) LIKE '".strtoupper($requestData['columns'][4]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][5]['search']['value']) ){ //Departments name
$cond.=" AND UPPER(Departments.dept_name) LIKE '".strtoupper($requestData['columns'][5]['search']['value'])."%' ";
}
$columns = array(
0 => 'Employees.id',
1 => 'Employees.first_name',
2 => 'Employees.last_name',
3 => 'Employees.email',
4 => 'Designations.name',
5 => 'Departments.dept_name',
6 => 'Employees.created',
7 => 'Employees.uuid',
);
$count = $count.$cond;
$detail = $detail.$cond;
$conn = ConnectionManager::get('default');
$results = $conn->execute($count)->fetchAll('assoc');
$totalData = isset($results[0]['count']) ? $results[0]['count'] : 0;
$totalFiltered = $totalData;
$sidx = $columns[$requestData['order'][0]['column']];
$sord = $requestData['order'][0]['dir'];
$start = $requestData['start'];
$length = $requestData['length'];
$SQL = $detail." ORDER BY $sidx $sord LIMIT $start , $length ";
$results = $conn->execute( $SQL )->fetchAll('assoc');
$i = 0;
$data = array();
foreach ( $results as $row){
$nestedData= [];
$nestedData[] = $row["id"];
$nestedData[] = $row["first_name"];
$nestedData[] = $row["last_name"];
$nestedData[] = $row['email'];
$nestedData[] = $row['name'];
$nestedData[] = $row['dept_name'];
$nestedData[] = $row['created'];
$data[] = $nestedData;
$i++;
}
$json_data = array(
"draw" => intval( $requestData['draw'] ),
"recordsTotal" => intval( $totalData ),
"recordsFiltered" => intval( $totalFiltered ),
"data" => $data
);
echo json_encode($json_data);exit;
}
}
Please refer following tutorial to know more about what is happening in ajax functions.
CakePHP 3 Ajax Pagination Using Datatables, jQuery, MySQL and Bootstrap 3
Step 2: Add Required Assets (jQuery, DataTables) for Datatables Ajax Custom Search Filter:
Next add the required assets (css and js) files in your layout file.
<?= $this->Html->css([ 'bootstrap.min', 'datatables-extensions/dataTables.bootstrap.min', 'style' ]) ?>
<?=
$this->Html->script([ 'jquery-1.12.3','bootstrap.min', 'jquery.dataTables.min',
'datatables-extensions/dataTables.bootstrap.min'
])
?>
Please refer Initialize Datatables in CakePHP 3 using static JSON data, jQuery and Bootstrap 3 tutorial to create CakePHP 3 layout file.
Step 3: Create View File for Datatables Custom Search Filter in CakePHP 3 :
Next We need to create view file ‘custom_search.ctp’ in the following location src/Template/Employees directory. Next add the following the HTML markup in it for Datatables ajax Custom Search Filter in CakePHP 3.
<div class="row">
<div class="col-md-12">
<div class="table-responsive12">
<table id="employees-grid" class="table table-striped">
<thead>
<tr>
<th>Employees No</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Designations</th>
<th>Department</th>
<th>Created</th>
</tr>
</thead>
<thead>
<tr>
<td><input type="text" data-column="0" class="form-control search-txt"></td>
<td><input type="text" data-column="1" class="form-control search-txt"></td>
<td><input type="text" data-column="2" class="form-control search-txt"></td>
<td><input type="text" data-column="3" class="form-control search-txt"></td>
<td><input type="text" data-column="4" class="form-control search-txt"></td>
<td><input type="text" data-column="5" class="form-control search-txt"></td>
</tr>
</thead>
</table>
</div>
</div>
</div>
We have added one more <thead> with number of required text fields in it for custom search for each field.
Where data-column="0" options are important, which will be used for preparing custom SQL filter queries.
Step 4: Initialize Datatables Using jQuery Datatables with Ajax Datasource: :
Next initialize Datatables by selecting the table ID $(‘#employees-grid’) and initialize that using this following DataTable() function with required options in it.
var dataTable = $('#employees-grid').DataTable( {
responsive: {
details: {
display: $.fn.dataTable.Responsive.display.childRow,
renderer: function ( api, rowIdx ) {
var data = api.cells( rowIdx, ':hidden' ).eq(0).map( function ( cell ) {
var header = $( api.column( cell.column ).header() );
return '<p style="color:#00A">'+header.text()+' : '+api.cell( cell ).data()+'</p>';
} ).toArray().join('');
return data ? $('<table/>').append( data ) : false;
}
}
},
"processing": true,
"serverSide": true,
"ajax":{
url :base_path+"employees/ajax_employees_custom_search",
type: "post",
error: function(){
$(".employees-grid-error").html("");
$("#employees-grid").append('<tbody class="employees-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
$("#employees-grid_processing").css("display","none");
}
}
} );
Next hide the global search box using following jQuery script.
//Hide Global Search Box
$("#employees-grid_filter").css("display","none");
Next initialize the text fields we have added for custom search filter. Following script will make ajax call to the provided ajax url (while initializing the datatables) with search parameter entered in the textbox when user started to type something.
$('.search-txt').on( 'keyup change', function () {
var i =$(this).attr('data-column');
var v = $.trim( $(this).val() );
dataTable.columns(i).search(v).draw();
} );
Step 5: Form SQL Query For Datatables Custom Search Filter in CakePHP 3 :
In the ajax function we are getting posted data, and forming the custom SQL filter query. If that particular value exists then it will add that condition in where condition, other wise it won’t add. It will increase performance greatly unlike global search filter.
$requestData= $this->request->data;
extract($this->request->session()->read('query'));
$cond = ' ';
// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){ //emp_no
$cond.=" AND Employees.id LIKE '".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){ //first_name
$cond.=" AND UPPER(Employees.first_name) LIKE '".strtoupper($requestData['columns'][1]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //last_name
$cond.=" AND UPPER(Employees.last_name) LIKE '".strtoupper($requestData['columns'][2]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][3]['search']['value']) ){ //email
$cond.=" AND UPPER(Employees.email) LIKE '".strtoupper($requestData['columns'][3]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][4]['search']['value']) ){ //Designations Name
$cond.=" AND UPPER(Designations.name) LIKE '".strtoupper($requestData['columns'][4]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][5]['search']['value']) ){ //Departments name
$cond.=" AND UPPER(Departments.dept_name) LIKE '".strtoupper($requestData['columns'][5]['search']['value'])."%' ";
}
Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts
If you want any of my script need to be customized according to your business requirement,
Please feel free to contact me [at] muni2explore[at]gmail.com
Note: But it will be charged based on your customization requirement
