DataTables AJAX Pagination with Search and Sort in CodeIgniter 3

Last updated on by

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 that 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 AJAX pagination in CodeIgniter 3.

DataTables AJAX Pagination with Search and Sort in CodeIgniter 3

Demo  Download

Contents

  1. Table structure
  2. Database 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. Database 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/ folder.

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

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

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.

© 2016-2024 Makitweb, All rights reserved