How to Create DataTables AJAX Pagination with Search and Sort in CodeIgniter 4

 

DataTables is a widely used jQuery plugin for pagination. After initialization, it adds a search box and column sort on the header column.

It allows adding pagination with and without AJAX.

In this tutorial, I show how you can implement DataTables AJAX pagination with search and sort in the CodeIgniter 4 project.

Demo Download

 1. Database configuration

• Open .env file which is available at the project root.

NOTE – If dot (.) not added at the start then rename the file to .env.

• Remove # from start of database.default.hostname, database.default.database, database.default.username, database.default.password, and database.default.DBDriver.
• Update the configuration and save it.

database.default.hostname = 127.0.0.1
database.default.database = testdb
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi

---

2. Enable CSRF

• Again open .env file.
• Remove # from the start of the security.tokenName,security.headerName, security.cookieName, security.expires, security.regenerate, and security.samesite.
• I update the security.tokenName value with 'csrf_hash_name'. With this name read CSRF hash. You can update it with any other value.
• If you don’t want to regenerate CSRF hash after each AJAX request then set security.regenerate = false.

security.tokenName = 'csrf_hash_name' 
security.headerName = 'X-CSRF-TOKEN' 
security.cookieName = 'csrf_cookie_name' 
security.expires = 7200 
security.regenerate = true 
security.redirect = true 
security.samesite = 'Lax'

• Open app/Config/Filters.php file.
• Uncomment in 'csrf' in 'before' if commented.

// Always applied before every request
public $globals = [
    'before' => [
       //'honeypot'
       'csrf',
    ],
    'after' => [
       'toolbar',
       //'honeypot'
    ],
];

---

3. Create Table

• Create a new table users using migration.

php spark migrate:create create_users_table

• Now, navigate to app/Database/Migrations/ folder from the project root.
• Find a PHP file that ends with create_users_table and open it.
• Define the table structure in the up() method.
• Using the down() method delete users table which calls when undoing migration.

<?php namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up() {
       $this->forge->addField([
          'id' => [
              'type' => 'INT',
              'constraint' => 5,
              'unsigned' => true,
              'auto_increment' => true,
          ],
          'name' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'email' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'city' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
       ]);
       $this->forge->addKey('id', true);
       $this->forge->createTable('users');
    }

    //--------------------------------------------------------------------

    public function down() {
       $this->forge->dropTable('users');
    }
}

• Run the migration –

php spark migrate

---

4. Download DataTables

• Download the Datatables plugin from here.
• Extract it in the public/ folder at the root.
• I am using CDN in the example –

<!-- Datatable CSS -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.24/datatables.min.css"/>

<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/v/dt/dt-1.10.24/datatables.min.js"></script>

---

5. Model

• Create Users Model –

php spark make:model Users

• Open app/Models/Users.php file.
• In $allowedFields Array specify field names – ['name','email','city'] that can be set during insert and update.

Completed Code

<?php

namespace App\Models;

use CodeIgniter\Model;

class Users extends Model
{
     protected $DBGroup = 'default';
     protected $table = 'users';
     protected $primaryKey = 'id';
     protected $useAutoIncrement = true;
     protected $insertID = 0;
     protected $returnType = 'array';
     protected $useSoftDeletes = false;
     protected $protectFields = true;
     protected $allowedFields = ['name','email','city'];

     // Dates
     protected $useTimestamps = false;
     protected $dateFormat = 'datetime';
     protected $createdField = 'created_at';
     protected $updatedField = 'updated_at';
     protected $deletedField = 'deleted_at';

     // Validation
     protected $validationRules = [];
     protected $validationMessages = [];
     protected $skipValidation = false;
     protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert = [];
    protected $afterInsert = [];
    protected $beforeUpdate = [];
    protected $afterUpdate = [];
    protected $beforeFind = [];
    protected $afterFind = [];
    protected $beforeDelete = [];
    protected $afterDelete = [];
}

---

6. Route

• Open app/Config/Routes.php file.
• Define 2 routes –
  • /
  • users/getUsers – It is used to load DataTable data.

Completed Code

$routes->get('/', 'UsersController::index');
$routes->post('users/getUsers', 'UsersController::getUsers');

---

7. Controller

• Create UsersController Controller –

php spark make:controller UsersController

• Open app/Controllers/UsersController.php file.
• Import Users Model.
• Create 2 methods –
  • index() – Load index view.
  • getUsers() – This method is use to handle DataTables AJAX requests.

Read POST values and assign to $postData variable. Assign datatable data to $dtpostData variable.

Read datatable post values from $dtpostData and assign them to the variables.

Count the number of records with and without a search filter from the 'users' table and assign to the $totalRecords and $totalRecordwithFilter variable.

Fetch records from the 'users' table where specify search filter, order by, and limit.

Loop on the fetched data and initialize $data Array with keys that are specified in the 'columns' option while initializing dataTable.

Initialize $response Array with required values. Here, also specify the 'token' key to store the new CSRF token hash.

