Add Custom filter in DataTables AJAX pagination in CodeIgniter 4

Last updated on by

DataTables, a versatile jQuery plugin, provides a built-in search box for global record searches. However, there are instances when a more specific filtering mechanism is needed, such as filtering records based on selected dropdown options or date ranges. Enter custom filters – a tailored solution to refine your data display within a DataTable.

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.

Add Custom filter in DataTables AJAX pagination in CodeIgniter 4

Demo  Download

Table of Content

  1. Configuring Database
  2. Enabling CSRF Protection
  3. Creating a Table Using Migration
  4. Creating an Employees Model
  5. Creating Routes
  6. Creating the Controller
  7. Creating the View and Initializing DataTable
  8. Demo
  9. Conclusion

1. Configuring Database

  • Locate the .env file in your project’s root directory. If dot(.) not added to the start of env file then rename it 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. Enabling CSRF Protection

  • Open the .env file.
  • Uncomment the following lines by removing the # symbol:
security.tokenName = 'csrf_hash_name'
security.headerName = 'X-CSRF-TOKEN'
security.cookieName = 'csrf_cookie_name'
security.expires = 7200
security.regenerate = true

Enable CSRF in Filters.php

  • Open the 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. Creating a Table Using Migration

  • Create a new table employees using migration.
php spark migrate:create create_employees_table
  • Navigate to the app/Database/Migrations/ directory from your project root. Find the PHP file that ends with CreateEmployeesTable and open it. This file will allow you to define your table’s structure within 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. Creating an Employees Model

  • Generating the Employees model using the following command:
php spark make:model Employees
  • Navigate to the app/Models/ directory and open the Employees.php file.
  • In $allowedFields Array specify field names – ['emp_name','email','city','gender'] that can be set during insert and update.
<?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. Creating Routes

  • Open app/Config/Routes.php file.
  • Define 2 routes –
    • The first route '/' is configured to display the index view.
    • The second route '/getEmployees' is set to return DataTable data.
$routes->get('/', 'EmployeesController::index');
$routes->post('/getEmployees', 'EmployeesController::getEmployees');

6. Creating the Controller

  • Generating the EmployeesController using the command:
php spark make:controller EmployeesController
  • Open app/Controllers/EmployeesController.php file.
  • 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.

<?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. Creating the View and Initializing DataTable

  • 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 the DataTable on the element with the ID #empTable and store its instance in the variable empTable.
  • Configure processing and serverSide options as true, set serverMethod to post, and to disable the default search filter of DataTable, set searching to false.
  • Utilize the AJAX option to send an AJAX request to the URL obtained from <?=site_url('/getEmployees')?>.
  • Extract the CSRF token and the values of custom filter elements, then assign these values to respective variables. These values are then appended to the data object sent in the AJAX request.
  • Using the dataSrc callback to update the CSRF token within the .txt_csrfname element.
  • In the columns option, specify the field names that need to be extracted from the response returned by the server.
  • Define a keyup event on the element with the ID #searchByName and a change event on the element with the ID #searchByCity.
  • Upon triggering these events, refresh the DataTable by invoking the draw() method on the DataTable instance, like this: empTable.draw(). This ensures the updated data is presented to the user without requiring a full page reload.
<!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 this example, the default search box has been disabled by setting the searching: false. However, you have the flexibility to enable it by changing the setting to true if it aligns with your requirements.

Remember, when implementing custom filters, it’s essential to call the draw() method on the DataTable instance within the respective filter event handlers. This ensures that the DataTable is reloaded with the updated data, providing a seamless user experience and accurate data representation.

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

Leave a Comment

© 2016-2024 Makitweb, All rights reserved