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 tables into a useful grid layouts. 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<?php 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<?php /** * 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
<?php /** * 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.
|
1 2 3 4 5 6 |
<!-- DataTables CSS --> <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"> <!-- jQuery --> <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-1.12.4.js"></script> <!-- DataTables --> <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script> |
Step 5: Create views
Create a views file named index.php inside “application/views/export” folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
<?php $this->load->view('templates/header'); ?> <div class="row"> <div class="col-lg-12"> <h2>DataTable using Codeigniter, MySQL and AJAX</h2> </div> </div><!-- /.row --> <div class="row"> <div class="col-lg-2"> <div class="form-group"> <input type="text" name="order_id" value="" class="form-control" id="filter-order-no" placeholder="Order No"> </div> </div> <div class="col-lg-2"> <div class="form-group"> <input type="text" name="name" value="" class="form-control" id="filter-name" placeholder="Name"> </div> </div> <div class="col-lg-3"> <div class="form-group"> <input type="text" name="order_start_date" value="" class="form-control getDatePicker" id="order-start-date" placeholder="Start date"> </div> </div> <div class="col-lg-3"> <div class="form-group"> <input type="text" name="order_end_date" value="" class="form-control getDatePicker" id="order-end-date" placeholder="End date"> </div> </div> <div class="col-lg-2"> <div class="form-group"> <button name="filter_order_filter" type="button" class="btn btn-primary btn-block" id="filter-order-filter" value="filter"><i class="fa fa-search fa-fw"></i></button> </div> </div> </div> <div class="row"> <div class="col-lg-12"> <div id="render-list-of-order"> </div> </div> </div> <div class="row"> <div class="col-lg-12"> <a class="btn btn-info btn-xs" style="margin: 2px" href="https://techarise.com/"><i class="fa fa-mail-reply"></i> Tutorial</a> </div> </div><!-- /.row --> <?php $this->load->view('templates/footer'); ?> |
Step 6: Create JavaScript file with
Create a views file named common.js inside “assets/js” folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
<script> // render order list data table // default render page jQuery(document).ready(function() { var data = {order_id:"", name:"", startDate: "", endDate: ""}; generateOrderTable(data); }); // render date datewise jQuery(document).on('click','#filter-order-filter', function(){ var order_id = jQuery('input#filter-order-no').val(); var name = jQuery('input#filter-name').val(); var startDate = jQuery('input#order-start-date').val(); var endDate = jQuery('input#order-end-date').val(); var data = {order_id:order_id, name:name, startDate:startDate, endDate:endDate}; generateOrderTable(data); }); // generate Order Table function generateOrderTable(element){ jQuery.ajax({ url: baseurl + 'order/getOrderList', data: {'order_id' : element.order_id , 'name' : element.name, 'start_date' : element.startDate , 'end_date' : element.endDate}, type: 'post', dataType: 'json', beforeSend: function () { jQuery('#render-list-of-order').html('<div class="text-center mrgA padA"><i class="fa fa-spinner fa-pulse fa-4x fa-fw"></i></div>'); }, success: function (html) { var dataTable='<table id="order-datatable" class="table table-striped" cellspacing="0" width="100%"></table>'; jQuery('#render-list-of-order').html(dataTable); var table = $('#order-datatable').DataTable({ data: html.data, "bPaginate": true, "bLengthChange": true, "bFilter": false, "bInfo": true, "bAutoWidth": true, columns: [ { title: "Order No", "width": "12%"}, { title: "Date.", "width": "16%"}, { title: "Name", "width": "17%"}, { title: "Amount", "width": "15%"}, { title: "Status", "width": "15%"} ], }); } }); } </script> |