DataTables AJAX Pagination with Search and Sort in CodeIgniter 4

Pagination is a widely used method to enhance user experience when displaying large amounts of data on a web page.

With the use of DataTables jQuery plugin you can easily implement pagination in your CodeIgniter 4 project. It adds different features along with pagination like – sorting, searching, and changing the number of rows view per page.

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. I am using CSRF token in the example, if you don’t know how to enable it then you can view this tutorial.

Demo Download

Create a Table
Download DataTables
Creating the Model
Create Routes
Creating the controller
Creating the view
Demo
Conclusion

1. Create a 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

2. 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/1.13.1/css/jquery.dataTables.min.css"/>

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

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js"></script>

3. Creating the 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 = [];
}

4. Create Routes

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

5. Creating the 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);
   }
}

6. Creating the View

Create index.php file in app/Views/.

Create a hidden element with name and value attributes to store the CSRF token name from the .env file and the CSRF hash.

<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/1.13.1/css/jquery.dataTables.min.css"/>

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

   <!-- Datatable JS -->
   <script src="https://cdn.datatables.net/1.13.1/js/jquery.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>

7. Demo

View Demo

8. Conclusion

Remove the "dataSrc" option in "ajax" and do not require the CSRF token to be passed with the "data" option if the CSRF token is not enabled in your project. Remove the "token" key from the controller datatable response array as well.

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

You can view this tutorial if you want how to add custom filter in DataTables in CodeIgniter 4.

If you found this tutorial helpful then don't forget to share.

Contents

1. Create a Table

2. Download DataTables

3. Creating the Model

4. Create Routes

5. Creating the Controller

6. Creating the View

7. Demo

8. Conclusion