Reading Time: 9 minutes

2,879 Views

Listing huge data of any application is generally recommended to use Server side data listing. It will list data in per page wise request. With this concept we will see the usage of DataTable to load data.

Inside this article we will see the concept of CakePHP 4 Server Side DataTable Implementation using Ajax. Article contains classified information about server side load datatable. You can learn add use the same concept in your application with your own data.

Server side datatable provides several benefits like loading a huge amount of data without any issue of page load, flexible search into list, etc.

Learn More –

Let’s get started.

CakePHP 4 Installation

To create a CakePHP project, run this command into your shell or terminal. Make sure composer should be installed in your system.

$ composer create-project --prefer-dist cakephp/app:~4.0 mycakephp

Above command will creates a project with the name called mycakephp.

Create Database

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE mydatabase;

Successfully, we have created a database.

Database Connection

Open app_local.php file from /config folder. Search for Datasources. Go to default array of it.

You can add your connection details here to connect with your database. It will be like this –

//...

'Datasources' => [
        'default' => [
            'host' => 'localhost',
            /*
             * CakePHP will use the default DB port based on the driver selected
             * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
             * the following line and set the port accordingly
             */
            //'port' => 'non_standard_port_number',

            'username' => 'root',
            'password' => 'sample@123',

            'database' => 'mydatabase',
            /*
             * If not using the default 'public' schema with the PostgreSQL driver
             * set it here.
             */
            //'schema' => 'myapp',

            /*
             * You can use a DSN string to set the entire configuration
             */
            'url' => env('DATABASE_URL', null),
        ],
  
     //...

//...

You can pass host, username, password and database.

Successfully, you are now connected with the database.

Create Migration

Open project into terminal and run this command to create migration file.

$ bin/cake bake migration CreateEmployees

It will create 20220330143909_CreateEmployees.php file inside /config/Migrations folder. Open migration file and write this following code into it.

The code is all about for the schema of employees table.

<?php

declare(strict_types=1);

use Migrations\AbstractMigration;

class CreateEmployees extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('employees');
        $table->addColumn("name", "string", [
            "limit" => 50,
            "null" => false
        ]);
        $table->addColumn("email", "string", [
            "limit" => 50,
            "null" => false
        ]);
        $table->addColumn("mobile", "string", [
            "limit" => 20,
            "null" => false
        ]);
        $table->create();
    }
}

Run Migration

Back to terminal and run this command.

$ bin/cake migrations migrate

It will create employees table inside database.

Create Test Data

Here, we have a list of fake data which you needs to be inserted into database table employees. Copy this mysql query and execute it.

Right now, we have only few rows. You can insert as many rows as you want.

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`id`, `name`, `email`, `mobile`) VALUES
(1, 'Sanjay Kumar', 'sanjay@gmail.com', '1234567895'),
(2, 'Ashish Kumar', 'ashish@gmail.com', '7412589635'),
(3, 'Vijay Kumar', 'vijay@gmail.com', '9632587410'),
(4, 'Dhananjay Negi', 'dj@gmail.com', '8529637410'),
(5, 'Ajit Kumar', 'ajit@gmail.com', '9658741230'),
(6, 'Suman Yadav', 'suman@gmail.com', '2635897410'),
(7, 'Mandeep Singh', 'mandeep@gmail.com', '8526937410'),
(8, 'Sourav Sukhla', 'sukhla.sourav@gmail.com', '8974563210'),
(9, 'Raman Singh', 'raman001@gmail.com', '8596347158'),
(10, 'Suneel Rana', 'suneel@gmail.com', '1547896304'),
(11, 'Vikram Singh', 'vikram@gmail.com', '8526934710');

After insertion of test data, table will look like this.

Create Controller

Open project into terminal and run this command into it.

$ bin/cake bake controller Site --no-actions

It will create SiteController.php file inside /src/Controller folder. Open controller file and write this code into it.

<?php

declare(strict_types=1);

namespace App\Controller;

use Cake\Datasource\ConnectionManager;

class SiteController extends AppController
{
    public $db;

    public function initialize(): void
    {
        parent::initialize();
        $this->db = ConnectionManager::get("default");
    }

