DataTables AJAX pagination with Custom filter in CodeIgniter 3

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 allow 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 3.

Contents

1. Table structure
2. Configuration
3. Model
4. Controller
5. View
6. Demo
7. Conclusion

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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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/ folder.

Create 2 methods –

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

Read POST DataTable parameters and assign in variables.

Here, read POST custom filter values as specified in data while appending in 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 is 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/ folder.

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

<?php
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/ folder.

HTML

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'>.

Script

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 a 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 3</title>

    <!-- Datatable CSS -->
    <link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

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

    <!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></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>
</html>

6. Demo

View Demo

7. Conclusion

By following the tutorial example, you can add more search filter elements in the DataTable pagination. For this, you need to bind an event and call draw() on the DataTable object.

Pass filter data using 'data' option.