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



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

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

Related Posts