Responsive Datatables with Ajax Pagination, Search Filter in CakePHP 3 Using Bootstrap 3, MySQL and jQuery
In this tutorial we are going to see how to implement Responsive Datatables with Ajax Pagination, Search Filter in CakePHP 3 Using Bootstrap 3, MySQL and jQuery. This is follow up tutorial of the Datatables Ajax Custom Search Filter in CakePHP 3 Using jQuery, MySQL and Bootstrap 3.
We are implementing the three types of responsive Datatables methods in this tutorial.
- Default Responsive Datatables.
- Responsive Datatables With Immediate Child Row
- Responsive Datatables with Modal
Each will be implemented with separate demo example.
- Integrate Datatables in CakePHP 3 with Static JOSN using jQuery, Bootstrap 3
- CakePHP 3 Ajax Pagination Using Datatables, jQuery, MySQL and Bootstrap 3
- CakePHP 3 Ajax Pagination with Search Using Datatables, jQuery, MySQL and Bootstrap 3
- Datatables Ajax Custom Search Filter in CakePHP 3 Using jQuery, MySQL and Bootstrap 3
Step 1: Create CakePHP 3 Controller and Actions for Responsive Datatables With Ajax Pagination and Search Filter :
We need three pages for the three types of responsive Datatables in CakePHP 3, So we need to create create four public function in the Employees controller. Three for three normal pages, and one for ajax.
<?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 responsive()
{
$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 ajaxEmployeesResponsive(){
$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;
}
public function responsiveImmediate()
{
$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 responsiveModal()
{
$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);
}
}
Step 2: Add Required Assets (jQuery, DataTables) for Responsive DataTables With Ajax Pagination:
Next add the required assets (css and js) files in your layout file.
<?= $this->Html->css([ 'bootstrap.min', 'datatables-extensions/dataTables.bootstrap.min',
datatables-extensions/responsive.bootstrap', 'style' ]) ?>
<?=
$this->Html->script([ 'jquery-1.12.3','bootstrap.min', 'jquery.dataTables.min',
'datatables-extensions/dataTables.bootstrap.min',
'datatables-extensions/dataTables.responsive.min'
])
?>
To implement Datatables responsive functionalities, we need responsive.bootstrap.css and dataTables.responsive.min.js are required.
Please refer Initialize Datatable in CakePHP 3 using static JSON data, jQuery and Bootstrap 3 tutorial to create CakePHP 3 layout file.
Step 3: Create View File for Responsive Datatables with Search Filter in CakePHP 3 :
Next create three view files (responsive.ctp, responsive_immediate.ctp & responsive_modal.ctp ) in the following location src/Template/Employees directory. Add the following same HTML markup in each of the files.
<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>
Step 5: Initialize Responsive Datatables in CakePHP 3 Using jQuery :
Next we are initializing each type of responsive Datatables using jQuery by selecting table id.
To Initialize Default Responsive Datatables.
$(document).ready(function() {
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_responsive",
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");
}
}
} );
//Hide Global Search Box
$("#employees-grid_filter").css("display","none");
$('.search-txt').on( 'keyup change', function () {
var i =$(this).attr('data-column');
var v = $.trim( $(this).val() );
dataTable.columns(i).search(v).draw();
} );
} );
To Initialize Responsive Datatables With Immediate Child Row.
Just replace display option in the responsive object.
display: $.fn.dataTable.Responsive.display.childRowImmediate
To Initialize Responsive Datatables with Modal.
Just replace display option in the responsive object.
display: $.fn.dataTable.Responsive.display.modal( {
header: function ( row ) {
var data = row.data();console.log(data);
return 'Details for '+data[1];
}
} )
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
