DataTables AJAX Pagination with Search and Sort in CodeIgniter

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 which comes with all basic functionality which requires for 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


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

Count total records in the employees table with or without 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($searchValue != '')
        $this->db->where($searchQuery);
     $records = $this->db->get('employees')->result();
     $totalRecordwithFilter = $records[0]->allcount;

     ## Fetch records
     $this->db->select('*');
     if($searchValue != '')
        $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" => $totalRecordwithFilter,
        "iTotalDisplayRecords" => $totalRecords,
        "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</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


7. Conclusion

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

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.
Spread the love
  • 1
  •  
  •  
  •  
  •  

2 Comments

  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.

    April 3, 2019
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *