Posted in AJAX, Codeigniter

DataTable Using Codeigniter MySQL and AJAX

DataTable

I will show you how you can use DataTable API 1.10/1.12 to display tabular data in Codeigniter framework.

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 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.

Related Post:

Prerequisites

Codeigniter 3.1 – 3.1.11
MySQL 5.x – 8.0.26
Apache HTTP Server 2.2/2.4 (Optional)

jQuery 1.11.3 – 3.6.0, DataTable 1.10.9 – 1.12.1

Project Directory

It’s assumed that you have setup PHP and Codeigniter in Windows system.

Now I will create a project root directory called codeigniter-datatable-ajax anywhere in the physical drive.

Now move all the directories and files from CodeIgniter framework into codeigniter-datatable-ajax directory.

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

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

MySQL Table

First thing is you need to create database table in MySQL server.

For MySQL server version 8.x, use the following table structure:

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

For MySQL server version 5.x, use the following table structure:

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 for testing the application right away:

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

Database Configurations

Configure database settings at location application/config/database.php.

'hostname' => 'localhost',
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',

Auto-Load Configurations

Modify the autoload.php file at location application/config as follows to autoload certain things for accessing them throughout the application:

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

Model Class

Create a model class file application/models/cdmodel.php which will give you the data for the grid view.

<?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 */

Controller Class

Now create one controller called CdController class in the file 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 */

Routing

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

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

View File

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

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?><!DOCTYPE html>
<html lang="en">
    <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"/>-->
		<link type="text/css" rel="stylesheet" href="//cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css"/>
        <!--<script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery-1.11.3.min.js"></script>-->
		<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
        <!--<script type= 'text/javascript' src="<?php //echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>-->
		<script type= 'text/javascript' src="//cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>		
        <script type= 'text/javascript'>
            $(document).ready(function () {
                $('#cd-grid').DataTable({
                    "processing": true,
                    "serverSide": true,
                    //"ajax": "http://localhost/codeigniter-datatable-ajax/index.php/cdcontroller/cd_list"
					"ajax": "http://localhost/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>

Assets

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

assets

Testing CodeIgniter DataTable

Once you run the application, you will see the following output in the browser:

codeigniter datatable

You can now search using the search input field. You can also change how many records you want to display per page. You can use pagination to navigate through different pages.

Source Code

Download

4 thoughts on “DataTable Using Codeigniter MySQL and AJAX”

Leave a Reply

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