Datatables 1.10+ with CodeIgniter 3.1.5

alemarba

There is an interesting Datatables libraries available at https://github.com/zepernick/Codeigniter-DataTables developed in 2015.

With that said, after making a quick review it seems to me that that library may be a little overkill for somebody that just want to try server side processing with Codeigniter. I independently built a simpler one that works neatly with Codeigniter 3.1.5 and Datatables 1.10+

Load the Datatables library in your constructor and set database that will be used to pull data

class MyConstructor extends CI_Controller {
    public function __construct()
    {
        parent::__construct();
        [...]
        $this->load->library('Datatables');
        $this->datatables->setDatabase('GFDVD22');
    }

The library return the fully formed JSON response. It requires that you provide the tables and the columns that will be queried. While the tables can obtained from the datatables columns POST variable, I think that overhead is better eliminated.

$aTable = 'my_table';
$aColumns = array('Col1','Col2','Col3');

$json = $this->datatables->get_json($aTable, $aColumns);

In you application /library create a Datatable.php with the following code

class DataTables {
    
    protected $CI;
    private $database;
    
    public function __construct($db = NULL)
    {
        $this->CI =& get_instance();
    }
    
    public function setDatabase($database) {
        $this->database = $database;
    }
    
    public function getDatabase() {
        return $this->database;
    }
    
    public function get_json($aTable = NULL, $aColumns = NULL)
    {   
        // Load CI Database library
        $this->CI->load->database($this->database);
        
        // Read dataTables POST variables
        $iDraw = $this->CI->input->post('draw');
        $iColumns = $this->CI->input->post('columns');
        $iOrder = $this->CI->input->post('order');
        $iStart = $this->CI->input->post('start');
        $iLength = $this->CI->input->post('length');
        $iSearch = $this->CI->input->post('search');
        
        // Total rows in the table
        $recordsTotal = $this->CI->db->count_all($aTable);
        
        // Filtering
        // NOTE: This does not match the built-on DataTables filtering which does it
        // word by word on any field. It's possible to do here, but concerned about efficiency
        // on very large tables.
        $recordsFiltered = $recordsTotal;
        
        if(isset($iSearch) && $iSearch['value'] != '') {
            for($i=0; $i < count($aColumns); $i++) {
                $this->db->or_like($aColumns[$i], $iSearch['value']);
            }
            
            // Saves number of records that matches the query and filters
            $recordsFiltered = $this->db->count_all_results($aTable, false);
        }
        
        if(isset($iSearch) && $iSearch['value'] != '') {
            for($i=0; $i < count($aColumns); $i++) {
                $this->db->or_like($aColumns[$i], $iSearch['value']);
            }
        }
        
        // Write the SELECT portion of the query
        $this->CI->db->select(implode(',', $aColumns));
        
        // Ordering
        if(isset($iOrder)) {
            for($i=0; $i < count($iOrder); $i++) {
                $this->CI->db->order_by($aColumns[$iOrder[0]['column']], strtoupper($iOrder[0]['dir']));
            }
        } else {
            $this->CI->db->order_by($aColumns[0], 'ASC');
        }
        
        // Paging
        if(isset($iStart) && $iLength != '-1') {
            $this->CI->db->limit($iLength, $iStart);
        } else {
            $this->CI->db->limit(25, 1);
        }
        
        // Select Data
        $Data = $this->CI->db->get($aTable);
        
        // JSON enconding
        $json = json_encode(array(
            "draw" => isset($iDraw) ? $iDraw : 1,
            "recordsTotal" => $recordsTotal,
            "recordsFiltered" => $recordsFiltered,
            "data" => $Data->result())
            );
        
        return $json;
    }
}