CakePHP 3 Ajax Pagination with Search Using Datatables, jQuery, MySQL and Bootstrap 3

Posted 3:18 pm by muni & filed under Ajax, CakePHP, CSS, JAVASCRIPT, JQUERY, MYSQL, PHP, SQL.

In this tutorial we are going to see how to implement CakePHP 3 Ajax Pagination with Search Using Datatables, jQuery, MySQL and Bootstrap 3. This is follow up tutorial of the Integrate DataTables in CakePHP 3 with Ajax Pagination using jQuery, Bootstrap 3. By default DataTables supports global search with single input element on the top right side. When user enters something in the textbox, then we have make search condition with possible table columns.

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

1. Integrate Datatables in CakePHP 3 with Static JOSN using jQuery, Bootstrap 3
2. CakePHP 3 Ajax Pagination Using DataTables, jQuery, MySQL and Bootstrap 3

Step 1: Create CakePHP 3 Controller and Actions for jQuery DataTables Integration:

To integrate ajax pagination with global search of DataTables in CakePHP 3, I have added two public functions (search & ajaxManageEmployeesSearch) in employees controller.

<?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 search()
    {
        $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 ajaxManageEmployeesSearch(){
        $this->autoRender = false;
        $requestData= $this->request->data;
        extract($this->request->session()->read('query'));

        $cond = "";
        if( isset($requestData['search']['value']) && !empty( $requestData['search']['value'] ) ){
            $search = $requestData['search']['value'];
            $cond.=" AND ( Employees.id LIKE '".$search."%' OR  Employees.first_name LIKE '".$search."%'
             OR Employees.last_name LIKE '".$search."%' OR Employees.email LIKE '".$search."%'
             OR Designations.name LIKE '".$search."%' OR Departments.dept_name LIKE '".$search."%'
            )";
        }


        $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= [];
            $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;
    }
}

Please refer following tutorial to know more about what is happening in ajax functions.

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

Step 2: Implement DataTables Global Search in CakePHP 3 using Ajax and jQuery :

Whenever user types something in the DataTables global search box on the top right corner, then by default DataTables makes ajax call to the mentioned ajax url with searched values like in the below image.

Then if the search is set and not empty, then make search condition like this…

if( isset($requestData['search']['value']) && !empty( $requestData['search']['value'] ) ){
    $search = $requestData['search']['value'];
    $cond.=" AND ( Employees.id LIKE '".$search."%' OR  Employees.first_name LIKE '".$search."%'
     OR Employees.last_name LIKE '".$search."%' OR Employees.email LIKE '".$search."%'
     OR Designations.name LIKE '".$search."%' OR Departments.dept_name LIKE '".$search."%'
    )";
}

Step 3: Add Required Assets (jQuery, DataTables) for DataTables:

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

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

Next We need to create view file and add the DataTables 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 5: Initialize DataTables Using jQuery DataTables with Ajax Datasource:

Initialize DataTables Using jQuery DataTables 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_search",
            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");
            }
        }
    } );
} );