Invoice Script PHP jQuery

Datatables Scroller in CakePHP 3 Using jQuery, Ajax, MySQL and Bootstrap 3

Posted by & filed under Ajax, CakePHP, CSS, JAVASCRIPT, JQUERY, MYSQL, PHP, SQL.

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>
HTML
Copy
CakePHP 3 jQuery Datatables Scroller Demo
Datatables Scroller in CakePHP 3 Using jQuery, Ajax, MySQL and Bootstrap 3

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;
    }
}
PHP
Copy
Download CakePHP3 Datatables Scroller Example Script
Instant Download This Script

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'
                        ])
?>
PHP
Copy

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>
PHP
Copy

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();
    } );
} );
JavaScript
Copy
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;
}
CSS
Copy

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
    } );
} );
JavaScript
Copy

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();
        }
    } );
} );
JavaScript
Copy

Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts

Get Access Now Instantly

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

Get Updates, Scripts & Other Useful Resources to your Email

Join 10,000+ Happy Subscribers on feedburner. Click to Subscribe (We don't send spam)
Every Email Subsciber could have access to download 100+ demo scripts & all future scripts.

Get Instant Script Download Access!

no thanks