DataTables AJAX pagination with Custom filter in CodeIgniter

DataTables by default comes with a search filter that can be used to search on all fields or specific fields and display records.

Sometimes, require to add the filter for the particular field only like – adding date range filter, country filter, etc.

DataTables allows for passing data that can read at the server-side script.

In this tutorial, I show how you can add a custom search filter and get records using DataTables in CodeIgniter.

1. Table structure

In this example, I am using users table and added some records –

CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(80) NOT NULL, `username` varchar(80) NOT NULL, `gender` varchar(10) NOT NULL, `email` varchar(80) NOT NULL, `city` varchar(80) NOT NULL

2. Configuration

Navigate to application/config/database.php and define the Database connection.

$db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'root', // Username 'password' => '', // Password 'database' => 'tutorial', // Database name 'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => FALSE, 'db_debug' => (ENVIRONMENT !== 'production'), 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE

Default controller

Open application/config/routes.php and edit default_controller value to Users.

$route['default_controller'] = 'Users';

Load Database

To access the MySQL database require loading database library.

Open application/config/autoload.php and add the database in libraries array().

$autoload['libraries'] = array("database");

3. Model

Create User_model.php file in application/models/ directory.

Create 2 methods –

  • getUsers() – This method takes a single parameter and uses to return formated DataTable data.

Read POST DataTable parameters and assign in variables.

Here, read POST custom filter values as specified in data while appending in the JavaScript.

Create $search_arr Array variable to assign search query if the search value is available.

Implode $search_arr Array and assign to $searchQuery if it not empty.

Count total records with and without the filter.

Fetch records where pass $searchQuery in WHERE clause, ORDER BY, and LIMIT.

Loop on the fetched records and initialize $data Array with associative Array which has a similar key as defined in columns option in dataTable() method.

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

Return $response Array.

  • getCities() – Get cities list and assign in $data Array and return it.

