Please support us by disabling ad blocker for smarttutorials.net ...

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

Posted by & 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.

 

 

CakePHP 3 Ajax Pagination Using Datatable, jQuery, MySQL and 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.

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

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");
            }
        }
    } );
} );

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] muni[at]smarttutorials.net

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!