close
DataTables is a powerful jQuery library that provides the quickest way to display data in tabular format on the web page. You can easily display data in an HTML table with sorting, filtering, and pagination functionality using Datatables plugin. Datatables server-side processing allows to fetch data from the database and listed in a tabular view with search and pagination feature.
Generally, the DataTables working with the client-side data. But if your web application handles a large amount of data from the database, you must consider using server-side processing option in Datatables. Datatables server-side processing can be easily handled with PHP and MySQL. You can also use DataTables with server-side processing in CodeIgniter framework. In this tutorial, we will show you how to enable server-side processing in DataTables with CodeIgniter in CodeIgniter 3.
In this CodeIgniter DataTbales example, the following functionality will be implemented.
Before getting started, take a look the files structure of CodeIgniter Datatables server-side processing.
codeigniter_datatables/ ├── application/ │ ├── controllers/ │ │ └── Members.php │ ├── models/ │ │ └── Member.php │ └── views/ │ └── members/ │ └── index.php └── assets/ ├── DataTables/ │ ├── datatables.min.js │ └── datatables.min.css └── js/ └── jquery.min.js
A table needs to be created in the database from where the server-side data will be fetched by the DataTables library. The following SQL creates a members table with some basic fields in the MySQL database.
CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL, `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
autoload.php
Specify some useful library and helper to loaded by default.
$autoload['libraries'] = array('database');
$autoload['helper'] = array('url');
The Members controller contains 3 functions, __construct(), index(), and getLists().
getRows() function of Member model.<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Members extends CI_Controller{
function __construct(){
parent::__construct();
// Load member model
$this->load->model('member');
}
function index(){
// Load the member list view
$this->load->view('members/index');
}
function getLists(){
$data = $row = array();
// Fetch member's records
$memData = $this->member->getRows($_POST);
$i = $_POST['start'];
foreach($memData as $member){
$i++;
$created = date( 'jS M Y', strtotime($member->created));
$status = ($member->status == 1)?'Active':'Inactive';
$data[] = array($i, $member->first_name, $member->last_name, $member->email, $member->gender, $member->country, $created, $status);
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->member->countAll(),
"recordsFiltered" => $this->member->countFiltered($_POST),
"data" => $data,
);
// Output to JSON format
echo json_encode($output);
}
}
The Member model handles the database related works.
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Member extends CI_Model{
function __construct() {
// Set table name
$this->table = 'members';
// Set orderable column fields
$this->column_order = array(null, 'first_name','last_name','email','gender','country','created','status');
// Set searchable column fields
$this->column_search = array('first_name','last_name','email','gender','country','created','status');
// Set default order
$this->order = array('first_name' => 'asc');
}
/*
* Fetch members data from the database
* @param $_POST filter data based on the posted parameters
*/
public function getRows($postData){
$this->_get_datatables_query($postData);
if($postData['length'] != -1){
$this->db->limit($postData['length'], $postData['start']);
}
$query = $this->db->get();
return $query->result();
}
/*
* Count all records
*/
public function countAll(){
$this->db->from($this->table);
return $this->db->count_all_results();
}
/*
* Count records based on the filter params
* @param $_POST filter data based on the posted parameters
*/
public function countFiltered($postData){
$this->_get_datatables_query($postData);
$query = $this->db->get();
return $query->num_rows();
}
/*
* Perform the SQL queries needed for an server-side processing requested
* @param $_POST filter data based on the posted parameters
*/
private function _get_datatables_query($postData){
$this->db->from($this->table);
$i = 0;
// loop searchable columns
foreach($this->column_search as $item){
// if datatable send POST for search
if($postData['search']['value']){
// first loop
if($i===0){
// open bracket
$this->db->group_start();
$this->db->like($item, $postData['search']['value']);
}else{
$this->db->or_like($item, $postData['search']['value']);
}
// last loop
if(count($this->column_search) - 1 == $i){
// close bracket
$this->db->group_end();
}
}
$i++;
}
if(isset($postData['order'])){
$this->db->order_by($this->column_order[$postData['order']['0']['column']], $postData['order']['0']['dir']);
}else if(isset($this->order)){
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
}
This view lists the member’s data in an HTML table with search, filter, and pagination options using DataTables with CodeIgniter.
At first, include the jQuery and DataTables library files.
<!-- DataTables CSS library --> <link rel="stylesheet" type="text/css" href="<?php echo base_url('assets/DataTables/datatables.min.css'); ?>"/> <!-- jQuery library --> <script src="<?php echo base_url('assets/js/jquery.min.js'); ?>"></script> <!-- DataTables JS library --> <script type="text/javascript" src="<?php echo base_url('assets/DataTables/datatables.min.js'); ?>"></script>
Use the DataTable() method to initialize the Datatables plugin.
#memListTable) of the HTML table where the DataTables will be attached.<script>
$(document).ready(function(){
$('#memListTable').DataTable({
// Processing indicator
"processing": true,
// DataTables server-side processing mode
"serverSide": true,
// Initial no order.
"order": [],
// Load data from an Ajax source
"ajax": {
"url": "<?php echo base_url('members/getLists/'); ?>",
"type": "POST"
},
//Set column definition initialisation properties
"columnDefs": [{
"targets": [0],
"orderable": false
}]
});
});
</script>
Now, define HTML table element where the DataTables will list the server-side data.
<table id="memListTable" class="display" style="width:100%">
<thead>
<tr>
<th>#</th>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
</tr>
</thead>
<tfoot>
<tr>
<th></th>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
</tr>
</tfoot>
</table>
Add Custom Search and Filter to DataTables Server-side Processing with PHP
Our example script helps you to integrate DataTables with Server-side processing in CodeIgniter. You can easily add a fully-featured HTML data table with search and pagination in the CodeIgniter application. Here we have shown some commonly used features (List, search, filter, sorting, and paging) of Datatables with CodeIgniter. You can easily enhance the HTML data tables functionality with DataTables server-side processing.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
How to do inline edit in this example and save the data to database
How to change default order to be multiple order in your model ?
Excellent.
thanks a lot.
I appreciate your efforts. thanks, I am learning how to make it with CI4
hello sir, When i joined many table by main table id ..how can i search the another table name
This is how I usually do my join statements hopes this helps:
$this->db->select(‘*’);
$this->db->where(‘location’, $id);
$this->db->where(‘priority’, $priority);
$this->db->join(‘locations’, ‘tickets.location = locations.id’, ‘inner’);
$this->data[‘tickets’] = $this->support_m->get();
Nice tutorial codexworld :), can you also provide with join sample and search functionality of it 🙂
eg.
users_table
gadgets_table
Name Gender Gadgets
John M phone, laptop, drone