DataTables is a popular Query JavaScript library to create dynamic HTML table with advance features like pagination, sorting, search etc.
In our previous tutorial about Datatables Add Edit Delete with Ajax, PHP & MySQL and get huge response from our readers. Many of them requested for the tutorial to implement DataTables with CodeIgniter. So here in this tutorial, you will learn how to implement Datatables Add Edit Delete with CodeIgniter.
We will cover tutorial step by step to handle DataTables server side processing with CodeIgniter to display records. We will implement functionality to add new record and display in DataTables. We will also implement functionality to edit and delete records from DataTables and refresh Datatables without page reload.
Also, read:
- CodeIgniter Tutorial for Beginners
- Upload Multiple Files in CodeIgniter
- Ajax Pagination in CodeIgniter with Example
- Form Validation in CodeIgniter with Example
- Ajax CRUD Operation in CodeIgniter with Example
- Export Data to Excel with PhpSpreadsheet using CodeIgniter
- Create RESTful API using CodeIgniter
Before going through this tutorial, we hope that you have setup your CodeIgniter project with database connection details to use with this example. So let’s start
Step1: Create MySQL Database Table
As we will implement DataTables with CodeIgniter to display staff data. So first we will create MySQL database table staff to store staffs records.
CREATE TABLE `staff` ( `id` int(11) NOT NULL COMMENT 'primary key', `name` varchar(255) NOT NULL COMMENT 'staff name', `email` varchar(255) NOT NULL COMMENT 'Email Address', `mobile` varchar(16) DEFAULT NULL, `address` text DEFAULT NULL, `salary` float(10,2) NOT NULL COMMENT 'staff salary' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `staff` ADD PRIMARY KEY (`id`); ALTER TABLE `staff` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key';
We will also insert few records using below insert statement.
INSERT INTO `staff` (`id`, `name`, `email`, `mobile`, `address`, `salary`) VALUES (1, 'Samson', 'samson@webdamn.com', '1234567890', 'London', 457002.00), (2, 'Jhon', 'jhon@webdamn.com', '1234567890', 'Paris', 456003.00), (3, 'Carl', 'Carl@webdamn.com', '1234567890', 'Newyork', 678002.00), (4, 'Boriss', 'Boriss@webdamn.com', '1234567890', 'Washington', 345003.00), (5, 'Ryan', 'Ryan@webdamn.com', '1234567890', 'Toronto', 876002.00), (6, 'Hokins', 'Hokins@webdamn.com', '1234567890', 'Sydney', 316003.00), (7, 'Marsh', 'Marsh@webdamn.com', '1234567890', 'Melbourne', 456002.00), (8, 'Jack', 'Jack@webdamn.com', '1234567890', 'Captown', 876003.00), (9, 'Iyan', 'Iyan@webdamn.com', '1234567890', 'Wellington', 345003.00), (10, 'Tare', 'tare@webdamn.com', '1234567890', 'Bankok', 654001.00), (11, 'Oley', 'ole@webdamn.com', '1234567890', 'Tokyo', 543001.00), (12, 'Ash', 'ash@webdamn.com', '1234567890', 'Delhi', 538001.00), (13, 'Haddin', 'Haddin@webdamn.com', '1234567890', 'Mumbai', 653001.00), (14, 'Graham', 'Graham@webdamn.com', '1234567890', 'New York', 941001.00), (15, 'Sam', 'Sam@webdamn.com', '1234567890', 'London', 453001.00), (16, 'Seal', 'Seal@webdamn.com', '1234567890', 'Sydney', 568002.00), (17, 'Ervine', 'Ervine@webdamn.com', '1234567890', 'Wellington', 169003.00), (18, 'Mark', 'Mark@webdamn.com', '1234567890', 'Washington DC', 216002.00), (19, 'Tim', 'tim@webdamn.com', '1234567890', 'Amsterdam', 563003.00), (20, 'Chaplin', 'Chaplin@webdamn.com', '1234567890', 'Madrid', 509002.00), (21, 'Charley', 'Charley@webdamn.com', '1234567890', 'Newyork', 768003.00);
Step2: Implement Model
We will create model file Staff_model.php in application/models directory and implement method getStaffList(), createStaff(), updateStaff() and deleteStaff() to handle functionality. Following is the complete code of Staff_model.php model.
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Staff_model extends CI_Model {
private $_staffID;
private $_name;
private $_email;
private $_address;
private $_salary;
private $_mobile;
public function setStaffID($staffID) {
$this->_staffID = $staffID;
}
public function setName($name) {
$this->_name = $name;
}
public function setEmail($email) {
$this->_email = $email;
}
public function setAddress($address) {
$this->_address = $address;
}
public function setSalary($salary) {
$this->_salary = $salary;
}
public function setMobile($mobile) {
$this->_mobile = $mobile;
}
var $table = 'staff';
var $column_order = array(null, 's.name','s.email','s.mobile','s.address','s.salary');
var $column_search = array('s.name','s.email','s.mobile','s.address','s.salary');
var $order = array('id' => 'DESC');
private function getQuery(){
if(!empty($this->input->post('name'))){
$this->db->like('s.name', $this->input->post('name'), 'both');
}
if(!empty($this->input->post('email'))){
$this->db->like('s.email', $this->input->post('email'), 'both');
}
if(!empty($this->input->post('mobile'))){
$this->db->like('s.mobile', $this->input->post('mobile'), 'both');
}
if(!empty($this->input->post('address'))){
$this->db->like('s.address', $this->input->post('address'), 'both');
}
$this->db->select(array('s.id', 's.name','s.email','s.mobile','s.address','s.salary'));
$this->db->from('staff as s');
$i = 0;
foreach ($this->column_search as $item){
if(!empty($_POST['search']['value'])){
if($i===0){
$this->db->group_start();
$this->db->like($item, $_POST['search']['value']);
} else {
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i)
$this->db->group_end();
}
$i++;
}
if(!empty($_POST['order'])){
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
} else if(!empty($this->order)){
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
public function getStaffList() {
$this->getQuery();
if(!empty($_POST['length']) && $_POST['length'] < 1) {
$_POST['length']= '10';
} else {
$_POST['length']= $_POST['length'];
}
if(!empty($_POST['start']) && $_POST['start'] > 1) {
$_POST['start']= $_POST['start'];
}
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result_array();
}
public function countFiltered(){
$this->getQuery();
$query = $this->db->get();
return $query->num_rows();
}
public function countAll(){
$this->db->from($this->table);
return $this->db->count_all_results();
}
public function createStaff() {
$data = array(
'name' => $this->_name,
'email' => $this->_email,
'address' => $this->_address,
'mobile' => $this->_mobile,
'salary' => $this->_salary,
);
$this->db->insert('staff', $data);
return $this->db->insert_id();
}
public function updateStaff() {
$data = array(
'name' => $this->_name,
'email' => $this->_email,
'address' => $this->_address,
'mobile' => $this->_mobile,
'salary' => $this->_salary,
);
$this->db->where('id', $this->_staffID);
$this->db->update('staff', $data);
}
public function getStaff() {
$this->db->select(array('s.id', 's.name', 's.email', 's.address', 's.mobile', 's.salary'));
$this->db->from('staff s');
$this->db->where('s.id', $this->_staffID);
$query = $this->db->get();
return $query->row_array();
}
public function deleteStaff() {
$this->db->where('id', $this->_staffID);
$this->db->delete('staff');
}
public function validateEmail($email) {
return preg_match('/^[^\@]+@.*.[a-z]{2,15}$/i', $email)?TRUE:FALSE;
}
public function validateMobile($mobile){
return preg_match('/^[0-9]{10}+$/', $mobile)?TRUE:FALSE;
}
}
Step3: Implement Controllers
We will create controllers file Staff.php in application/controllers directory to load Model and Model method. We will implement method getStaffListing() to return staff data as JSON to load staff list into DataTables. We will implement method save()(), update() and delete to return JSON to handle functionality. Here is the completed code from Staff.php controllers file.
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Staff extends CI_Controller {
public function __construct() {
parent::__construct();
$this->load->model('Staff_model', 'staff');
}
public function index() {
$data['page'] = 'staff-list';
$data['title'] = 'Datatables Add Edit Delete with CodeIgniter and Ajax';
$this->load->view('staff/index', $data);
}
public function getStaffListing(){
$json = array();
$list = $this->staff->getStaffList();
$data = array();
foreach ($list as $element) {
$row = array();
$row[] = $element['id'];
$row[] = $element['name'];
$row[] = $element['email'];
$row[] = $element['mobile'];
$row[] = $element['address'];
$row[] = $element['salary'];
$data[] = $row;
}
$json['data'] = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->staff->countAll(),
"recordsFiltered" => $this->staff->countFiltered(),
"data" => $data,
);
$this->output->set_header('Content-Type: application/json');
echo json_encode($json['data']);
}
public function save() {
$json = array();
$name = $this->input->post('name');
$email = $this->input->post('email');
$address = $this->input->post('address');
$mobile = $this->input->post('mobile');
$salary = $this->input->post('salary');
if(empty(trim($name))){
$json['error']['name'] = 'Please enter name';
}
if(empty(trim($email))){
$json['error']['email'] = 'Please enter email address';
}
if ($this->staff->validateEmail($email) == FALSE) {
$json['error']['email'] = 'Please enter valid email address';
}
if(empty($address)){
$json['error']['address'] = 'Please enter address';
}
if($this->staff->validateMobile($mobile) == FALSE) {
$json['error']['mobile'] = 'Please enter valid mobile no';
}
if(empty($salary)){
$json['error']['salary'] = 'Please enter salary';
}
if(empty($json['error'])){
$this->staff->setName($name);
$this->staff->setEmail($email);
$this->staff->setAddress($address);
$this->staff->setSalary($salary);
$this->staff->setMobile($mobile);
try {
$last_id = $this->staff->createStaff();
} catch (Exception $e) {
var_dump($e->getMessage());
}
if (!empty($last_id) && $last_id > 0) {
$staffID = $last_id;
$this->staff->setStaffID($staffID);
$staffInfo = $this->staff->getStaff();
$json['staff_id'] = $staffInfo['id'];
$json['name'] = $staffInfo['name'];
$json['email'] = $staffInfo['email'];
$json['address'] = $staffInfo['address'];
$json['mobile'] = $staffInfo['mobile'];
$json['salary'] = $staffInfo['salary'];
$json['status'] = 'success';
}
}
$this->output->set_header('Content-Type: application/json');
echo json_encode($json);
}
public function edit() {
$json = array();
$staffID = $this->input->post('staff_id');
$this->staff->setStaffID($staffID);
$json['staffInfo'] = $this->staff->getStaff();
$this->output->set_header('Content-Type: application/json');
$this->load->view('staff/popup/renderEdit', $json);
}
public function update() {
$json = array();
$staff_id = $this->input->post('staff_id');
$name = $this->input->post('name');
$email = $this->input->post('email');
$address = $this->input->post('address');
$mobile = $this->input->post('mobile');
$salary = $this->input->post('salary');
if(empty(trim($name))){
$json['error']['name'] = 'Please enter name';
}
if(empty(trim($email))){
$json['error']['email'] = 'Please enter email address';
}
if ($this->staff->validateEmail($email) == FALSE) {
$json['error']['email'] = 'Please enter valid email address';
}
if(empty($address)){
$json['error']['address'] = 'Please enter address';
}
if($this->staff->validateMobile($mobile) == FALSE) {
$json['error']['mobile'] = 'Please enter valid mobile no';
}
if(empty($salary)){
$json['error']['salary'] = 'Please enter salary';
}
if(empty($json['error'])){
$this->staff->setStaffID($staff_id);
$this->staff->setName($name);
$this->staff->setEmail($email);
$this->staff->setAddress($address);
$this->staff->setSalary($salary);
$this->staff->setMobile($mobile);
try {
$last_id = $this->staff->updateStaff();;
} catch (Exception $e) {
var_dump($e->getMessage());
}
if (!empty($staff_id) && $staff_id > 0) {
$this->staff->setStaffID($staff_id);
$staffInfo = $this->staff->getStaff();
$json['staff_id'] = $staffInfo['id'];
$json['name'] = $staffInfo['name'];
$json['email'] = $staffInfo['email'];
$json['address'] = $staffInfo['address'];
$json['mobile'] = $staffInfo['mobile'];
$json['salary'] = $staffInfo['salary'];
$json['status'] = 'success';
}
}
$this->output->set_header('Content-Type: application/json');
echo json_encode($json);
}
public function display() {
$json = array();
$staffID = $this->input->post('staff_id');
$this->staff->setStaffID($staffID);
$json['staffInfo'] = $this->staff->getStaff();
$this->output->set_header('Content-Type: application/json');
$this->load->view('staff/popup/renderDisplay', $json);
}
public function delete() {
$json = array();
$staffID = $this->input->post('staff_id');
$this->staff->setStaffID($staffID);
$this->staff->deleteStaff();
$this->output->set_header('Content-Type: application/json');
echo json_encode($json);
}
}
Step4: Implement Views
We will create views file index.php in application/views directory to display staff records.
<div class="container"> <div class="pb-2 mt-4 mb-2 border-bottom"> <h2>Example: Datatables Add Edit Delete with CodeIgniter and Ajax</h2> </div> <div class="row"> <div class="col-lg-12"><span id="success-msg"></div> </div> <div class="row"> <div class="col-lg-12"> <a href="javascript:void(0);" data-toggle="modal" data-target="#add-staff" class="float-right btn btn-primary btn-sm" style="margin: 4px;"><i class="fa fa-plus"></i> Add</a> </div> </div> <div class="row"> </div> <div class=""> <table id="staffListing" class="table table-bordered table-hover small"> <thead> <tr> <th scope="col">#</th> <th scope="col">Name</th> <th scope="col">Email</th> <th scope="col">Contact No</th> <th scope="col">Address</th> <th scope="col">Salary</th> <th scope="col">Action</th> </tr> </thead> <tbody> </tbody> </table> </div> </div>
We will make Ajax request to load staff records into DataTables.
jQuery('#staffListing').dataTable({
"lengthChange": false,
"paging": true,
"processing": false,
"serverSide": true,
"order": [],
"ajax": {
"url": baseurl+"staff/getStaffListing",
"type": "POST"
},
"columns": [
{
"bVisible": false, "aTargets": [0]
},
null,
null,
null,
null,
null,
{
mRender: function (data, type, row) {
var bindHtml = '';
bindHtml += '<a data-toggle="modal" data-target="#dispaly-staff" href="javascript:void(0);" title="View staff" class="display-staff ml-1 btn-ext-small btn btn-sm btn-info" data-staffid="' + row[0] + '"><i class="fas fa-eye"></i></a>';
bindHtml += '<a data-toggle="modal" data-target="#update-staff" href="javascript:void(0);" title="Edit Staff" class="update-staff-details ml-1 btn-ext-small btn btn-sm btn-primary" data-staffid="' + row[0] + '"><i class="fas fa-edit"></i></a>';
bindHtml += '<a data-toggle="modal" data-target="#delete-staff" href="javascript:void(0);" title="Delete Stff" class="delete-staff-details ml-1 btn-ext-small btn btn-sm btn-danger" data-staffid="' + row[0] + '"><i class="fas fa-times"></i></a>';
return bindHtml;
}
},
],
"fnCreatedRow": function( nRow, aData, iDataIndex ) {
$(nRow).attr('id', aData[0]);
}
});
Step5: Implement DataTables Records Add, Edit, Delete
We will create a JavaScript file custom.js and implement DataTables add, edit, delete functionality with Ajax.
jQuery(document).on('click', 'button#add-staff', function(){
jQuery.ajax({
type:'POST',
url:baseurl+'staff/save',
data:jQuery("form#add-staff-form").serialize(),
dataType:'json',
beforeSend: function () {
jQuery('button#add-staff').button('loading');
},
complete: function () {
jQuery('button#add-staff').button('reset');
setTimeout(function () {
jQuery('span#success-msg').html('');
}, 5000);
},
success: function (json) {
$('.text-danger').remove();
if (json['error']) {
for (i in json['error']) {
var element = $('.input-staff-' + i.replace('_', '-'));
if ($(element).parent().hasClass('input-group')) {
$(element).parent().after('<div class="text-danger" style="font-size: 14px;">' + json['error'][i] + '</div>');
} else {
$(element).after('<div class="text-danger" style="font-size: 14px;">' + json['error'][i] + '</div>');
}
}
} else {
jQuery('span#success-msg').html('<div class="alert alert-success">Record added successfully.</div>');
jQuery('#staffListing').DataTable().ajax.reload();
jQuery('form#add-staff-form').find('textarea, input').each(function () {
jQuery(this).val('');
});
jQuery('#add-staff').modal('hide');
}
},
error: function (xhr, ajaxOptions, thrownError) {
console.log(thrownError + "\r\n" + xhr.statusText + "\r\n" + xhr.responseText);
}
});
});
jQuery(document).on('click', 'button#update-staff', function(){
jQuery.ajax({
type:'POST',
url:baseurl+'staff/update',
data:jQuery("form#update-staff-form").serialize(),
dataType:'json',
beforeSend: function () {
jQuery('button#update-staff').button('loading');
},
complete: function () {
jQuery('button#update-staff').button('reset');
setTimeout(function () {
jQuery('span#success-msg').html('');
}, 5000);
},
success: function (json) {
$('.text-danger').remove();
if (json['error']) {
for (i in json['error']) {
var element = $('.input-staff-' + i.replace('_', '-'));
if ($(element).parent().hasClass('input-group')) {
$(element).parent().after('<div class="text-danger" style="font-size: 14px;">' + json['error'][i] + '</div>');
} else {
$(element).after('<div class="text-danger" style="font-size: 14px;">' + json['error'][i] + '</div>');
}
}
} else {
jQuery('span#success-msg').html('<div class="alert alert-success">Record updated successfully.</div>');
jQuery('#staffListing').DataTable().ajax.reload();
jQuery('form#update-staff-form').find('textarea, input').each(function () {
jQuery(this).val('');
});
jQuery('#update-staff').modal('hide');
}
},
error: function (xhr, ajaxOptions, thrownError) {
console.log(thrownError + "\r\n" + xhr.statusText + "\r\n" + xhr.responseText);
}
});
});
jQuery(document).on('click', 'button#delete-staff', function(){
var staff_id = jQuery('#staff_id').val();
jQuery.ajax({
type:'POST',
url:baseurl+'staff/delete',
data:{staff_id: staff_id},
dataType:'html',
success: function (html) {
jQuery('span#success-msg').html('');
jQuery('span#success-msg').html('<div class="alert alert-success">Deleted staff successfully.</div>');
jQuery('#staffListing').DataTable().ajax.reload();
jQuery('#delete-staff').modal('hide');
},
error: function (xhr, ajaxOptions, thrownError) {
console.log(thrownError + "\r\n" + xhr.statusText + "\r\n" + xhr.responseText);
}
});
});
You may also like:
- User Management System with PHP & MySQL
- Datatables Add Edit Delete with Ajax, PHP & MySQL
- Build Helpdesk System with jQuery, PHP & MySQL
- Build Online Voting System with PHP & MySQL
- School Management System with PHP & MySQL
- Build Reusable Captcha Script with PHP
- Product Search Filtering using Ajax, PHP & MySQL
- Image Upload and Crop in Modal with jQuery, PHP & MySQL
- Build Push Notification System with PHP & MySQL
- Project Management System with PHP and MySQL
- Hospital Management System with PHP & MySQL
- Build Newsletter System with PHP and MySQL
- Skeleton Screen Loading Effect with Ajax and PHP
- Build Discussion Forum with PHP and MySQL
- Customer Relationship Management (CRM) System with PHP & MySQL
- Online Exam System with PHP & MySQL
- Expense Management System with PHP & MySQL
You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download
Hi,
This code works perfectly find. Thanks. But I would love it if there was Ajax search functionality as well. More over if I could download the data in excel sheet.
How can we bulk edit multiple rows of datatables in codeigniter?
We are checking this and update with details, thanks!
brother Add item button is not working in demo but also in downloaded source code, please guide as soon as possible
I am checking this and update soon. thanks!