Hire a web Developer and Designer to upgrade and boost your online presence with cutting edge Technologies

Wednesday, May 3, 2023

DataTables Add Edit Delete with CodeIgniter

 

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

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

 

Posted by H at 7:45 AM

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)