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

DataTables AJAX pagination with Custom filter 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/ 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

<?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/ directory.

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

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.

21 thoughts on “DataTables AJAX pagination with Custom filter in CodeIgniter 3”

  1. Hi Yogesh,
    Ref: DataTables AJAX pagination with Custom filter in CodeIgniter
    For most part it seems it reads the database table “users” and dropdown text fields are populated like “Select City” & “Select Gender”.
    But the table content doesn’t appear except the headers.
    Any suggestions?
    Do I need any modification?

    Cheers,

    Atour

    Reply
    • Hi Atour,
      Have you added city and gender dropdown for filter and loading content on change? In this case, have you checked the header what data is passing using the browser console network tab?

      Reply
    • Hi Fahrizal,
      Add a new column in table and define in columns option in DataTable initialization. Update the Model file method from which return DataTable response like this –

      $data[] = array( 
               "username"=>$record->username,
               "name"=>$record->name,
               "email"=>$record->email,
               "gender"=>$record->gender,
               "city"=>$record->city,
               "action"=>"<button id='btndetail_".$record->id."'>Detail</button>"
             );

      Added button in the action key.

      Reply
  2. i try many error, how fix ?

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: draw

    Filename: models/User_model.php

    Line Number: 11

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 11
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: start

    Filename: models/User_model.php

    Line Number: 12

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 12
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: length

    Filename: models/User_model.php

    Line Number: 13

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 13
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: order

    Filename: models/User_model.php

    Line Number: 14

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 14
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: columns

    Filename: models/User_model.php

    Line Number: 15

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 15
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: order

    Filename: models/User_model.php

    Line Number: 16

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 16
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: search

    Filename: models/User_model.php

    Line Number: 17

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 17
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: searchCity

    Filename: models/User_model.php

    Line Number: 20

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 20
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: searchGender

    Filename: models/User_model.php

    Line Number: 21

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 21
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    A PHP Error was encountered
    Severity: Notice

    Message: Undefined index: searchName

    Filename: models/User_model.php

    Line Number: 22

    Backtrace:

    File: C:\xampp\htdocs\filter\application\models\User_model.php
    Line: 22
    Function: _error_handler

    File: C:\xampp\htdocs\filter\application\controllers\Users.php
    Line: 33
    Function: getUsers

    File: C:\xampp\htdocs\filter\index.php
    Line: 315
    Function: require_once

    Reply
  3. This course is so far the best I have seen on the web. It really implements MVC pattern.
    In many courses you see the form talking directly to the model, that view code in model. But here it is only the controller that passes the view request to the model, by substituting postdata variable of the model with $this->input->post(), request coming from the form. It is elegant and really professional.
    I want to use the code with Codeigniter 4 , that is replacing $this->input->post() by
    $this->request->getpost()
    This is how I tried use your code but no data is return error 500 could not load resources from this server
    public function loadUserList(){
    // POST data
    $postData = $this->request->getpost();

    // Get data
    $model = new Usermodel();
    $data = $model->loadUserList($postData);

    echo json_encode($data);
    }
    Is there any thing I need to change in your code in model?
    Thanks and keep it up

    Reply
    • I just have done it with CI4.
      function fetch_data($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[] = ” (firstname like ‘%”.$searchValue.”%’ or
      lastname like ‘%”.$searchValue.”%’ or
      email like’%”.$searchValue.”%’ ) “;
      }
      if($searchCity != ”){
      $search_arr[] = ” firstname='”.$searchCity.”‘ “;
      }
      if($searchGender != ”){
      $search_arr[] = ” lastname='”.$searchGender.”‘ “;
      }
      if($searchName != ”){
      $search_arr[] = ” email like ‘%”.$searchName.”%’ “;
      }
      if(count($search_arr) > 0){
      $searchQuery = implode(” and “,$search_arr);
      }

      $builder = $this->db->table($this->table);
      ## Total number of records without filtering
      #$this->db->select(‘count(*) as allcount’);
      #$records = $this->db->get(‘users’)->result();
      #$totalRecords = $records[0]->allcount;

      #$query = $this->db->query(‘select count(*) as allcount from books’);
      #$records = $query->getResult();
      #$totalRecords = $records[0]->allcount;

      $builder->select(‘count(*) as allcount’);
      $query = $builder->get();
      $records = $query->getResult();
      $totalRecords = $records[0]->allcount;

      ## Total number of record with filtering
      $builder->select(‘count(*) as allcount’);
      if($searchQuery != ”)
      $builder->where($searchQuery);
      $query = $builder->get();
      $records = $query->getResult();
      $totalRecordwithFilter = $records[0]->allcount;

      ## Fetch records
      $builder->select(‘*’);
      if($searchQuery != ”)
      $builder->where($searchQuery);
      $builder->orderBy($columnName, $columnSortOrder);
      $builder->limit($rowperpage, $start);
      $query = $builder->get();
      $records = $query->getResult();

      $data = array();

      foreach($records as $record ){

      $data[] = array(
      “firstname”=>$record->firstname,
      “lastname”=>$record->lastname,
      “email”=>$record->email,
      “action”=>”id.”‘>Detail”
      );

      }

      ## Response
      $response = array(
      “draw” => intval($draw),
      “iTotalRecords” => $totalRecords,
      “iTotalDisplayRecords” => $totalRecordwithFilter,
      “aaData” => $data
      );

      return $response;
      }

      Reply
    • I added image_url and store_url to the database and also in user_view.php and User_model.php
      { data: ‘image_url’ },
      { data: ‘store_url’ },

      Reply
      • Hi Danny,
        For this create an <img > element add pass the value to src attribute in $data Array in Model. e.g. $data[] = array( “image_url” => “<img src=’image.png’ width=’48px’ />”, “store_url” => “<a href=’#’ >Link</a>” );

  4. i have this tutorial work for me.
    can i ask for help?
    i want to join my table with other. it works on 1st page of pagination. but if i go to page 2. or others, the error happen. how to fix this?

    the error output looks like the query for page 2 is wrong.
    thanks before

    Reply
    • Hi Putra,

      For this, create a new column to display the s.no. In view update the datatable columns option –

      'columns': [
      { data: 'id' }, // new column
                  { data: 'username' },
                  { data: 'name' },
                  { data: 'email' },
                  { data: 'gender' },
                  { data: 'city' },
               ]

      In the Model update the $data Array  –

      $data[] = array( 
      "id" => ++$start,
      "username"=>$record->username,
      "name"=>$record->name,
      "email"=>$record->email,
      "gender"=>$record->gender,
      "city"=>$record->city
      );
      Reply

Leave a Comment