Codeigniter Tutorials

Home » Codeigniter »

DataTables pagination using AJAX with Custom filter in codeigniter?

View :-

HTML

Include DataTables and jQuery library at the <head > section.

Add custom filter elements in <div>.

Two <select > elements for city and gender search and a textbox for a name search.

Create <table id='userTable'>.

Script

Initialize DataTable on #userTable selector and assign in userDataTable variable.

Send AJAX request to <?=base_url()?>index.php/Users/userList and with data option read custom search fields values – #sel_city, #sel_gender, #searchName and append in the data object.

In the columns options specify the key names which get read on successful callback.

Define change event on the #sel_city, #sel_gender and keyup event on #searchName elements.

Call userDataTable.draw(); to redraw the DataTable.

File Name : user_view.php

<!DOCTYPE html>
<html>
<head>
<title>DataTables AJAX pagination with Custom filter 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.6.0/jquery.min.js"></script>


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

</head>
<body>


<div>

<select id='sel_city'>
<option value=''>-- Select city --</option>
<?php
foreach($cities as $city){
echo "<option value='".$city."'>".$city."</option>";
}
?>
</select>


<select id='sel_gender'>
<option value=''>-- Select Gender --</option>
<option value='male'>Male</option>
<option value='female'>Female</option>
</select>


<input type="text" id="searchName" placeholder="Search Name">
</div>


<table id='userTable' class='display dataTable'>

<thead>
<tr>
<th>Username</th>
<th>Name</th>
<th>Email</th>
<th>Gender</th>
<th>City</th>
</tr>
</thead>

</table>


<script type="text/javascript">
$(document).ready(function(){

var userDataTable = $('#userTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
//'searching': false, // Remove default Search Control
'ajax': {
'url':'<?=base_url()?>index.php/Users/userList',
'data': function(data){
data.searchCity = $('#sel_city').val();
data.searchGender = $('#sel_gender').val();
data.searchName = $('#searchName').val();
}
},
'columns': [
{ data: 'username' },
{ data: 'name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'city' },
]
});

$('#sel_city,#sel_gender').change(function(){
userDataTable.draw();
});
$('#searchName').keyup(function(){
userDataTable.draw();
});
});
</script>
</body>
</html>

Controller :-

File Name : Users.php

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

class Users extends CI_Controller {

public function __construct(){

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

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

}

public function index(){

$cities = $this->User_model->getCities();

$data['cities'] = $cities;

// load view
$this->load->view('user_view',$data);

}

public function userList(){

// POST data
$postData = $this->input->post();

// Get data
$data = $this->User_model->getUsers($postData);

echo json_encode($data);
}

}

Model :-

File Name : User_model.php

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

class User_model extends CI_Model {

// Get DataTable data
function getUsers($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

// Custom search filter
$searchCity = $postData['searchCity'];
$searchGender = $postData['searchGender'];
$searchName = $postData['searchName'];

## Search
$search_arr = array();
$searchQuery = "";
if($searchValue != ''){
$search_arr[] = " (name like '%".$searchValue."%' or
email like '%".$searchValue."%' or
city like'%".$searchValue."%' ) ";
}
if($searchCity != ''){
$search_arr[] = " city='".$searchCity."' ";
}
if($searchGender != ''){
$search_arr[] = " gender='".$searchGender."' ";
}
if($searchName != ''){
$search_arr[] = " name like '%".$searchName."%' ";
}
if(count($search_arr) > 0){
$searchQuery = implode(" and ",$search_arr);
}

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

$data = array();

foreach($records as $record ){

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

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

return $response;
}

// Get cities array
public function getCities(){

## Fetch records
$this->db->distinct();
$this->db->select('city');
$this->db->order_by('city','asc');
$records = $this->db->get('users')->result();

$data = array();

foreach($records as $record ){
$data[] = $record->city;
}

return $data;
}

}

Table :-

File Name :

CREATE TABLE `users` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(80) NOT NULL,
`username` varchar(80) NOT NULL,
`gender` varchar(10) NOT NULL,
`email` varchar(80) NOT NULL,
`city` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

File Name :

Itechtuto

Codeigniter Tutorials

DataTables pagination using AJAX with Custom filter in codeigniter?

File Name : user_view.php

File Name : Users.php

File Name : User_model.php

File Name :

File Name :

File Name :

Connect Us Socially:

Quick Links