(Codeigniter) Ajax CRUD using Bootstrap modals and DataTables
Screenshot :
Ajax CRUD DataTables
Bootstrap Modals (Adding data)
Bootstrap Modals (Editing data)
Required and Included on this source :
- PHP+ MySQL or you may use XAMPP –> Download
- Codeigniter 3.0 –> Download
- jQuery 2.1.4 –> Download
- Twitter Bootstrap 3.3.5 –> Download
- Bootstrap-datepicker 1.4.0 –> Download
- DataTables 1.10.7 –> Download
Database and Query :
[code hl="1, 4, 7"]
create database crud;
use crud;
CREATE TABLE `persons` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`firstName` varchar(100) DEFAULT NULL,
`lastName` varchar(100) DEFAULT NULL,
`gender` enum('male','female') DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`dob` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- example data persons
INSERT INTO `persons` (`id`, `firstName`, `lastName`, `gender`, `address`, `dob`)
VALUES
(1, 'Airi', 'Satou', 'female', 'Tokyo', '1964-03-04'),
(2, 'Garrett', 'Winters', 'male', 'Tokyo', '1988-09-02'),
(3, 'John', 'Doe', 'male', 'Kansas', '1972-11-02'),
(4, 'Tatyana', 'Fitzpatrick', 'male', 'London', '1989-01-01'),
(5, 'Quinn', 'Flynn', 'male', 'Edinburgh', '1977-03-24');
[/code]
Configuring Codeigniter :
assets folder structure :
Assets Folder Codeigniter
path : config/route.php
[code hl="1, 4, 7"]
$route['default_controller'] = 'person';
$route['404_override'] = '';
$route['translate_uri_dashes'] = FALSE;
[/code]
Base URL Cofig (required if using Codeigniter 3.0.3 or later)
path : config/config.php
see for dynamic base_url : Dynamic base_url() and site_url() Codeigniter 3.0.3 +
in this source leave empty because using codeigniter 3.0
Source Code
model : Person_model.phppath : application/models/Person_model.php
[code hl="1, 4, 7"]
'desc'); // default order
public function __construct()
{
parent::__construct();
$this->load->database();
}
private function _get_datatables_query()
{
$this->db->from($this->table);
$i = 0;
foreach ($this->column_search as $item) // loop column
{
if($_POST['search']['value']) // if datatable send POST for search
{
if($i===0) // first loop
{
$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->like($item, $_POST['search']['value']);
}
else
{
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if(isset($_POST['order'])) // here order processing
{
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}
else if(isset($this->order))
{
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
function get_datatables()
{
$this->_get_datatables_query();
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}
function count_filtered()
{
$this->_get_datatables_query();
$query = $this->db->get();
return $query->num_rows();
}
public function count_all()
{
$this->db->from($this->table);
return $this->db->count_all_results();
}
public function get_by_id($id)
{
$this->db->from($this->table);
$this->db->where('id',$id);
$query = $this->db->get();
return $query->row();
}
public function save($data)
{
$this->db->insert($this->table, $data);
return $this->db->insert_id();
}
public function update($where, $data)
{
$this->db->update($this->table, $data, $where);
return $this->db->affected_rows();
}
public function delete_by_id($id)
{
$this->db->where('id', $id);
$this->db->delete($this->table);
}
}
[/code]
Controller Person.php
path : applications/controllers/Person.php
[code hl="1, 4, 7"]
load->model('person_model','person');
}
public function index()
{
$this->load->helper('url');
$this->load->view('person_view');
}
public function ajax_list()
{
$list = $this->person->get_datatables();
$data = array();
$no = $_POST['start'];
foreach ($list as $person) {
$no++;
$row = array();
$row[] = $person->firstName;
$row[] = $person->lastName;
$row[] = $person->gender;
$row[] = $person->address;
$row[] = $person->dob;
//add html for action
$row[] = 'id."'".')"> Edit
id."'".')"> Delete';
$data[] = $row;
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->person->count_all(),
"recordsFiltered" => $this->person->count_filtered(),
"data" => $data,
);
//output to json format
echo json_encode($output);
}
public function ajax_edit($id)
{
$data = $this->person->get_by_id($id);
echo json_encode($data);
}
public function ajax_add()
{
$data = array(
'firstName' => $this->input->post('firstName'),
'lastName' => $this->input->post('lastName'),
'gender' => $this->input->post('gender'),
'address' => $this->input->post('address'),
'dob' => $this->input->post('dob'),
);
$insert = $this->person->save($data);
echo json_encode(array("status" => TRUE));
}
public function ajax_update()
{
$data = array(
'firstName' => $this->input->post('firstName'),
'lastName' => $this->input->post('lastName'),
'gender' => $this->input->post('gender'),
'address' => $this->input->post('address'),
'dob' => $this->input->post('dob'),
);
$this->person->update(array('id' => $this->input->post('id')), $data);
echo json_encode(array("status" => TRUE));
}
public function ajax_delete($id)
{
$this->person->delete_by_id($id);
echo json_encode(array("status" => TRUE));
}
}
[/code]
view : person_view.php
path : application/views/person_view.php
[code hl="1, 4, 7"]
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Ajax CRUD with Bootstrap modals and Datatables</title>
<link href="<?php echo base_url('assets/bootstrap/css/bootstrap.min.css')?>" rel="stylesheet">
<link href="<?php echo base_url('assets/datatables/css/dataTables.bootstrap.css')?>" rel="stylesheet">
<link href="<?php echo base_url('assets/bootstrap-datepicker/css/bootstrap-datepicker3.min.css')?>" rel="stylesheet">
<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body>
<div class="container">
<h1 style="font-size:20pt">Ajax CRUD with Bootstrap modals and Datatables</h1>
<h3>Person Data</h3>
<br />
<button class="btn btn-success" onclick="add_person()"><i class="glyphicon glyphicon-plus"></i> Add Person</button>
<button class="btn btn-default" onclick="reload_table()"><i class="glyphicon glyphicon-refresh"></i> Reload</button>
<br />
<br />
<table id="table" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Address</th>
<th>Date of Birth</th>
<th style="width:125px;">Action</th>
</tr>
</thead>
<tbody>
</tbody>
<tfoot>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Address</th>
<th>Date of Birth</th>
<th>Action</th>
</tr>
</tfoot>
</table>
</div>
<script src="<?php echo base_url('assets/jquery/jquery-2.1.4.min.js')?>"></script>
<script src="<?php echo base_url('assets/bootstrap/js/bootstrap.min.js')?>"></script>
<script src="<?php echo base_url('assets/datatables/js/jquery.dataTables.min.js')?>"></script>
<script src="<?php echo base_url('assets/datatables/js/dataTables.bootstrap.js')?>"></script>
<script src="<?php echo base_url('assets/bootstrap-datepicker/js/bootstrap-datepicker.min.js')?>"></script>
<script type="text/javascript">
var save_method; //for save method string
var table;
$(document).ready(function() {
//datatables
table = $('#table').DataTable({
"processing": true, //Feature control the processing indicator.
"serverSide": true, //Feature control DataTables' server-side processing mode.
"order": [], //Initial no order.
// Load data for the table's content from an Ajax source
"ajax": {
"url": "<?php echo site_url('person/ajax_list')?>",
"type": "POST"
},
//Set column definition initialisation properties.
"columnDefs": [
{
"targets": [ -1 ], //last column
"orderable": false, //set not orderable
},
],
});
//datepicker
$('.datepicker').datepicker({
autoclose: true,
format: "yyyy-mm-dd",
todayHighlight: true,
orientation: "top auto",
todayBtn: true,
todayHighlight: true,
});
});
function add_person()
{
save_method = 'add';
$('#form')[0].reset(); // reset form on modals
$('.form-group').removeClass('has-error'); // clear error class
$('.help-block').empty(); // clear error string
$('#modal_form').modal('show'); // show bootstrap modal
$('.modal-title').text('Add Person'); // Set Title to Bootstrap modal title
}
function edit_person(id)
{
save_method = 'update';
$('#form')[0].reset(); // reset form on modals
$('.form-group').removeClass('has-error'); // clear error class
$('.help-block').empty(); // clear error string
//Ajax Load data from ajax
$.ajax({
url : "<?php echo site_url('person/ajax_edit/')?>/" + id,
type: "GET",
dataType: "JSON",
success: function(data)
{
$('[name="id"]').val(data.id);
$('[name="firstName"]').val(data.firstName);
$('[name="lastName"]').val(data.lastName);
$('[name="gender"]').val(data.gender);
$('[name="address"]').val(data.address);
$('[name="dob"]').datepicker('update',data.dob);
$('#modal_form').modal('show'); // show bootstrap modal when complete loaded
$('.modal-title').text('Edit Person'); // Set title to Bootstrap modal title
},
error: function (jqXHR, textStatus, errorThrown)
{
alert('Error get data from ajax');
}
});
}
function reload_table()
{
table.ajax.reload(null,false); //reload datatable ajax
}
function save()
{
$('#btnSave').text('saving...'); //change button text
$('#btnSave').attr('disabled',true); //set button disable
var url;
if(save_method == 'add') {
url = "<?php echo site_url('person/ajax_add')?>";
} else {
url = "<?php echo site_url('person/ajax_update')?>";
}
// ajax adding data to database
$.ajax({
url : url,
type: "POST",
data: $('#form').serialize(),
dataType: "JSON",
success: function(data)
{
if(data.status) //if success close modal and reload ajax table
{
$('#modal_form').modal('hide');
reload_table();
}
$('#btnSave').text('save'); //change button text
$('#btnSave').attr('disabled',false); //set button enable
},
error: function (jqXHR, textStatus, errorThrown)
{
alert('Error adding / update data');
$('#btnSave').text('save'); //change button text
$('#btnSave').attr('disabled',false); //set button enable
}
});
}
function delete_person(id)
{
if(confirm('Are you sure delete this data?'))
{
// ajax delete data to database
$.ajax({
url : "<?php echo site_url('person/ajax_delete')?>/"+id,
type: "POST",
dataType: "JSON",
success: function(data)
{
//if success reload ajax table
$('#modal_form').modal('hide');
reload_table();
},
error: function (jqXHR, textStatus, errorThrown)
{
alert('Error deleting data');
}
});
}
}
</script>
<!-- Bootstrap modal -->
<div class="modal fade" id="modal_form" role="dialog">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h3 class="modal-title">Person Form</h3>
</div>
<div class="modal-body form">
<form action="#" id="form" class="form-horizontal">
<input type="hidden" value="" name="id"/>
<div class="form-body">
<div class="form-group">
<label class="control-label col-md-3">First Name</label>
<div class="col-md-9">
<input name="firstName" placeholder="First Name" class="form-control" type="text">
<span class="help-block"></span>
</div>
</div>
<div class="form-group">
<label class="control-label col-md-3">Last Name</label>
<div class="col-md-9">
<input name="lastName" placeholder="Last Name" class="form-control" type="text">
<span class="help-block"></span>
</div>
</div>
<div class="form-group">
<label class="control-label col-md-3">Gender</label>
<div class="col-md-9">
<select name="gender" class="form-control">
<option value="">--Select Gender--</option>
<option value="male">Male</option>
<option value="female">Female</option>
</select>
<span class="help-block"></span>
</div>
</div>
<div class="form-group">
<label class="control-label col-md-3">Address</label>
<div class="col-md-9">
<textarea name="address" placeholder="Address" class="form-control"></textarea>
<span class="help-block"></span>
</div>
</div>
<div class="form-group">
<label class="control-label col-md-3">Date of Birth</label>
<div class="col-md-9">
<input name="dob" placeholder="yyyy-mm-dd" class="form-control datepicker" type="text">
<span class="help-block"></span>
</div>
</div>
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" id="btnSave" onclick="save()" class="btn btn-primary">Save</button>
<button type="button" class="btn btn-danger" data-dismiss="modal">Cancel</button>
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->
<!-- End Bootstrap modal -->
</body>
</html>
[/code]
sumber
