Codeigniter Tutorials

Home » Codeigniter »

How to DataTables AJAX Pagination with Search and Sort in CodeIgniter?

View :-

File Name : emp_view.php

<!DOCTYPE html>
<html>
<head>
<title>DataTables AJAX Pagination with Search and Sort in CodeIgniter 3</title>


<link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>


<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>


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

</head>
<body>


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

Controller

File Name : Employee.php

<?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);
}

}

Model :

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.

File Name : Employee_model.php

<?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;
}

}

File Name :

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;

File Name :

Itechtuto

Codeigniter Tutorials

How to DataTables AJAX Pagination with Search and Sort in CodeIgniter?

File Name : emp_view.php

File Name : Employee.php

File Name : Employee_model.php

File Name :

File Name :

File Name :

Connect Us Socially:

Quick Links