I will show you how we can use DataTable API 1.10 in Codeigniter framework. You may also be interested in DataTable CRUD Example using Codeigniter, MySQL and AJAX

Grid view in an Web page is a very important now-a-days. Functions like sorting, searching, pagination is not an easy job in HTML tables. One of many grid view frameworks, an open source, light weight, highly flexible and customizable DataTable API is the most popular among. It offers features like AutoFill, inline editing, sticky header, responsive, and supports bootstrap. In version 1.10 DataTable has changed and improved over other versions.
In basic initialization datatable provides pagination, sorting, instant searching by loading whole data records at once. It can be a performance issue fetching large amount of data from server side. It will be better if we integrate server side pagination, searching and sorting, so we can break large amount data in chunk to increase the performance  significantly.

Prerequisites

Knowledge of PHP & Codeigniter, jQuery and AJAX
MySQL 5.x
Apache HTTP server 2.2
Codeigniter 2.1.4
Netbeans 7.x

You need to create assets folder in parallel to application folder for putting all asset files such as js, css, images etc.

Final Output

Codeigniter DataTable

Step 1. First thing is we need to create database table

CREATE TABLE `cds` (
  `cd_title` varchar(200) COLLATE latin1_general_ci NOT NULL,
  `cd_interpret` varchar(200) COLLATE latin1_general_ci NOT NULL,
  `cd_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cd_release_date` datetime NOT NULL,
  `cd_no_of_copies` int(11) NOT NULL DEFAULT '0',
  `cd_type` char(5) COLLATE latin1_general_ci NOT NULL,
  `cd_owner` varchar(35) COLLATE latin1_general_ci NOT NULL,
  `cd_content_type` varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`cd_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Insert some data into the table

insert  into `cds`(`cd_title`,`cd_interpret`,`cd_id`,`cd_release_date`,`cd_no_of_copies`,`cd_type`,`cd_owner`,`cd_content_type`) values
('Singham','Rohit Setty',1,'2013-01-09 08:43:14',5,'Video','Rohit Setty','Movie'),
('Singham Returns','Rohit Setty',2,'2015-09-07 07:43:21',7,'Video','Rohit Setty','Movie'),
('Golmal','Rohit Setty',3,'2015-08-18 07:43:26',4,'Video','Rohit Setty','Movie'),
('Golmal Returns','Rohit Setty',4,'2015-06-30 07:43:29',6,'Video','Rohit Setty','Movie'),
('Golmal Returns 2','Rohit Setty',5,'2014-07-15 07:43:35',10,'Video','Rohit Setty','Movie'),
('Welcome','Rohit Setty',6,'2014-05-14 07:43:41',12,'Video','Rohit Setty','Movie'),
('Toofan','RGV',7,'2013-10-30 07:44:45',8,'Video','RGV','Movie'),
('Alag Alag','Kishore Kumar',8,'2012-01-17 07:45:39',25,'Audio','Kishore Kumar','Songs'),
('Sholay','Amitabh Bacchan',9,'1990-02-14 08:10:47',6,'Video','Amitabh','Movie'),
('Khiladi','Akshay Kumar',10,'2010-06-15 08:11:36',7,'Video','Akshay','Movie'),
('Taal Songs','A R Reheman',11,'2014-06-10 08:12:47',8,'Audio','A R Reheman','Songs');

Step 2. Once you have codeigniter framework setup in place then configure database settings at location application/config/database.php

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'cdcol';

Step 3. Create a assets helper file under /application/helpers/assets_helper.php which gives us the URL for different assets like css, js etc.

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

function assets_url() {
    return base_url();
}

/* End of file assets_helper.php */
/* Location: ./application/helpers/assets_helper.php */

Step 4. Modify the autoload.php file at location application/config/autoload.php as follows

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

$autoload['helper'] = array('url', 'file', 'assets', 'text', 'form');

Step 5. Create a model under application/models/cdmodel.php which will give us the data for the grid view. The below model file is pretty simple.

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * Description of cdmodel
 *
 * @author https://roytuts.com
 */
class CdModel extends CI_Model {

    private $cds = 'cds';

    function __construct() {
        
    }

    function get_cd_list() {
        /* Array of table columns which should be read and sent back to DataTables. Use a space where
         * you want to insert a non-database field (for example a counter or static image)
         */
        $aColumns = array(
            'cd_id',
            'cd_title',
            'cd_interpret',
            'cd_release_date',
            'cd_no_of_copies',
            'cd_type',
            'cd_owner',
            'cd_content_type');

        /* Indexed column (used for fast and accurate table cardinality) */
        $sIndexColumn = "cd_id";

        /* Total data set length */
        $sQuery = "SELECT COUNT('" . $sIndexColumn . "') AS row_count
            FROM $this->cds";
        $rResultTotal = $this->db->query($sQuery);
        $aResultTotal = $rResultTotal->row();
        $iTotal = $aResultTotal->row_count;

        /*
         * Paging
         */
        $sLimit = "";
        $iDisplayStart = $this->input->get_post('start', true);
        $iDisplayLength = $this->input->get_post('length', true);
        if (isset($iDisplayStart) && $iDisplayLength != '-1') {
            $sLimit = "LIMIT " . intval($iDisplayStart) . ", " .
                    intval($iDisplayLength);
        }

        $uri_string = $_SERVER['QUERY_STRING'];
        $uri_string = preg_replace("/%5B/", '[', $uri_string);
        $uri_string = preg_replace("/%5D/", ']', $uri_string);

        $get_param_array = explode("&", $uri_string);
        $arr = array();
        foreach ($get_param_array as $value) {
            $v = $value;
            $explode = explode("=", $v);
            $arr[$explode[0]] = $explode[1];
        }

        $index_of_columns = strpos($uri_string, "columns", 1);
        $index_of_start = strpos($uri_string, "start");
        $uri_columns = substr($uri_string, 7, ($index_of_start - $index_of_columns - 1));
        $columns_array = explode("&", $uri_columns);
        $arr_columns = array();
        foreach ($columns_array as $value) {
            $v = $value;
            $explode = explode("=", $v);
            if (count($explode) == 2) {
                $arr_columns[$explode[0]] = $explode[1];
            } else {
                $arr_columns[$explode[0]] = '';
            }
        }

        /*
         * Ordering
         */
        $sOrder = "ORDER BY ";
        $sOrderIndex = $arr['order[0][column]'];
        $sOrderDir = $arr['order[0][dir]'];
        $bSortable_ = $arr_columns['columns[' . $sOrderIndex . '][orderable]'];
        if ($bSortable_ == "true") {
            $sOrder .= $aColumns[$sOrderIndex] .
                    ($sOrderDir === 'asc' ? ' asc' : ' desc');
        }

        /*
         * Filtering
         */
        $sWhere = "";
        $sSearchVal = $arr['search[value]'];
        if (isset($sSearchVal) && $sSearchVal != '') {
            $sWhere = "WHERE (";
            for ($i = 0; $i < count($aColumns); $i++) {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $this->db->escape_like_str($sSearchVal) . "%' OR ";
            }
            $sWhere = substr_replace($sWhere, "", -3);
            $sWhere .= ')';
        }

        /* Individual column filtering */
        $sSearchReg = $arr['search[regex]'];
        for ($i = 0; $i < count($aColumns); $i++) {
            $bSearchable_ = $arr['columns[' . $i . '][searchable]'];
            if (isset($bSearchable_) && $bSearchable_ == "true" && $sSearchReg != 'false') {
                $search_val = $arr['columns[' . $i . '][search][value]'];
                if ($sWhere == "") {
                    $sWhere = "WHERE ";
                } else {
                    $sWhere .= " AND ";
                }
                $sWhere .= $aColumns[$i] . " LIKE '%" . $this->db->escape_like_str($search_val) . "%' ";
            }
        }


        /*
         * SQL queries
         * Get data to display
         */
        $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
        FROM $this->cds
        $sWhere
        $sOrder
        $sLimit
        ";
        $rResult = $this->db->query($sQuery);

        /* Data set length after filtering */
        $sQuery = "SELECT FOUND_ROWS() AS length_count";
        $rResultFilterTotal = $this->db->query($sQuery);
        $aResultFilterTotal = $rResultFilterTotal->row();
        $iFilteredTotal = $aResultFilterTotal->length_count;

        /*
         * Output
         */
        $sEcho = $this->input->get_post('draw', true);
        $output = array(
            "draw" => intval($sEcho),
            "recordsTotal" => $iTotal,
            "recordsFiltered" => $iFilteredTotal,
            "data" => array()
        );

        foreach ($rResult->result_array() as $aRow) {
            $row = array();
            foreach ($aColumns as $col) {
                $row[] = $aRow[$col];
            }
            $output['data'][] = $row;
        }

        return $output;
    }

}

/* End of file cdmodel.php */
/* Location: ./application/models/cdmodel.php */

Step 6. Now create one controller called CdController under application/controller/cdcontroller.php with below code

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * Description of cdcontroller
 *
 * @author https://roytuts.com
 */
class CdController extends CI_Controller {

    function __construct() {
        parent::__construct();
        $this->load->model('cdmodel', 'cd');
    }

    function index() {
        $this->load->view('cds', NULL);
    }

    function cd_list() {
        $results = $this->cd->get_cd_list();
        echo json_encode($results);
    }

}

/* End of file cdcontroller.php */
/* Location: ./application/controllers/cdcontroller.php */

Step 7. Now modify the application/config/routes.php file as follows

$route['default_controller'] = "cdcontroller";

Step 8. Next create a view with the following code under /application/views/cds.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8"/>
        <title>Codeigniter Datatable Example</title>
        <!--[if IE]> <script> (function() { var html5 = ("abbr,article,aside,audio,canvas,datalist,details," + "figure,footer,header,hgroup,mark,menu,meter,nav,output," + "progress,section,time,video").split(','); for (var i = 0; i < html5.length; i++) { document.createElement(html5[i]); } try { document.execCommand('BackgroundImageCache', false, true); } catch(e) {} })(); </script> <![endif]-->
        <link type="text/css" rel="stylesheet" href="<?php echo base_url(); ?>assets/css/jquery.dataTables.min.css"/>
        <script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery-1.11.3.min.js"></script>
        <script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
        <script type= 'text/javascript'>
            $(document).ready(function () {
                $('#cd-grid').DataTable({
                    "processing": true,
                    "serverSide": true,
                    "ajax": "http://localhost/ci/index.php/cdcontroller/cd_list"
                });
            });
        </script>
    </head>
    <body>
        <table id="cd-grid" class="display" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>Cd Id</th>
                    <th>Title</th>
                    <th>Interpret</th>
                    <th>Release Date</th>
                    <th>No of Copies</th>
                    <th>Type</th>
                    <th>Owner</th>
                    <th>Content Type</th>
                </tr>
            </thead>

            <tfoot>
                <tr>
                    <th>Cd Id</th>
                    <th>Title</th>
                    <th>Interpret</th>
                    <th>Release Date</th>
                    <th>No of Copies</th>
                    <th>Type</th>
                    <th>Owner</th>
                    <th>Content Type</th>
                </tr>
            </tfoot>
        </table>
    </body>
</html>

Step 9. Download the assets directory and put it under project root directory

assets

Step 10. Run the application.

That’s all. Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

21 thoughts on “DataTable using Codeigniter, MySQL and AJAX

  1. Thai language is not work in filter function.
    In config.php
    ‘char_set’ => ‘utf8’,
    ‘dbcollat’ => ‘utf8_unicode_ci’,
    database and table and filed
    I set all to utf8
    It still not work. Please help

  2. Please guide me.

    related to your tutorial, please guide me.

    I’m saved some record against some users. i want to show these record to that specific user when they login and only search there record that store in db with there user id.

    Regards in-advance.

  3. Hello, I used your code and works perfect thank you, but I need to add a restriction of two dates to the query and I can’t find a way to make it work. Any help will be great. Thank you.

      1. Thank you Soumitra, I took the time to understand your code and with this I made mine work with all the necessary modifications. I didn’t use the link you shared just the original post of this page. Thanks again for the original post. Amazing work!

  4. Hello, I used your code and works perfect thank you, but I also want sum of some columns and I can’t find a way to make it work. Any help will be great. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *