Our social:

Latest Post

Rabu, 07 Februari 2018

(Codeigniter) Ajax CRUD using Bootstrap modals and DataTables

Screenshot :

Ajax CRUD Datatable
Ajax CRUD DataTables
Bootstrap Modals (Adding data)
Bootstrap Modals (Adding data)
ajax crud bootstrap modals datatable edit
Bootstrap Modals (Editing data)

Required and Included on this source :

  1. PHP+ MySQL or you may use XAMPP –> Download
  2. Codeigniter 3.0 –> Download
  3. jQuery 2.1.4 –> Download
  4. Twitter Bootstrap 3.3.5 –> Download
  5. Bootstrap-datepicker 1.4.0 –> Download
  6. 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 :
ajax crud bootstrap datatable assets
Assets Folder Codeigniter
Routing

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.php
path : 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">&times;</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