DataTables - Server-side Processing using Codeigniter, MySQL and AJAX with custom searching

In this post, we will explain how to use most popular dataTables plugin using Ajax with Codeigniter framework and also using custom searching. Datatable is a highly flexible jQuery plugin that’s used to convert HTML table into useful grid layout. DataTables is a jQuery plugin that can provide a lot of functionality like Pagination, Sorting, Filtering, Server-side processing (AJAX).

Step 1: Create Database and Table: DB Name: datatable_DB and Table Name:order_details
For this tutorial, you need a MySQL database with the following table:

CREATE TABLE `order_details` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `city` varchar(60) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `order_id` varchar(20) NOT NULL,
  `order_date` varchar(12) NOT NULL,
  `status` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `order_details` (`id`, `name`, `city`, `amount`, `order_id`, `order_date`, `status`) VALUES
(1, 'Michael', 'New York', '450.00', '000001', '1506859318', 'Completed'),
(2, 'Rosy', 'Sydney', '250.00', '000002', '1506945718', 'Completed'),
(3, 'Simon', 'Washington', '300.00', '000003', '1507032118', 'Pending'),
(4, 'Sameer', 'Kolkatta', '500.00', '000004', '1509706918', 'Pending'),
(5, 'Sachin', 'Mumbai', '0.00', '000005', '1510049458', 'Pending'),
(6, 'Bably', 'New Delhi', '250.00', '000006', '1510308658', 'Completed'),
(7, 'Sonia', 'New Delhi', '0.00', '000007', '1512900658', 'Completed'),
(8, 'Shama', 'New Delhi', '250.00', '000008', '1513332658', 'Completed'),
(9, 'Merry', 'Landon', '0.00', '000009', '1514196705', 'Completed'),
(10, 'Nixon Tiger', 'Canada', '300.00', '000010', '1514801505', 'Completed'),
(11, 'Garrett Winters', 'Wellington', '250.00', '000011', '1514974305', 'Cancelled'),
(12, 'Colleen Hurst', 'New Jersey', '0.00', '000012', '1515060705', 'Cancelled'),
(13, 'Sonya Frost', 'New Jersey', '0.00', '000013', '1515147105', 'Completed'),
(14, 'Airi Satouy', 'Peris', '0.00', '000014', '1515233505', 'Cancelled');

ALTER TABLE `order_details` ADD PRIMARY KEY (`id`);
ALTER TABLE `order_details` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
?>

Step 2: Create Model
Create a model file named Site_model.php inside “application/models” folder.

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Site Model
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Site_model extends CI_Model {

    private $_order_id;
    private $_name;
    private $_city;
    private $_startDate;
    private $_endDate;

    public function setOrderID($order_id) {
        $this->_order_id = $order_id;
    }
    public function setName($name) {
        $this->_name = $name;
    }    
    public function setStartDate($startDate) {
        $this->_startDate = $startDate;
    }
    public function setEndDate($endDate) {
        $this->_endDate = $endDate;
    }
    // get Orders List
    public function getOrders() {        
        $this->db->select(array('o.order_id', 'o.name', 'o.city', 'o.amount', 'o.order_date', 'o.status', 'o.amount'));
        $this->db->from('order_details o');
        if(!empty($this->_startDate) && !empty($this->_endDate)) {
            $this->db->where('DATE_FORMAT(FROM_UNIXTIME(`o`.`order_date`),"%Y-%m-%d") BETWEEN \'' . $this->_startDate . '\' AND \'' . $this->_endDate . '\'');
        }        
        if(!empty($this->_order_id)){
            $this->db->where('o.order_id', $this->_order_id);
        }        
        if(!empty($this->_name)){            
            $this->db->like('o.name', $this->_name, 'both');
        }       
        $this->db->order_by('o.order_date', 'DESC');
        $query = $this->db->get();
        return $query->result_array();
    }

}
?>

Step 3: Create Controller
Create a controller file like Order.php inside “application/controllers” folder.

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Order Controller
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Order extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model('Site_model', 'site');
    }
    // upload xlsx|xls file
    public function index() {
        $data['page'] = 'order';
        $data['title'] = 'Data Table | TechArise';
        $this->load->view('order/index', $data);
    }
    // get Orders List
    public function getOrderList() {    
        $orderID = $this->input->post('order_id');
        $name = $this->input->post('name');
        $startDate = $this->input->post('start_date');
        $endDate = $this->input->post('end_date');        
        if(!empty($orderID)){
            $this->site->setOrderID($orderID);
        }        
        if(!empty($name)){
            $this->site->setName($name);
        }                
        if(!empty($startDate) && !empty($endDate)) {
            $this->site->setStartDate(date('Y-m-d', strtotime($startDate)));
            $this->site->setEndDate(date('Y-m-d', strtotime($endDate)));
        }        
        $getOrderInfo = $this->site->getOrders();
        $dataArray = array();
        foreach ($getOrderInfo as $element) {            
            $dataArray[] = array(
                $element['order_id'],
                date(DATE_FORMAT_SIMPLE, $element['order_date']),
                $element['name'],
                $element['city'],
                $element['amount'],
                $element['status'],
            );
        }
        echo json_encode(array("data" => $dataArray));
    }

}
?>

Step 4: Include Library
We will Include jQuery Datatable and jQuery Library files in header

Step 5: Create views
Create a views file named index.php inside “application/views/export” folder.

$this->load->view('templates/header');
?>

    

        
DataTable using Codeigniter, MySQL and AJAX
                 
    


   
            
                                        
                

                    
                
            
 
            
   
                

                     
                
            
            

                

                    
                
            
            

                

                    
                
 
            
            
                                      
                

                    
                
            
                                        
        
        

        
      
        

        
        
        
    
    

    
       
         Tutorial          
    

$this->load->view('templates/footer');
?>

Step 6: Create JavaScript file with
Create a views file named common.js inside “assets/js” folder.

Demo  [sociallocker] Download[/sociallocker]