Code Playground.

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.

• Fetch data from the MySQL database and list in tabular format with jQuery Datatables plugin.
• Add pagination, search, and filter features to the HTML table with Datatables.

Before getting started, take a look the files structure of CodeIgniter Datatables server-side processing.

Create Database Table

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;

Config

autoload.php
Specify some useful library and helper to loaded by default.

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

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

Controller (Members.php)

The Members controller contains 3 functions, __construct(), index(), and getLists().

• __construct() – Load the member model.
• index() – Load the member’s list view.
• getLists() – This function handles server-side processing and is called by Ajax method of DataTable.
  • Fetch member’s records from the database using getRows() function of Member model.
  • The data is filtered based on the $_POST parameters posted by the Datatables.
  • Prepare data for Datatables.
  • Render output as 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 Member model handles the database related works.

• __construct() – Specify the table name, column fields order, searchable column fields, and recordset order.
• getRows() – Fetch the members data from the database. Returns the filtered records based on the specified parameters in the POST method.
• countAll() – Count all the records of members table.
• countFiltered() – Count filtered records based on the posted parameters.
• _get_datatables_query() – This is a helper function of Member model. Perform the SQL queries needed for an server-side processing requested.

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

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.

• Specify the selector ID (#memListTable) of the HTML table where the DataTables will be attached.
• Configure the following to enable the server-side processing:
  • Set the processing option to true.
  • Set the serverSide option to true.
  • Set the Ajax source URL from where DataTables will fetch the 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 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>

Conclusion

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.