Return $response Array in JSON format.

Completed Code

<?php namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\Users;

class UsersController extends BaseController{

   public function index(){
     return view('index');
   }

   public function getUsers(){

     $request = service('request');
     $postData = $request->getPost();
     $dtpostData = $postData['data'];
     $response = array();

     ## Read value
     $draw = $dtpostData['draw'];
     $start = $dtpostData['start'];
     $rowperpage = $dtpostData['length']; // Rows display per page
     $columnIndex = $dtpostData['order'][0]['column']; // Column index
     $columnName = $dtpostData['columns'][$columnIndex]['data']; // Column name
     $columnSortOrder = $dtpostData['order'][0]['dir']; // asc or desc
     $searchValue = $dtpostData['search']['value']; // Search value

     ## Total number of records without filtering
     $users = new Users();
     $totalRecords = $users->select('id')
                     ->countAllResults();

     ## Total number of records with filtering
     $totalRecordwithFilter = $users->select('id')
            ->orLike('name', $searchValue)
            ->orLike('email', $searchValue)
            ->orLike('city', $searchValue)
            ->countAllResults();

     ## Fetch records
     $records = $users->select('*')
            ->orLike('name', $searchValue)
            ->orLike('email', $searchValue)
            ->orLike('city', $searchValue)
            ->orderBy($columnName,$columnSortOrder)
            ->findAll($rowperpage, $start);

     $data = array();

     foreach($records as $record ){

        $data[] = array( 
           "id"=>$record['id'],
           "name"=>$record['name'],
           "email"=>$record['email'],
           "city"=>$record['city']
        ); 
     }

     ## Response
     $response = array(
        "draw" => intval($draw),
        "iTotalRecords" => $totalRecords,
        "iTotalDisplayRecords" => $totalRecordwithFilter,
        "aaData" => $data,
        "token" => csrf_hash() // New token hash
     );

     return $this->response->setJSON($response);
   }
}

---

8. View

Create index.php file in app/Views/.

Create a hidden element to store CSRF token name specified in .env file in the name attribute and store CSRF hash in the value attribute.

<input type="hidden" class="txt_csrfname" name="<?= csrf_token() ?>" value="<?= csrf_hash() ?>" />

Create a <table id='userTable' > to initialize dataTables.

Script – 

Initialize dataTables on #userTable. Set options – 'processing': true, 'serverSide': true, 'serverMethod': 'post'.

Send AJAX request to <?=site_url('users/getUsers')?>. With 'data' option pass CSRF token with dataTable data.

With 'dataSrc' option handle datatable return response. Update token hash and return data.addData.

With 'columns' option specifies key names that need to read from the AJAX response.

Completed Code

<!DOCTYPE html>
<html>
<head>
   <title>DataTables AJAX Pagination with Search and Sort in CodeIgniter 4</title>

   <meta name="viewport" content="width=device-width, initial-scale=1.0">

   <!-- Datatable CSS -->
   <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.24/datatables.min.css"/>

   <!-- jQuery Library -->
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

   <!-- Datatable JS -->
   <script src="https://cdn.datatables.net/v/dt/dt-1.10.24/datatables.min.js"></script>

</head>
<body>

   <!-- CSRF token --> 
   <input type="hidden" class="txt_csrfname" name="<?= csrf_token() ?>" value="<?= csrf_hash() ?>" />

   <!-- Table -->
   <table id='userTable' class='display dataTable'>

     <thead>
       <tr>
         <th>ID</th>
         <th>Name</th>
         <th>Email</th>
         <th>City</th>
       </tr>
     </thead>

   </table>

   <!-- Script -->
   <script type="text/javascript">
   $(document).ready(function(){
      $('#userTable').DataTable({
         'processing': true,
         'serverSide': true,
         'serverMethod': 'post',
         'ajax': {
            'url':"<?=site_url('users/getUsers')?>",
            'data': function(data){
               // CSRF Hash
               var csrfName = $('.txt_csrfname').attr('name'); // CSRF Token name
               var csrfHash = $('.txt_csrfname').val(); // CSRF hash

               return {
                  data: data,
                  [csrfName]: csrfHash // CSRF Token
               };
            },
            dataSrc: function(data){

              // Update token hash
              $('.txt_csrfname').val(data.token);

              // Datatable data
              return data.aaData;
            }
         },
         'columns': [
            { data: 'id' },
            { data: 'name' },
            { data: 'email' },
            { data: 'city' },
         ]
      });
   });
   </script>
</body>
</html>

---

9. Run & Demo

• Navigate to the project using Command Prompt if you are on Windows or terminal if you are on Mac or Linux, and
• Execute “php spark serve” command.

php spark serve

• Run http://localhost:8080 in the web browser.

View Demo

---

10. Conclusion

If the CSRF token is not enabled in your project then not require to pass the CSRF token with the 'data' option and remove the 'dataSrc' option in 'ajax'. Also, remove the 'token' key from datatable response Array in the Controller.

Use 'data' option in 'ajax' to send additional data.

Share this post