    public function employeesList()
    {
        // for the frontend layout
    }

    public function loadEmployees()
    {
        if ($this->request->is("ajax")) {
            // Used when ajax loads data
            $params['draw'] = $_REQUEST['draw'];
            $start = $_REQUEST['start'];
            $length = $_REQUEST['length'];
            /* If we pass any extra data in request from ajax */
            //$value1 = isset($_REQUEST['key1'])?$_REQUEST['key1']:"";

            /* Value we will get from typing in search */
            $search_value = $_REQUEST['search']['value'];

            if (!empty($search_value)) {
                // If we have value in search, searching by id, name, email, mobile

                // count all data
                $total_count = $this->db->execute("SELECT * from employees WHERE id like '%" . $search_value . "%' OR name like '%" . $search_value . "%' OR email like '%" . $search_value . "%' OR mobile like '%" . $search_value . "%'")->fetchAll('assoc');

                $data = $this->db->execute("SELECT * from employees WHERE id like '%" . $search_value . "%' OR name like '%" . $search_value . "%' OR email like '%" . $search_value . "%' OR mobile like '%" . $search_value . "%' limit $start, $length")->fetchAll('assoc');
            } else {
                // count all data
                $total_count = $this->db->execute("SELECT * from employees")->fetchAll('assoc');

                // get per page data
                $data = $this->db->execute("SELECT * from employees limit $start, $length")->fetchAll('assoc');
            }

            $json_data = array(
                "draw" => intval($params['draw']),
                "recordsTotal" => count($total_count),
                "recordsFiltered" => count($total_count),
                "data" => $data   // total data array
            );

            echo json_encode($json_data);
            die;
        }
    }
}

Inside above code, we have two methods –

employeesList() method for frontend layout of datatable.
loadEmployees() method responsible to load data into datatable via ajax request.

Create Template

Create Site folder inside /templates folder. Next, needs to create employees_list.php file inside /templates/Site folder.

Open employees_list.php file and write this following code into it. This will give the frontend layout for datatable.

<!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>CakePHP 4 Server Side DataTable Tutorial - Online Web Tutor</title>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css"/>
</head>

<body>
  <div class="container">

    <br/>
    <h2>CakePHP 4 Server Side DataTable Tutorial</h2>
    <br/>
    <table class="table table-striped" id="tbl-employees-data">
      <thead>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Email</th>
          <th>Mobile</th>
        </tr>
      </thead>
      <tbody></tbody>
    </table>
  </div>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
  
  <script>

    $(document).ready( function () {

        $('#tbl-employees-data').DataTable({
          lengthMenu: [[ 10, 30, -1], [ 10, 30, "All"]], // page length options
          bProcessing: true,
          serverSide: true,
          scrollY: "400px",
          scrollCollapse: true,
          ajax: {
            url: "/ajax-load-data", // json datasource
            type: "post",
            data: {
              // key1: value1 - in case if we want send data with request
            }
          },
          columns: [
            { data: "id" },
            { data: "name" },
            { data: "email" },
            { data: "mobile" }
          ],
          columnDefs: [
            { orderable: false, targets: [0, 1, 2, 3] }
          ],
          bFilter: true, // to display datatable search
        });
    });
  </script>
</body>

</html>

Disable CSRF Token

When we submit a cakephp form, it needs a CSRF token should be submitted with form submission request.

We are not interested to send CSRF token with form data. To disable it, Open Application.php from /src folder.

Remove these lines of code from middleware() method.

->add(new CsrfProtectionMiddleware([
    'httponly' => true,
]))

Add Route

Open routes.php file from /config folder. Add these routes into it.

//...

$routes->connect(
    '/employees-list',
    ['controller' => 'Site', 'action' => 'employeesList']
);

$routes->connect(
    '/ajax-load-data',
    ['controller' => 'Site', 'action' => 'loadEmployees']
);

//...

Application Testing

Open terminal and run this command to start development server.

$ bin/cake server

URL: http://localhost:8765/employees-list

We hope this article helped you to learn about CakePHP 4 Server Side DataTable Implementation Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.