DataTables is a strong jQuery module that provides the easiest method for displaying data in tabular style on a web page. Using the Datatables plugin, you can quickly show data in an HTML table with sorting, filtering, and pagination capability. Datatables server-side processing allows data to be retrieved from a database and displayed in a tabular layout with search and paging capabilities.
In general, DataTables function with client-side data. However, if your web application processes a huge volume of data from the database, you should consider using Datatables' server-side processing option. PHP and MySQL can easily manage datatables server-side processing. DataTables may also be used with server-side processing in the CodeIgniter framework. In this video, we'll teach you how to enable server-side processing in CodeIgniter DataTables in CodeIgniter 3.
The following functionality will be provided in this CodeIgniter DataTbales sample.
- Using the jQuery Datatables plugin, get data from a MySQL database and display it in a tabular fashion.
- Datatables may be used to provide pagination, search, and filter functionality to an HTML table.
Before you begin, examine the file structure of CodeIgniter Datatables server-side operation.
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 in the database must be established from which the server-side data will be collected via the DataTables library. In the MySQL database, the following SQL creates a members table with some basic columns.
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 which libraries and helpers should be loaded by default.
$autoload['libraries'] = array('database'); $autoload['helper'] = array('url');
Members has three functions: __construct(), index(), and getLists ().
1. __construct() – Start by loading the member model.
2. index() – Display the member list view.
3. getLists() – This function conducts server-side processing and is invoked by DataTable's Ajax method.
- Using the getRows() method of the Member model, retrieve the records of members from the database.
- The Datatables' $_POST parameters are used to filter the data.
- Data should be prepared for Datatables.
- Produce output in JSON format.
<?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 database-related tasks are handled by the Member model.
1. __construct() – Enter the table name, the order of the column fields, the order of the searchable column fields, and the order of the recordsets.
2. getRows() – Retrieve the members' information from the database. Returns the filtered records depending on the parameters supplied in the POST method.
3. countAll() – Count the number of records in the members table.
4. countFiltered() – Count the number of filtered records depending on the parameters you specified.
5. _get_datatables_query() – This is a Member model assistance method. Execute the SQL queries required for the desired server-side processing.
<?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)]); } } }
Using DataTables with CodeIgniter, this view displays the member's data in an HTML table with search, filter, and pagination options.
Include the jQuery and DataTables library files first.
<!-- 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>
To initialise the Datatables plugin, use the DataTable() function.
- Specify the HTML table selector ID (#memListTable) to which the DataTables will be associated.
- To enable server-side processing, configure the following:
- Turn on the processing option.
- Set the serverSide parameter to true.
- Set the Ajax source URL, which DataTables will use to retrieve server-side data.
<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 the HTML table element where the server-side data will be shown by the DataTables.
<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>
Our sample script demonstrates how to use DataTables in conjunction with Server-side processing in CodeIgniter. In the CodeIgniter application, you can quickly add a fully-featured HTML data table with search and pagination. We've demonstrated some of the most widely used functionalities of Datatables with CodeIgniter (list, search, filter, sorting, and paging). With DataTables server-side processing, you can simply extend the capabilities of HTML data tables.
© ThemesGiant Copyright @2015-2022 | All rights reserved.