Fixed Header Datatables Scroller With Ajax Pagination Search Filter in Cakephp3 Using jQuery & Bootstrap3
In this tutorial we are going to see how to implement Fixed Header Datatables Scroller With Ajax Pagination, Search Filter in Cakephp3 Using jQuery, MySQL & Bootstrap3. This is follow up tutorial of the Datatables Scroller in CakePHP 3 Using jQuery, Ajax, MySQL and Bootstrap 3.
When user scrolls up to see long list of data in the Datatables, then Datatables header will get hidden. So if user forget the list of column names, then user has to back and forth to see Datatables header column names. So this FixedHeader is an extension for DataTables that provides the ability to show a header and / or footer that is sticky.
Note: FixedHeader configuration object requires the FixedHeader extension for DataTables.
<link rel=stylesheet href="fixedHeader.bootstrap.min.css">
<script src="dataTables.fixedHeader.js"></script>
- 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
- Responsive Datatables with Ajax Pagination, Search Filter in CakePHP 3 Using Bootstrap 3, MySQL and jQuery
- Datatables Scroller in CakePHP 3 Using jQuery, Ajax, MySQL and Bootstrap 3
- Fixed Header Datatables Scroller With Ajax Pagination Search Filter in Cakephp3 Using jQuery & Bootstrap3
- Datatables Edit/Delete/Print Options with Search Filter in CakePHP 3 Using jQuery, MySQL, Bootstrap 3
$('#employees-grid').DataTable( {
fixedHeader: true
} );
Step 1: Create CakePHP 3 Controller and Actions for FixedHeader Datatables:
First create two controller actions in Employees controller. One for normal view rendering, another one ajax data provider.
<?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 fixedHeader()
{
$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 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 FixedHeader DataTables in CakePHP 3:
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',
'datatables-extensions/fixedHeader.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.fixedHeader',
])
?>
Note: FixedHeader configuration object requires the FixedHeader extension for DataTables.
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 FixedHeader Datatables in CakePHP 3 :
Next create view file (fixed_header.ctp ) in the following location src/Template/Employees directory. Add the following same HTML markup in it.
<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>
<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>
</div>
</div>
</div>
Step 4: Initialize FixedHeader Datatables in CakePHP 3 Using jQuery :
Next we are initializing each type of fixed header Datatables using jQuery by selecting table id.
$(document).ready(function() {
var dataTable = $('#employees-grid').DataTable( {
fixedHeader: {
header: true,
headerOffset: 50,
footer: true
},
responsive: {
details: {
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");
}
}
} );
//new $.fn.dataTable.FixedHeader( dataTable );
//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();
} );
} );
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
