loading

How to DataTables Server-side Processing with CodeIgniter

How to DataTables Server-side Processing with CodeIgniter

How to DataTables Server-side Processing with CodeIgniter

0 Sales

Free

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) - == $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.

LICENSE OF USE

You can use it for personal or commercial projects. You can't resell it partially or in this form.

PRODUCT INFO

Create Date : Feb 17, 2022

Updated Date : Feb 17, 2022

Ratings

Comments : 0

Downloads : 0