Completed Code

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); class User_model extends CI_Model { // Get DataTable data function getUsers($postData=null){ $response = array(); ## Read value $draw = $postData['draw']; $start = $postData['start']; $rowperpage = $postData['length']; // Rows display per page $columnIndex = $postData['order'][0]['column']; // Column index $columnName = $postData['columns'][$columnIndex]['data']; // Column name $columnSortOrder = $postData['order'][0]['dir']; // asc or desc $searchValue = $postData['search']['value']; // Search value // Custom search filter $searchCity = $postData['searchCity']; $searchGender = $postData['searchGender']; $searchName = $postData['searchName']; ## Search $search_arr = array(); $searchQuery = ""; if($searchValue != ''){ $search_arr[] = " (name like '%".$searchValue."%' or email like '%".$searchValue."%' or city like'%".$searchValue."%' ) "; } if($searchCity != ''){ $search_arr[] = " city='".$searchCity."' "; } if($searchGender != ''){ $search_arr[] = " gender='".$searchGender."' "; } if($searchName != ''){ $search_arr[] = " name like '%".$searchName."%' "; } if(count($search_arr) > 0){ $searchQuery = implode(" and ",$search_arr); } ## Total number of records without filtering $this->db->select('count(*) as allcount'); $records = $this->db->get('users')->result(); $totalRecords = $records[0]->allcount; ## Total number of record with filtering $this->db->select('count(*) as allcount'); if($searchQuery != '') $this->db->where($searchQuery); $records = $this->db->get('users')->result(); $totalRecordwithFilter = $records[0]->allcount; ## Fetch records $this->db->select('*'); if($searchQuery != '') $this->db->where($searchQuery); $this->db->order_by($columnName, $columnSortOrder); $this->db->limit($rowperpage, $start); $records = $this->db->get('users')->result(); $data = array(); foreach($records as $record ){ $data[] = array( "username"=>$record->username, "name"=>$record->name, "email"=>$record->email, "gender"=>$record->gender, "city"=>$record->city ); } ## Response $response = array( "draw" => intval($draw), "iTotalRecords" => $totalRecords, "iTotalDisplayRecords" => $totalRecordwithFilter, "aaData" => $data ); return $response; } // Get cities array public function getCities(){ ## Fetch records $this->db->distinct(); $this->db->select('city'); $this->db->order_by('city','asc'); $records = $this->db->get('users')->result(); $data = array(); foreach($records as $record ){ $data[] = $record->city; } return $data; } }

4. Controller

Create Users.php file in application/controllers/ directory.

Here, create 3 methods –

  • __construct() – Load url helper and User_model Model.
  • index() – Get cities list Array by calling getCities() method and assign in $cities. Load user_view and pass $data Array.
  • userList() – This method use to handle DataTable AJAX request. Assign POST data to $postData.

Pass $postData to getUsers() method to get user list Array and return response in JSON format.

Completed Code

defined('BASEPATH') OR exit('No direct script access allowed'); class Users extends CI_Controller { public function __construct(){ parent::__construct(); $this->load->helper('url'); // Load model $this->load->model('User_model'); } public function index(){ $cities = $this->User_model->getCities(); $data['cities'] = $cities; // load view $this->load->view('user_view',$data); } public function userList(){ // POST data $postData = $this->input->post(); // Get data $data = $this->User_model->getUsers($postData); echo json_encode($data); } }

5. View

Create user_view.php file in application/views/ directory.


Include DataTables and jQuery library at the <head > section.

Add custom filter elements in <div>.

Two <select > elements for city and gender search and a textbox for a name search.

Create <table id='userTable'>.


Initialize DataTable on #userTable selector and assign in userDataTable variable.

Send AJAX request to <?=base_url()?>index.php/Users/userList and with data option read custom search fields values – #sel_city#sel_gender, #searchName and append in the data object.

In the columns options specify the key names which get read on successful callback.

Define change event on the #sel_city, #sel_gender and keyup event on #searchName elements.

Call userDataTable.draw(); to redraw the DataTable.

Completed Code

<!DOCTYPE html>
<html> <head> <title>DataTables AJAX pagination with Custom filter in CodeIgniter</title> <!-- Datatable CSS --> <link href='//' rel='stylesheet' type='text/css'> <!-- jQuery Library --> <script src=""></script> <!-- Datatable JS -->
<script src="//"></script> </head> <body> <!-- Search filter --> <div> <!-- City --> <select id='sel_city'> <option value=''>-- Select city --</option> <?php foreach($cities as $city){ echo "<option value='".$city."'>".$city."</option>"; } ?> </select> <!-- Gender --> <select id='sel_gender'> <option value=''>-- Select Gender --</option> <option value='male'>Male</option> <option value='female'>Female</option> </select> <!-- Name --> <input type="text" id="searchName" placeholder="Search Name"> </div> <!-- Table --> <table id='userTable' class='display dataTable'> <thead> <tr> <th>Username</th> <th>Name</th> <th>Email</th> <th>Gender</th> <th>City</th> </tr> </thead> </table> <!-- Script --> <script type="text/javascript"> $(document).ready(function(){ var userDataTable = $('#userTable').DataTable({ 'processing': true, 'serverSide': true, 'serverMethod': 'post', //'searching': false, // Remove default Search Control 'ajax': { 'url':'<?=base_url()?>index.php/Users/userList', 'data': function(data){ data.searchCity = $('#sel_city').val(); data.searchGender = $('#sel_gender').val(); data.searchName = $('#searchName').val(); } }, 'columns': [ { data: 'username' }, { data: 'name' }, { data: 'email' }, { data: 'gender' }, { data: 'city' }, ] }); $('#sel_city,#sel_gender').change(function(){ userDataTable.draw(); }); $('#searchName').keyup(function(){ userDataTable.draw(); }); }); </script> </body>

6. Conclusion

Add your search element and bind an event. Call draw() method on DataTable object.

Pass your data using 'data' option and handle it to filter records.

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

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

Related Posts