Datatables Scroller in CakePHP 3 Using jQuery, Ajax, MySQL and Bootstrap 3
In this tutorial we are going to see how to integrate Datatables Scroller in CakePHP 3 using jQuery, Ajax, MySQL and Bootstrap 3. This Datatables Scroller will render millions of data within the datatables itself while Scrolling as per browser Viewport.
This Datatables Scroller will make ajax request and get the data from backend (CakePHP 3 & MYSQL) on demand as per the Scroller Viewport. So this prevents need of pagination, improves performance and reduces user effort. This exactly gives feel like Checking Facebook status on Facebook timeline.
Note: To integrate Datatables Scroller, you must include following Datatable Scroller plugins in your page.
<link rel="stylesheet" href="scroller.bootstrap.min.css">
<script src="dataTables.scroller.min.js"></script>
Step 1: Create CakePHP 3 Controller and Actions for Datatables Scroller:
Please create two actions (scroller & ajaxEmployeesAction ) in the EmployeesController. One ( scroller) for normal view rendering, another one is ajax action (ajaxEmployeesAction) which handles ajax request that comes from frontend.
<?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
{
/**
* scroller method
*/
public function scroller()
{
$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);
}
/**
* ajaxEmployeesAction method
*/
public function ajaxEmployeesAction(){
$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= [];
$link = "<div class='btn-group action '>
<button type='button' class='btn btn-danger'> <i class='fa fa-cog' aria-hidden='true'></i> Options</button>
<button type='button' class='btn btn-danger dropdown-toggle' data-toggle='dropdown' aria-haspopup='true' aria-expanded='false'>
<span class='caret'></span>
<span class='sr-only'>Toggle Dropdown</span>
</button>
<ul class='dropdown-menu'>
<li><a title='Edit' href='#'> <i class='fa fa-edit'></i> Edit</a></li>
<li class='divider'></li>
<li><a title='Edit' href='#'> <i class='fa fa-sticky-note'></i> New Invoice </a></li>
<li><a title='Edit' href='#'> <i class='fa fa-edit'></i> New Quote </a></li>
<li><a title='Edit' href='#'> <i class='fa fa-money'></i> Enter Payment </a></li>
<li class='divider'></li>
<li><a class='smart_delete' href='#'> <i class='fa fa-trash-o'></i> Delete</a></li>
</ul>
</div>";
$nestedData[] = $row["id"];
$nestedData[] = $row["first_name"];
$nestedData[] = $row["last_name"];
$nestedData[] = $row['email'];
$nestedData[] = $row['name'];
$nestedData[] = $row['dept_name'];
$nestedData[] = $row['created'];
$nestedData[] = $link;
$data[] = $nestedData;
$i++;
}
$json_data = array(
"draw" => intval( $requestData['draw'] ),
"recordsTotal" => intval( $totalData ),
"recordsFiltered" => intval( $totalFiltered ),
"data" => $data
);
echo json_encode($json_data);exit;
}
}
Step 2: Add Required Assets (jQuery, DataTables) for Responsive DataTables Scroller With Ajax Pagination:
Please include necessary assets files (jQuery, CSS and Datatables libraries) in your layout file to implement Datatables Scroller in CakePHP 3.
<?= $this->Html->css([ 'bootstrap.min', 'datatables-extensions/dataTables.bootstrap.min',
'datatables-extensions/responsive.bootstrap', 'datatables-extensions/scroller.bootstrap.min', 'style' ]) ?>
<?=
$this->Html->script([ 'jquery-1.12.3','bootstrap.min', 'jquery.dataTables.min',
'datatables-extensions/dataTables.bootstrap.min',
'datatables-extensions/dataTables.responsive.min',
'datatables-extensions/dataTables.scroller.min'
])
?>
Please refer Basic Datatables Implementation 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 Scroller with Search Filter in CakePHP 3 :
Next create view file (scroller.ctp) in the following location src/Template/Employees directory. Add the following same HTML markup in that file.
<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>
<th>Action</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>
Step 4: Initialize Datatables Scroller in CakePHP 3 Using jQuery :
Now initialize the Datatables Scroller using DataTable() function by selecting table ID…
$(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_action",
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");
}
},
deferRender: true,
scrollY: 300,
scrollCollapse: true,
scroller: true,
fnInitComplete :function(){
$('div.dataTables_scrollBody').css('min-height', 0);
}
} );
//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();
} );
} );
Where
deferRender – this will create the DOM and render the data when user scrolls down
scrollY – this will enable vertical scrolling in DataTables.
loadingIndicator – this will enable the loading indicator while data is requested
Note : Please add following CSS change in your CSS file, This will fix data loading issue.
.dataTables_scrollBody{
min-height: 400px;
}
Step 5: Enable Datatables Scroller stateSave Functionality:
This will save the state of a table (its paging position, ordering state etc), so that is can be restored when the user reloads a page, or comes back to the page after visiting a sub-page. This state saving ability is enabled by the stateSave option.
$(document).ready(function() {
$('#employee-grid').DataTable( {
ajax: 'ajaxURL',
deferRender: true,
scrollY: 200,
scrollCollapse: true,
scroller: true,
stateSave: true
} );
} );
Step 6: Initialize Datatables Scroller scrollTo Functionality:
If you enable Datatables Scroller scrollTo functionality, then on page load Datatables Scroll to a specific row mentioned during Initialization.
$(document).ready(function() {
$('#employee-grid').DataTable( {
ajax: 'ajaxURL',
deferRender: true,
scrollY: 200,
scrollCollapse: true,
scroller: true,
initComplete: function () {
this.api().row( 1000 ).scrollTo();
}
} );
} );
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
