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

Create Database Table

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;

Config

autoload.php
Specify which libraries and helpers should be loaded by default.

$autoload['libraries'] = array('database');

$autoload['helper'] = array('url');

Controller (Members.php)

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

Model (Member.php)

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

}

View (members/index.php)

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>

Conclusion

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.