DataTables AJAX Pagination with Search and Sort in CodeIgniter 3

CodeIgniter already has a library for pagination.

Which is easier to implement on the page. But you need to customize it to add extra functionality like – search, sorting, rows per page.

Datatables is a jQuery library that comes with all basic functionality which requires pagination.

Need to handle the request and return the response in the specified format.

In this tutorial, I show how you can implement Datatables pagination in CodeIgniter.

DataTables AJAX Pagination with Search and Sort 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 employees table and added some records –

CREATE TABLE `employees` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(60) NOT NULL,
  `salary` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` 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 Employee.

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

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 Employee_model.php file in application/models/ directory.

Create a single method –

  • getEmployees – Read $postData values.

If $searchQuery is not empty then set a search query.

Count total records in the employees table with or without a search filter.

Fetch records from employees table and loop on it to initialize $data Array with keys defined while Datatable initialization in the columns option.

Set draw, iTotalRecords, iTotalDisplayRecords, and aaData keys with values in the $response Array.

Return $response Array.

Completed Code

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Employee_model extends CI_Model {

   function getEmployees($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

     ## Search 
     $searchQuery = "";
     if($searchValue != ''){
        $searchQuery = " (emp_name like '%".$searchValue."%' or email like '%".$searchValue."%' or city like'%".$searchValue."%' ) ";
     }

     ## Total number of records without filtering
     $this->db->select('count(*) as allcount');
     $records = $this->db->get('employees')->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('employees')->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('employees')->result();

     $data = array();

     foreach($records as $record ){

        $data[] = array( 
           "emp_name"=>$record->emp_name,
           "email"=>$record->email,
           "gender"=>$record->gender,
           "salary"=>$record->salary,
           "city"=>$record->city
        ); 
     }

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

     return $response; 
   }

}

4. Controller

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

Define 3 methods –

  • __construct – Load url helper, Employee_Model Model.
  • index – Load emp_view view.
  • empList – This method is used to load datatable content. Pass POST values to getEmployees() method and return the response in JSON format.

Completed Code

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Employee extends CI_Controller {

   public function __construct(){

     parent::__construct();
     $this->load->helper('url');

     // Load model
     $this->load->model('Employee_model');

   }

   public function index(){

     // load view
     $this->load->view('emp_view');

   }

   public function empList(){
     
     // POST data
     $postData = $this->input->post();

     // Get data
     $data = $this->Employee_model->getEmployees($postData);

     echo json_encode($data);
  }

}

5. View

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

HTML

Include datatable.min.css, jQuery and datatable.min.js script in the <head> section.

NOTE – You can also download Datatables from here and include it if you don’t want to use CDN.

Create <table id='empTable'> element and added some columns the header row.

Script

Initialize datatable on #empTable selector.

Set processing: false, serverSide: true, serverMethod: post.

With ajax option send AJAX request to '<?=base_url()?>index.php/Employee/empList'.

Set columns option.

Completed Code

<!DOCTYPE html>
<html>
  <head>
     <title>DataTables AJAX Pagination with Search and Sort 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.3.1/jquery.min.js"></script>

     <!-- Datatable JS -->
     <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

  </head>
  <body>

     <!-- Table -->
     <table id='empTable' class='display dataTable'>

       <thead>
         <tr>
           <th>Employee name</th>
           <th>Email</th>
           <th>Gender</th>
           <th>Salary</th>
           <th>City</th>
         </tr>
       </thead>

     </table>

     <!-- Script -->
     <script type="text/javascript">
     $(document).ready(function(){
        $('#empTable').DataTable({
          'processing': true,
          'serverSide': true,
          'serverMethod': 'post',
          'ajax': {
             'url':'<?=base_url()?>index.php/Employee/empList'
          },
          'columns': [
             { data: 'emp_name' },
             { data: 'email' },
             { data: 'gender' },
             { data: 'salary' },
             { data: 'city' },
          ]
        });
     });
     </script>
  </body>
</html>

6. Demo

View Demo


7. Conclusion

In the example, I have used Datatable and jQuery library CDN which you can change if they are available in your project.

View this tutorial to know datatable AJAX pagination implementation in CodeIgniter 4.

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.

23 thoughts on “DataTables AJAX Pagination with Search and Sort in CodeIgniter 3”

  1. Hi Yogesh!
    in the Dutch language – as in French – we use many special characters such as: é, è, à, ç, ë. When reading or displaying my data, the pagination stops every time a record has to be shown that contains such a special character! How can I bypass this? I could replace the special characters with characters without accent, but then the spelling and pronunciation is wrong!
    Greetings, and thanks you already for all the examples I eagerly devour.

    Reply
  2. You have an error in your SQL. On the create employees table you have a comma at the end, you have to remove it or import fails.

    `email` varchar(80) NOT NULL, <<<<<<————- Comma has to be removed.
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Reply
    • To view, the issue navigate to Network tab from Developer tools and search for ajax script for datatable. It will display the issue in detail and help you to resolve it.

      Reply
  3. Hello! Very nice tutorial, but why my pagination not working properly after search, the demo that you give on this page also showing the same problem.

    Reply
  4. Super articles, good but if i use these coding. I got ReferenceError: “jQuery is not defined” these error. how can i solve this. Please give me a solution.

    Reply
  5. Hi Yogesh, I have implemented data tables in my CI application. It is working properly. Now the issue is in the mobile responsive view where it is taking around 25% of the screen. With Show 10 entries coming in one row and Search box in another. How can I reduce the size and fit this in smaller space? Any suggestion would be much appreciated.

    Reply
  6. Severity: Notice
    Message: Undefined index: draw

    Severity: Notice
    Message: Undefined index: start

    Severity: Notice
    Message: Undefined index: length

    Severity: Notice
    Message: Undefined index: order

    Severity: Notice
    Message: Undefined index: columns

    i am Getting these error.any solution for this ?
    Reply will br appriciated 🙂

    Reply
  7. I have just followed this. But i got invalid json response error. I got all the data in response which is visible in network. But it is not displayed in the page. Please help me.

    Reply

Leave a Comment