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.
Contents
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
, anddatabase.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
,andsecurity.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
CreateEmployeesTable
and open it. - Define the table structure in the
up()
method. - Using the
down()
method deleteemployees
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']
. Loadindex
view and pass$data
. - getEmployees() – Using this method return DataTable data.
- index() – Fetch DISTINCT city names from the
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
fileapp/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 inempTable
variable. - Set
processing
andserverSide
totrue
,serverMethod
topost
, and for disabling the DataTable default search filter setsearching
tofalse
. - 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
andchange
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
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.