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:
Step 2: Create Model
Create a model file named Site_model.php inside “application/models” folder.
Step 3: Create Controller
Create a controller file like Order.php inside “application/controllers” folder.
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.
Step 6: Create JavaScript file with
Create a views file named common.js inside “assets/js” folder.
Demo [sociallocker] Download[/sociallocker]
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
$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]