Add Custom filter in DataTables AJAX pagination in CodeIgniter 4

Last updated on by

DataTables already comes with a single search box for searching globally on the records.

Sometimes requires to filter records on the basis of available data in the element. For example, filter records according to the city selection from the dropdown or date range selection, etc.

You can create your own custom filters and use them with DataTable.

In this tutorial, I show how you can add custom filter in DataTables AJAX pagination in CodeIgniter 4.

Demo  Download

Contents

  1. Database configuration
  2. Enable CSRF
  3. Create Table
  4. Model
  5. Routes
  6. Controller
  7. View
  8. Demo
  9. Conclusion

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, database.default.DBDriver, database.default.DBPrefix, and database.default.port.
  • Update the configuration and save it.
database.default.hostname = 127.0.0.1
database.default.database = codeigniterdb
database.default.username = root
database.default.password = root
database.default.DBDriver = MySQLi
database.default.DBPrefix =
database.default.port = 3306

2. Enable CSRF

  • Again open .env file.
  • Remove # from the start of the security.tokenName,security.headerName, security.cookieName, security.expires,and security.regenerate.
  • 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 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
  • Open app/Config/Filters.php file.
  • Uncomment 'csrf' in 'before' if commented.
// Always applied before every request
public $globals = [
    'before' => [
       // 'honeypot',
       'csrf',
       // 'invalidchars',
    ],
    'after' => [
       'toolbar',
       // 'honeypot',
       // 'secureheaders',
    ],
];

3. Create Table

  • Create a new table employees using migration.
php spark migrate:create create_employees_table
  • Now, navigate to app/Database/Migrations/ folder from the project root.
  • Find a PHP file that ends with CreateEmployeesTableand open it.
  • Define the table structure in the up() method.
  • Using the down() method delete employees table that calls when undoing migration.
<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

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

     public function down(){
          $this->forge->dropTable('employees');
     }
}
  • Run the migration –
php spark migrate

I added some records to the table.

4. Model

  • Create Employees Model –
php spark make:model Employees
  • Open app/Models/Employees.php file.
  • In $allowedFields Array specify field names – ['emp_name','email','city','gender'] that can be set during insert and update.

Completed Code

<?php

namespace App\Models;

use CodeIgniter\Model;

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

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

5. Routes

  • Open app/Config/Routes.php file.
  • Define 2 routes –
    • / – Display index view.
    • /getEmployees – Return DataTable data.
$routes->get('/', 'EmployeesController::index');
$routes->post('/getEmployees', 'EmployeesController::getEmployees');

6. Controller

  • Create EmployeesController Controller –
php spark make:controller EmployeesController
  • Open app/Controllers/EmployeesController.php file.
  • Import Employees Model.
  • Create 2 methods –
    • index() – Fetch DISTINCT city names from the employees table and assign it to $data['citylists']. Load index view and pass $data.
    • getEmployees() – Using this method return DataTable data.

Read DataTable POST data and assign it to variables. Also reading custom filter data –

// Custom filter
$searchByName = $dtpostData['searchByName'];
$searchByCity = $dtpostData['searchByCity'];

Fetch all records from the employees table with and without filters and assign them to $totalRecords and $totalRecordwithFilter.

Fetch records from employees table with filter and set LIMIT by specifying $rowperpage and $start in findAll().

Loop on the fetched records and initialize $data Array.

Initialize $response Array with draw, iTotalRecords, iTotalDisplayRecords, aaData and token keys.

Return $response Array in JSON format.

Completed Code

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\Employees;

class EmployeesController extends BaseController
{
     public function index(){
          $employees = new Employees();
          $citylists = $employees->select('city')
                      ->distinct()
                      ->orderBy('city')
                      ->findAll();
          $data['citylists'] = $citylists;

          return view('index',$data);
     }
 
     public function getEmployees(){

          $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

          // Custom filter
          $searchByName = $dtpostData['searchByName'];
          $searchByCity = $dtpostData['searchByCity'];

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

          ## Total number of records with filtering
          $searchQuery = $employees->select('id');
          if($searchByName != ''){
                $searchQuery->orLike('emp_name', $searchByName);
          }
          if($searchByCity != ''){
                $searchQuery->orLike('city', $searchByCity);         
          }

          $totalRecordwithFilter = $searchQuery->countAllResults();

          ## Fetch records
          $searchQuery = $employees->select('*');
          if($searchByName != ''){
                $searchQuery->orLike('emp_name', $searchByName);
          }
          if($searchByCity != ''){
                $searchQuery->orLike('city', $searchByCity);
          }
          $records = $searchQuery->orderBy($columnName,$columnSortOrder)
                     ->findAll($rowperpage, $start);

          $data = array();

          foreach($records as $record ){

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

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

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

7. View

  • Create index.php file app/Views/ folder.
  • Include jQuery and DataTable library –
<!-- 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>
  • Stored CSRF token in hidden field.
  • Create 2 elements for custom filter –
    • 1st element is for filtering only employee name.
    • 2nd element is for filtering records by city. Loop on the $citylists to add city <option > in <select >.
  • Create <table id='empTable' > to initialize DataTable.

Script

  • Initialize DataTable on #empTable and store its instance in empTable variable.
  • Set processing and serverSide to true, serverMethod to post, and for disabling the DataTable default search filter set searching to false.
  • Using ajax option send AJAX request to <?=site_url('/getEmployees')?>.
  • Read CSRF token and custom filter element value and assign them to the variables. Append value in data object.
  • Using dataSrc update CSRF token in .txt_csrfname.
  • In columns option specify field names that need to be read from the return response.
  • Define keyup event on #searchByName and change event on #searchByCity.
  • When the event triggers then refresh the datatable by calling draw() on DataTable instance – empTable.draw().

Completed Code

<!DOCTYPE html>
<html>
<head>
    <title>Add custom filter in DataTables AJAX pagination 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() ?>" />

     <!-- Custom Filter -->
     <table>
          <tr>
              <td>
                   <input type='text' id='searchByName' placeholder='Enter name'>
              </td>
              <td>
                   <select id='searchByCity'>
                        <option value=''>-- Select City--</option>
                        <?php 
                        foreach($citylists as $citylist){
                             echo "<option value='".$citylist['city']."' >".$citylist['city']."</option>";
                        }
                        ?>
                   </select>
              </td>
          </tr>
     </table>

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

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

     </table>

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

                               // Custom filter values
                               var name = $('#searchByName').val();
                               var city = $('#searchByCity').val();

                               data.searchByName = name;
                               data.searchByCity = city;
                               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: 'emp_name' },
                       { data: 'email' },
                       { data: 'city' },
                       { data: 'gender' },
                  ]
          });

          // Custom filter
          $('#searchByName').keyup(function(){
               empTable.draw();
          });

          $('#searchByCity').change(function(){
               empTable.draw();
          });
     });
     </script>
</body>
</html>

8. Demo

View Demo

9. Conclusion

In the example, I have disabled the default search box by setting searching to false. You can remove it or set it to true if you want to also use it.

Make sure to call draw() on the DataTable instance from the custom filter event to reload the DataTable data.

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

Leave a Comment

© 2016-2023 Makitweb, All rights reserved