CakePHP 3 Ajax Pagination Using Datatable, jQuery, MySQL and Bootstrap 3

Posted 3:27 am by muni & filed under Ajax, CakePHP, JAVASCRIPT, JQUERY, MYSQL, PHP, SQL.

In this tutorial we are going to see how to implement CakePHP 3 Ajax Pagination Using Datatable, jQuery, MySQL and Bootstrap 3. This is follow up tutorial of the Integrate Datatable in CakePHP 3 with Static JOSN using jQuery, Bootstrap 3.

Note : I have implemented whole tutorial by joining multiple mysql tables (employees, departments and designations) on the server side. Because It difficult to find the datatable example with multiple mysql table join.

• Integrate Datatable in CakePHP 3 with Static JOSN using jQuery, Bootstrap 3

Step 1: Create Controller and Actions in CakePHP 3:

I have created EmployeesController.php file in src/Controller folder, and added employees and ajaxManageEmployees public functions in it. We can implement this CakePHP 3 Ajax Pagination Using Datatable, jQuery, MySQL and Bootstrap 3 with single public function itself. But I am using two functions one for rendering normal view and another 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 employees()
    {
        $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 ajaxManageEmployees(){

        $this->autoRender = false;
        $requestData= $this->request->data;
    	extract($this->request->session()->read('query'));


        $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',
        );


        $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;
    }

Where in the employees() function we have kept query for getting employee details and count, and written into CakePHP 3 session.

Next from ajaxManageEmployees() function we only need data, so we are disabling normal rendering of view byy setting autoRender property as false.

$this->autoRender = false;

Next I am getting posted data via ajax using CakePHP 3 Request object, and assigning count and detail query to $count and $detail variable using extract method.

$requestData= $this->request->data;
extract($this->request->session()->read('query'));

Next form the $columns array, which will be helpful for creating filter and order by query. Also using ConnectionManager I am using count query and saved the result in $totalData variable.

$conn = ConnectionManager::get('default');
$results = $conn->execute($count)->fetchAll('assoc');
$totalData = isset($results[0]['count']) ? $results[0]['count'] : 0;

Next I am getting the orderby column and limit start and end length from the posted data. By concating those with detail query, and I am running the detail query.

$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');

Finally create the response array with following key and value pairs in it, and returning JSON encoded data back to the front end.

$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;

Step 2: Add Required Assets (jQuery, DataTables) for DataTable:

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 Datatable in CakePHP 3 using static JSON data, jQuery and Bootstrap 3 tutorial to create CakePHP 3 layout file.

Step 3: Create CakePHP 3 View File and Add Datatable HTML Markup In It:

Next We need to create view file and add the datatable 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>
                    </tr>
                </thead>
            </table>
        </div>
    </div>
</div>

Step 4: Initialize DataTable Using jQuery Datatable with Ajax Datasource:

First I am disabling the global datatable search using following script.

$.extend( true, $.fn.dataTable.defaults, {
    searching: false
} );

Note : Next tutorial I am going to implement jQuery global search.

Next Initialize DataTable Using jQuery Datatable with Ajax Datasource like this.

$(document).ready(function() {
    var dataTable = $('#employees-grid').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax":{
            url :base_path+"employees/ajax_manage_employees",
            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");
            }
        }
    } );
} );