CodeIgniter, jQuery, MySQL

(Codeigniter) Ajax CRUD using Bootstrap modals and DataTables

Codeigniter Ajax CRUD using Bootstrap modals and DataTables Tutorial series :

  1. Ajax CRUD using Bootstrap modals and DataTables
  2. Ajax CRUD using Bootstrap modals and DataTables with server-side validation
  3. Ajax CRUD using Bootstrap modals and DataTables with image upload
  4. Ajax CRUD using Bootstrap modals and DataTables with Bulk Delete

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 :

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

 

Configuring Codeigniter :

assets folder structure :

ajax crud bootstrap datatable assets

Assets Folder Codeigniter

Routing

path : config/route.php

$route['default_controller'] = 'person';
$route['404_override'] = '';
$route['translate_uri_dashes'] = FALSE;

 

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

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Person_model extends CI_Model {

    var $table = 'persons';
    var $column_order = array('firstname','lastname','gender','address','dob',null); //set column field database for datatable orderable
    var $column_search = array('firstname','lastname','address'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    var $order = array('id' => '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);
    }


}

 

Controller Person.php

path : applications/controllers/Person.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Person extends CI_Controller {

	public function __construct()
	{
		parent::__construct();
		$this->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[] = '<a class="btn btn-sm btn-primary" href="javascript:void(0)" title="Edit" onclick="edit_person('."'".$person->id."'".')"><i class="glyphicon glyphicon-pencil"></i> Edit</a>
				  <a class="btn btn-sm btn-danger" href="javascript:void(0)" title="Hapus" onclick="delete_person('."'".$person->id."'".')"><i class="glyphicon glyphicon-trash"></i> Delete</a>';

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

}

 

view : person_view.php

path : application/views/person_view.php

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

 

Demo :

Preview Demo

Download Full Source Code :
Download








You Might Also Like

  • Muhamad Nasir

    bisa join antar table gak ?

    • Mbah Coding

      nggak masalah harusnya. cuma masalah query nya kalo itu 🙂

    • Mbah Coding

      nggak masalah harusnya. cuma masalah query nya kalo itu 🙂

      • Muhamad Nasir

        saya coba join ini scriptnya:
        $this->db->join(‘tbl_unit’,’tbl_logmaintenance.id_unit = tbl_unit.id_unit’,’LEFT’);

        tapi pas melakukan pencarian gak bisa dan pas ordering data dari table yang di join pun tidak bisa anyway itu kanapa ya ?

        • Muhamad Nasir

          Ternyata kalu pake nomor urut jadi error :/..
          kalau tidak pake, ternyata gak error 🙂

          Makasih Mas atas tutorialnya bagus banget 😀

    • rexy agustian

      mass ya…cara y gmn sih join table y?boleh minta kasih tau?:D

      • Muhamad Nasir

        ya join seperti biasa di codeigniter mas 😀

  • Dwi Marstya Wibowo

    Tutorial yang bagus, di tunggu bahasan selanjut nya 🙂

    Request bahasan untuk Login dan Find dengan beberapa Filter

    Thx

    • Mbah Coding

      oke thanks, kalo ada kesempatan menulis ditengah2 kesibukan :))

  • ijin download bang… 😀

    • Mbah Coding

      silahkan 🙂

  • aiko memory

    ini maksudnya apa ya ??
    $this->load->model(‘person_model’,’person’);

    saya lihat di model “person_model” sub “person” kok ga ada yak??

  • Federico

    Great tutorial, exactly what I was looking for my project. Thaks a lot

  • Federico

    I got in trouble trying to add this features into my app…I got a JSON error, any way to get some help? Thanks

    • Mbah Coding

      whats the problem?

      • Federico

        Fixed this issue Mbah…thx for feedback.

  • aiko memory

    Mbah… kolom action saya pindah ke kolom 0, kenapa kolom terakhir ketika saya klik sort muncul pesan error yak, padahah columnDefs -> target -1 sudah saya ganti menjadi 0..??

    • Mbah Coding

      di models colom var index 0 di null aja, urutannya dimulai dari index 1 misal:
      var $column = array(null, ‘firstname’,’lastname’,’gender’,’address’,’dob’);

      karna quering nya dari index 1, pada script :

      private function _get_datatables_query()
      {
      $this->db->from($this->table);
      $i = 0;

      foreach ($this->column as $item)
      {
      if($_POST[‘search’][‘value’])
      ($i===0) ? $this->db->like($item, $_POST[‘search’][‘value’]) : $this->db->or_like($item, $_POST[‘search’][‘value’]);
      $column[$i] = $item;
      $i++;
      }
      —————–
      scipt dibawah $this->db->from($this->table);
      $i = 0; diganti –> $i = 1;

      selamat mencoba 🙂

      • aiko memory

        Sudah saya edit menjadi “$column[$i+1] = $item;” malah error mbah..
        script saya adalah:
        var $table = ‘tj_pelanggan’;
        var $column = array(‘nama’,’npwp’,’alamat’);
        var $order = array(‘idpelanggan’ => ‘asc’);

        private function _get_datatables_query(){
        $this->db->from($this->table);
        $i = 0;

        foreach ($this->column as $item) {
        if($_POST[‘search’][‘value’])
        ($i===0) ? $this->db->like($item, $_POST[‘search’][‘value’]) : $this->db->or_like($item, $_POST[‘search’][‘value’]);
        $column[$i+1] = $item;
        $i++;
        }

        if(isset($_POST[‘order’])){
        $this->db->order_by($column[$_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)]);
        }
        }

        • aiko memory

          Jika saya tambah “null” pada script ini
          var $column = array(null,’nama’,’npwp’,’alamat’);
          kolom terakhir pada tabel bisa untuk proses sort tanpa mengganti
          $column[$i+1] = $item;

          Sekiranya ada solusi lain mohon bantuannya mbah….

        • Mbah Coding

          coba diteliti lagi, itu cuma masalah di column order

          $_POST[‘order’][‘0’][‘column’] –> POST data dari datatables index column saat kita klik.

          jadi code -> $column[$i+1] = $item; hanya variable menyimpan untuk dipanggil ke

          if(isset($_POST[‘order’])){
          $this->db->order_by($column[$_POST[‘order’][‘0’][‘column’]], $_POST[‘order’][‘0’][‘dir’]);
          }

          • aiko memory

            OK mbah thanks atas bantuannya, nanya lagi dunks mbah 🙂
            pada proses public function ajax_list() pada controller pada script

            public function ajax_list(){
            $list = $this->customer->get_datatables(‘tj_pelanggan’);

            }

            disitu saya tambahkan tabel “tj_pelanggan” ketika memanggil function model get_datatables. Dan pada model saya buat:

            function get_datatables($tbpelanggan){
            $this->_get_datatables_query($tbpelanggan);

            }

            Nah, pada function diatas saya juga menambahkan $tbpelanggan pada script $this->_get_datatables_query($tbpelanggan);, nilai $tbpelanggan diambil dari controller yg tadi. Namun ketika mau ekseskusi pada :

            private function _get_datatables_query($tabel){
            $this->db->from($tabel);

            }

            Proses menampilkan data tidak berjalan. Mohon bantuannya lagi yak mbah

  • James Wachira

    This is what i have been finding about. Thank you

  • gilang diaz

    mbah kalo eror nya kayak gini !!!!!!!

    DataTables warning: table id=table – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    ini yang salah apanya mbah
    saya coba nampilkan data malah EROR …
    saya gak terlalu ngerti AJAX mbah ….
    mohon bimbingannya

    • Mbah Coding

      duh maaf ya jarang cek blog hehe

      error json response ya. coba pake browser console (tekan ctrl+shift+j) lali klik kanan centang log request and response body. disana kliatan errornya knapa

  • Ramdan Kurniawan

    kang admin, kalo contoh modal form plus form_validation ada gak? terima kasih..

    • Mbah Coding

      maybe next tutorial hehe

  • Federico

    I need some help about the reload function. It seems not working on my project.Any hint to fix the issue?
    Thanks for help

    • Federico

      Got the reload function is working now…for some reason I cannot use the script into a separate file…I have to add the script at the end of the page

  • Federico

    Reload function issue fixed. Now I have a trouble with edit modal form…it doesn’t show the old values in input field…….and cannot edit the data in db. It seems that the data are undefined. Thanks for any feedback…

    • Federico

      Finally!! I got it working replacing return $query->row(); in get_by_id function in modal file with return $query->row_array();

      • Mbah Coding

        sorry. I dont know your error why? my code is work fine here. what php version are you using?

        • Federico

          On my server runs PHP 5.4. Any idea about the issue?

  • michael layangan

    mbah tolong jawab mbahhhhh…
    di fungsi ajax_list kok ada $this->person->getdatatables; nah tapi di folder model nggak ada file person.
    kenapa ya? tapi kok nggak error anehnya

  • Federico

    Mbah..I need your help please…your script is great…I’m using it estensively in my project….I have just a weird issue.
    In few list (not in all my controller/models) the search field and pagination are not aligned to the right column…any suggestions to fix this issue?
    Attached an image.
    Thanks a lot for any help

    • Mbah Coding

      It’s your css problem. Or maybe your templates bugs.

  • Fariz Yoga Syahputra

    Mbah, makasih banget nih share code nya, berguna banget. sekarang saya lagi ada ide buat bikin CRUD Generator, mudah mudahan bisa

    • Mbah Coding

      Sukses bro 😉

  • BlindBanditStory .

    Mbah, kalo DBnya pake Oracle bisa gak ya?
    Trus klo fungsinya dibuat hanya edit saja bisa gak ya?

    • Mbah Coding

      bisa saja. Codeigniter juga sudah support Oci8 Driver bawaannya untuk oracle connection.

      dibuat hanya edit, tinggal hapus aja function2 nya yang lain

      • changing base_url and removing index.php from url using htaccess stop the search bar loading and shows error “invalid json ..”

  • irfan mokoginta

    mbah, berguna banget code nya, sya ada beberapa masalah
    kalo kita mau buat dari lebih dari satu tabel yang akan insert gmana yah?
    trus kalo form modal yang bisa insert 2 tabel sekaligus
    contoh kasus form person dan form user(untuk login) dapat terisi bersamaan dengan 1 form yang berasal dari modal.
    maaf banyak nanya, masih newbie soalnya

    • Mbah Coding

      sangat bisa..
      tinggal mainin model nya contoh di code saya disini

      person_model.php
      public function save($data)
      {
      $this->db->insert($this->table, $data);
      return $this->db->insert_id();
      }

      insert nya dua kali. ga masalah. tinggal sesuaikan saja 🙂

      • irfan mokoginta

        makasih banyak mbah,,, sukses terus!!!

  • Novpritama

    Jika errornya “500 Internal Server Error” bagaimana penyelesaiannya ya mbah…

    • Mbah Coding

      dilihat saja response body nya. biasanya masih ada error. seperti syntax error. di ajax nya

      contoh kalo di firefox ini saya bikin error :

      • Novpritama

        Siiip mbah, kesalahan ditemukan pada penamaan variable column dan table nya.. 😀

  • Argo’Bel Arek Golek Belink

    kl seperti ini knp bos? class url tidak ditemukan, brrt harus buat function class gitu?

  • Argo’Bel Arek Golek Belink

    kl dklik add g muncul biasany kurang dimanany?

    • Argo’Bel Arek Golek Belink

      wkt add person di klik g ada respon sprt di demo…

      • Mbah Coding

        Bagian Javascriptnya mgkin dicek lagi 🙂

  • arta sitinjak

    mbah, script show dan searchnya ada dibagian view yg mana?

    • Mbah Coding

      itu bagian dari jquery datatable 🙂

  • tom

    mbah, nanya dong. kalo misal grid nya udah berhasil keload tapi ga bisa di order by ( saat klik header title ) dan ga bisa search itu kenapa ya mbah ? terus kenapa tampilan header order by nya ga keluar icon nya ya mbah ? gimana cara setting nya mbah, mohon petunjuk 😀 thanks

    • Mbah Coding

      itu kalau saya lihat pada tampilan mungkin ada css yg ga load. mgkin font nya bootstrap / font awesome.

      msalah error klik header, itu query column nya
      coba edit di model nya. contoh di script saya tinggal edit :
      var $column = array(‘firstname’,’lastname’,’gender’,’address’,’dob’);
      itu nama field database nya

  • putragaluh

    mbah tanya dong. makasih atas pencerahan scriptnya sangat membantu dan berhasil. cuma koq data yang ditampilkan itu data lama ya bukan data terbaru. mohon pencerahan. trims

    • Mbah Coding

      mungkin masalah query order nya saja, kalo pake script saya diatas di class person_model
      tinggal ubah disini:
      var $order = array(‘id’ => ‘desc’);

      keep coding 😉

      • putragaluh

        makasih atas responnya, ya logika saya juga ke sana bahkan script asli download cm ganti table nggak mau sort by id desc seperti yang diset di var $order itu.

        trus tanya lagi, nggak mau delete kira2 itu kenapa ya mbah ? makasih sebelumnya

        • Mbah Coding

          Coba diteliti lagi aja ajax jquery atau model nya

          • putragaluh

            iya nih masih belum berhasil padahal kayaknya dah bener deh hehehe .. oh ya terkait order itu, di demonya juga saya coba yang pertama dimunculkan itu bukan yang terakhir, data terakhir diinput muncul dibawah. harusnya kan paling atas. kayaknya order berdasarkan firstname/kolom pertama. thanks

          • Mbah Coding

            Oh iya. Itu karena ada sort datatables initial nya di kolom pertama bisa disetting dengan ditambah
            “order”: [],

            Jadi begini :

            table = $(‘#table’).DataTable({
            “processing”: true,
            “serverSide”: true,
            “order”: [],

          • putragaluh

            mantap, solved dah bisa .. sebelumnya dah pake itu cuma salah nyimpen hehehe .. makasih mbah dan sukses selalu ya ..

          • putragaluh

            akhirnya yang delete berhasil, karena saya pake url_prefix jadi url ajax nggak mau pake site_url melainkan base_url ..

  • Phil Lipines

    Thanks Dude… Keep posting more

    • Mbah Coding

      oke thanks brow

  • ‘Herman Eka Putra

    terimakasih mbah tutornya..

    tapi ada yg mau ditanya mbah..
    private function _get_datatables_query()

    {

    $this->db->from($this->table);

    ni kan menampilkan dari table, bagaimana kalau menampilkan dari hasil query.. makasih mas.

  • edo murdani

    sorry mbah newbie mau tanya.

    kenapa file css nya gk bisa ke-load ya mbah??

    <link rel="shortcut icon" href="”>

    <link rel="stylesheet" type="text/css" href="”>

    <link rel="stylesheet" type="text/css" href="”>

    mohon bimbingannya 🙂

    • Mbah Coding

      coba view page source nya dulu

  • Patrick Sangian

    eror pas update
    padahal udah ikut tutorialnya
    form nya gak bisa di parsing kayaknya

    gimana dong?

    • Mbah Coding

      Mksudnya ga bisa parsing gmn ya?

      • Patrick Sangian

        giniyah mbah kan form nya di post jadi json kan, kayaknya controller ga baca
        gak tau sih knapa
        pnya solusi gak?

        oia sttus ajax nya success kok, soalnya modal nya udah ke tutup
        tapi gak ke update

        oio bisa gak kalo yang di post bukan json, gimana kalo type html?

        • Mbah Coding

          Coba check js/ajax nya di browser console (ctrl+shift+J) response body nya keluarnya gmn?

  • Ahmad Solehin

    Is this can prevent sql injection..? Or another hacking type..?

    • Mbah Coding

      CI always escapes query values as long you use Active Record. Its enought secure..

      • Ahmad Solehin

        U use active record in this coding..?

        • Mbah Coding

          Yes

          • Ahmad Solehin

            if i Want to upload image,, what is the code..

            ‘image’ => $this->input->post(‘image’),

          • Mbah Coding

            Uploading file is different.
            U can using file uploading library

            http://www.codeigniter.com/user_guide/libraries/file_uploading.html

          • Ahmad Solehin

            upload file ini hanya bisa disimpan dalam folder saja? gi mana mau disimpan dalam database..?

  • freddy

    hello how can i make some validations, i just trying to input wrong date but the data can inserted uit ? Please respoon admin, by the way thaks for you effort to make this tutorial ?

    • Mbah Coding

      Hello sorry this tutorial just for simple ajax crud. Maybe next I will be write tutorial more complex case n add validation

      • freddy

        By the way i can understand with fastly, hem do you mind if you make the tutorial as soon as possible Mbah cause already search in google with little bite results with Keyword “Error Validations in modal using Codiegnter” and i’m in deadline (doesn’t mean force you)

        will be big thanks and many apreaciate, Keep in touch

        Freddy Sidauruk

      • Kinjal Upadhyay

        hi Mbah Coding. Please upload with validation in this tutorial.

  • Joko

    mbah, kalo eror yg seperti ini di bagian apa ya yg salah kira2?

    • Mbah Coding

      ajax_list() nya mgkin. coba buka browser console (ctrl+shift+J) nya untuk lihat ajax/javascript error nya

      • Iarinaval

        I want to know these answer in English please!

  • Syukron Muttaqien

    mbah.. kok data ane gk nampil ? itu controller dan javascript nya.. dan hasilnya tidak nampil.. apa ada yang salah mbah?

  • Kinjal Upadhyay

    This is helpful but validation is not giving. Can you upload jquery validation with this demo??. I try validation. But It is not fire in any textbox. Add blank value in data table

  • asep furkon efendi

    thanks to the tutorials
    nice code!…

    • Mbah Coding

      thanks for visiting this blog 😉

  • susiloharjo

    mbah nanya dong kalau array datanya bentuknya sum() dan order by gitu gimana ya caranya need help banget trims

    • Mbah Coding

      maksudnya gmn ya?

      • susiloharjo

        querynya mbah kalau misalnya jadi gini select barang,sum(total) from table group by barang order by barang desc;

        • Mbah Coding

          Tinggal tambah query di modelnya di dalam function _get_datatables_query()
          misal:
          $this->db->select(‘sum(total) as jumlah’);

          Jgn lupa pake alias untuk return object nya
          Group by di CI juga tinggal $this->db->group_by(‘barang’)

          Coba baca2 lagi active record atau query buildernya CI

          ini referensi nya
          https://www.codeigniter.com/userguide3/database/query_builder.html

          • susiloharjo

            Thx mbah

  • Cah Andezta

    mbah tanya donk? cara memnampilkan 2 table yang berbeda dalam 1 datatables gimana ya? mohon pencerahanya.

    • Mbah Coding

      apa maksud nya join?

      • Aghost Rush

        sama mbah, gimana

        ya kalo nampilin 2 tabel (join)

        • Mbah Coding

          tinggal query nya aja kan kalo itu. coba pahami query builder/Active record nya CI dulu 🙂

          • Aghost Rush

            boleh minta fbnya mbah, biar bisa tak chat lebih enak. masih newbie juga aku

      • Aghost Rush

        kalo join gimana mas ya

  • Cuma Liat2 Aja

    Masih Ada eror Mas DI Codingnya,
    Coba Test console di chrome… ( Fungsi edit, & Delete Nya )

    Uncaught SyntaxError: Unexpected token )

    makasih udah sharing, good jobs bro

    • Mbah Coding

      iya bro. entahlah. but It still work prefectly :))

  • Exepro Exe

    Salam sejahtera mbah…
    mbah,, saya udah mencoba demonya dan udah download juga,,
    dan hasilnya sangat menarik,,
    kerenn…

    saya mau nanya lagi ni mbah,,,
    gimana cara menampilkan data yang berelasi mbah..?

    $this->db->from($this->table);-> itu kan untuk menampilkan 1 table mbah,
    bagaimana kalau kita ingin me relasinya 2 atau 3 table gitu…
    Atau, Bisa $this->db->from($this->table); diganti dengan :
    $this->db->query(“select ……….. from nama_table where a=b ….. “);

    Mohoh di ajarkan mbah…
    hehehehe..
    saya baru belajar data table server side mbah…

    di tunggu bantuan dan solusinya mbah…
    terima kasih sebelumnya….
    🙂

    • Mbah Coding

      bisa juga tapi saya sarankan dengan query builder saja.

      kalo mau join kan tinggal ditambahi $this->db->join(“table1”, “table1 = table2 …..”);

      coba pelajari dulu query builder di CI
      https://www.codeigniter.com/userguide3/database/query_builder.html

      • Exepro Exe

        Selamat siang mbah….

        saya udah coba, dan berhasil merelasikannya mbah,,,

        tapi masalahnya,,

        ketika header data nya saya clik untuk mensortir malaah eror mbah,,,

        berikut script model saya :

        ‘desc’);

        private function _get_datatables_query()

        {

        $this->db->select(‘*’);

        $this->db->from($this->table);

        $this->db->join(‘persons_join_1’, ‘persons_join_1.id = persons.ket’);

        //$this->db->post();

        $i = 0;

        foreach ($this->column as $item)

        {

        if($_POST[‘search’][‘value’])

        ($i===0) ? $this->db->like($item, $_POST[‘search’][‘value’]) : $this->db->or_like($item, $_POST[‘search’][‘value’]);

        $column[$i] = $item;

        $i++;

        }

        if(isset($_POST[‘order’]))

        {

        $this->db->order_by($column[$_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);

        }

        }

        • Rifqi Fardi Pradhana

          sama saya juga dapet error ini. error nya ada di function _get_datatables_query() waktu nyari $_POST[‘order’] atau $_POST[‘search’]. ada solusi buat ngatasi nya?

  • IrFan Mokoginta

    malam mbah, tanya dong.
    kalo kita memisahkan data yand di ambil dari tabel database dipisahkan berdasarkan session,,
    contoh pada gambar , pada kegiatan data yang ditampilkan masi terdapat data milik user lain

    • Mbah Coding

      coba di kasih where aja, di modal nya 🙂

  • freddy

    Hi mbah i’m coming again, so to the point, i change your script cause doesn’t match with me, please check this out mbah about edit form inside modal

    here is my view based on event onclik in href modal mbah


    <input type="text" id ='id_product_post ?>’ >

    and here is my js

    //modal form di edit products
    $(‘#myModaledit’).on(‘shown.bs.modal’, function(event) {
    event.preventDefault();
    var id = $(“.id”).val(); //it give me same value
    alert(id);
    });

    all i need is take the right value, if your script show in view then onclick with value, i hope you understand what i mean ?

    Thanks

  • Ahmad Solehin

    kamu ade tutorial upload image using ajax tak?????????

  • freddy

    Hi mbah, i’m comming again ? i have issue mbh need you to know

    The issue is

    1. if one controller has private function _validate() more than 1 the validations will not working, i have been tested in my current project spend 2 hours, but debug it with delete one function _validate() all working fine, please help me to make two validations on it ? should i seperate with diffrent controller mbah, please reply as soon as possible if you don’t mind, thanks a lot

    • freddy

      Done, stupid errors by my self, all work like charm mbah, +1

  • Dinesh

    Want to run raw sql queries with join and union on different tables. How do i do this?

  • fuji taufik

    mbah kalo begini kenapa ya

    • Mbah Coding

      php ajax list nya biasanya yg error. cek error dengan browser console bro. (Ctrl+Shift+ J)

  • Carlos Zatarain

    Hi, my name is Carlos

    I have been following your guide and it has been great so far, but i have run into a little issue.

    I’m trying to show data that has an status:
    Status: A, it doesn’t show.
    Status: C, it shows.

    (I wanted it to be numbers but oh well…).

    I added this line in the model:

    private function _get_datatables_query(){
    $this->db->from($this->table);
    $this->db->where(‘status’,’C’); <- The one I added

    And it works fine when the table loads.
    But after I use the search field, data from the data base shows whether it has status=C or not.

    Could you please help me.
    Btw I'm kind of new to javascript,jquery and php.

  • Zdenko

    I’m getting this error:

    DataTables warning: table id = table – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    Is it something to do with the server or something else?

    • Mbah Coding

      your ajax response is getting error. you can see error with Browser console. Ctrl+shift+J

      • Zdenko

        using Ctrl + Shift + J did not see what kind of error does,
        only data from the server to display in the table did not come in browser.

        I use Postgres SQL server?
        I made the same table as indicated in the example with the same data.

        in the file person.php, function ajax_list()
        command – echo json_encode($output);
        make this string:

        “{“draw”:”1″,”recordsTotal”:5,”recordsFiltered”:5,”data”:[[null,null,”female”,”Tokyo”,”1964-03-04″],[null,null,”male”,”Tokyo”,”1988-09-02″],[null,null,”male”,”Kansas”,”1972-11-02″],[null,null,”male”,”Edinburgh”,”1977-03-24″],[null,null,”male”,”London”,”1989-01-01″]]}”

        but the data from the server does not appear in the table?
        Do I have something on the server Adjustments?

        • Mbah Coding

          sure no error ajax response like this?

          • Zdenko

            Thank you for help!
            I was able to solve all the CRUD working on postgresql
            ————————————————————————————————
            1.) in the example of all the column names in the table change in small characters
            firstName in firstname
            lastName in lastname

            ————————————————————————————————
            2.) example test table for postgresql:

            CREATE TYPE t2sex_t AS ENUM (‘male’,’female’);

            CREATE TABLE persons
            (
            id SERIAL PRIMARY KEY,
            firstname varchar(100),
            lastname varchar(100),
            gender t2sex_t,
            address varchar(200),
            dob date
            );

            INSERT INTO persons (firstname, lastname, gender, address, dob)
            VALUES
            (‘Airi’, ‘Satou’, ‘female’, ‘Tokyo’, ‘1964-03-04’),
            (‘Garrett’, ‘Winters’, ‘male’, ‘Tokyo’, ‘1988-09-02’),
            (‘John’, ‘Doe’, ‘male’, ‘Kansas’, ‘1972-11-02’),
            (‘Tatyana’, ‘Fitzpatrick’, ‘male’, ‘London’, ‘1989-01-01’),
            (‘Quinn’, ‘Flynn’, ‘male’, ‘Edinburgh’, ‘1977-03-24’);

            ————————————————————————————————
            3.) in file person_model, private function _get_datatables_query()
            to make it work “search” change line
            // ($i===0) ? $this->db->like($item, $_POST[‘search’][‘value’]) : $this->db->or_like($item, $_POST[‘search’][‘value’]);
            to
            ($i===0) ? $this->db->like(“cast(” . $item . ” as varchar)”, $_POST[‘search’][‘value’]) : $this->db->or_like(“cast(” . $item . ” as varchar)”, $_POST[‘search’][‘value’]);

          • Himanshu Bhola

            Please help me solving this i am getting the same error again and again
            DataTables warning: table id=table – Ajax error.

      • Zdenko

        I’ve solved one problem,
        Postgres always put little signs columns
        {[local]} => select * from persons;

        id | firstname | lastname | gender | address | age
        —- + ———– + ————- + ——– + ———- – + ————
        1 | Airi | satou | female | Tokyo | 04.03.1964
        2 | Garrett | Winters | small | Tokyo | 09.02.1988
        3 | John | Doe | small | Kansas | 02/11/1972
        4 | tatyana | Fitzpatrick | small | London | 1989-01-01
        5 | Quinn | Flynn | small | Edinburgh | 24/03/1977
        (5 rows)

        a column name in src are firstName | lastName with BIG “N”

        now I have another problem
        6th column we action is null

        not showing the button Edit and Delete?

  • Samir Taha

    hi
    can you tell how we should modify the codeigniter 3 functions $this->db->group_start(); to work in codeigniter 2.X ?
    *cause group_start() found only in Codeigniter 3.X.
    thanks

  • Andri M Fahmi

    hatur nuhun codingan nana berhasil . makasih (y)

  • Bang Tobek

    Selamat sore om,,, mau nanya ni om,, gimana cara nya membuat pencarian data seperti berikut ini(gambar) multi input search gitu… makasih om…

  • Rafael Bq

    I got a error where the assets files are not found. I changed base_url to site_url AND changed BASEPATH to FCPATH and still won’t do. Please help 🙁

  • Ali Richard

    Why is my output looks like this one? although I followed your instruction correctly

    • Ar

      import data tables before..

  • G CYBER CORRIDOR (GCC)

    Kalau untuk yang type file gimana caranya?

  • Qaher Husein

    Pada saat mau update atau add data… selalu keluar kaya begini:

    Sy coba cek webdeveloper inspector ada massage begini kenapa ya?

    SyntaxError: expected expression, got ‘)’

  • Indra Laksmana

    SyntaxError: expected expression, got ‘)’

    gimana cara atasinya mbah

    • Mirf

      Thank you very much for the wonder code. According to problem

      Uncaught SyntaxError: Unexpected token )

      I found the solution now.

      Please got to Person.php . There are

      href=”javascript:void() change to href=”javascript:void(0)

      the problem will gone.

  • ardiansyah Chaniago

    Maaf Mbah mau tanya, kalau id nya mau dibuat urut seperti ini : PRS001dst, itu gimana mbah ?, kalau bs skalian ada sample codenya mbah…

    Trimakasih

  • susiloharjo

    Mbah thx codenya berhasil, saya cuman tanya satu kalau saya pengen datanya yang kluar di filter berdasarkan session gimana ya mbah, edit bagian mananya saya sudah coba masukkan where di models dibagian ini

    private function _get_datatables_query()
    {

    $this->db->from($this->table);
    $this->db->where(‘noid’, ‘1’);
    dst …

    berhasil secara manual tapi saya pinginnya 1 di noidnya saya ambil dari session gimana caranya ya mbah mohon pencerahannya trima kasih

  • martin mendoza

    Helpppp me…
    is the line if($_POST[‘search’][‘value’])

  • inubee

    makasih banyak Mbah tutor-nya.
    btw, kok dari segi tampilannya punya saya aneh nih, agak ngelebar gitu…

    Ada saran Mbah?

  • inubee

    Makasih banyak Mbah tutornya.
    Btw, minta sarannya mengenai tampilan. kok punya saya ngelebar gini ya.
    Ada yg salah kah?

  • derpapst3000

    Pleace help i am a Newbe and i managed to write a timestamp in the table called “created” if i add a new entry. But how to do get the propper time and date output?

  • Gilang Pratama

    makasih mbah crud ini sangat bermanfaat bagi ane.

    terus kalau upload foto gimana ya mbah? ada tutorial lagi gak mbah?

  • S M Arshad

    hi how to use custom query

  • Koco

    selamat sore master
    master punya sourcode “(Codeigniter) Ajax CRUD using Bootstrap modals and DataTables” yang untuk posgresql? mohon bantuanya. Terimakasih banyak

    • mochamad anwar

      Dbdrivernya tinggal ganti ajah toh ,

  • Koco

    mbah punya sourcode yg untuk posgresql ga? terimakasih

  • Koco

    kalau ke posgresql yang dirubah apa saja ya? ada sourcodenya ga?

  • ScienceEdu

    Terimakasih mbah artikelnya, sepertinya tutorial ini sudah banyak dijadikan rujukan di youtube… 🙂

  • Pay

    kalo CSRF nya di aktifin piye mbah ? Udah keliling2 nyari masih bingung 🙁

  • rakesh

    data table warning table=id

  • dan

    izin download, mantap running well

  • whendikz

    Mbah mohon diajari cara nambahin form file upload yang nampik ke tabelnya, terimakasih

  • Ahmad Romadhon

    Gan, pada file model nya hanya dapat digunakan untuk satu tabel saja. Cara agar bisa fleksibel tanpa ada nya function-function yng sama persis dan file model tersebut bisa digunakan lebih dari 1 tabel bagaimana ya ?

    Thanks :3

  • Kev Maghirang

    hi how can i do left joins here?

    can someone help?

  • Kev Maghirang

    when i tried to edit the delete function i got this error on my error logs.

    Undefined property: CI::$person C:xampphtdocsTLC_HRapplicationthird_partyMXController.php 59 why is it?

  • Nicolai Cardenas

    I need an example of a crud form. with an input field the user to select from a list of data that queries a mysql table . When editing the record is located in the option that brings the database.

  • Achmad Rozikin

    Thank’s gan sangat bermanfaat 😀

  • Julian Harris

    apakah bisa jalan di CI 2?

    • roger

      $this->db->group_start(); dan $this->db->group_end();

      ngga’ ada di CI 2. pake custom string aja

  • Guess

    nambahin filter per kolom gimana ya?

  • Kurniawan Ismail

    wah mantab tenan ki…. mak nyus

  • saiful bachri

    where i get database?

  • ARYA ADITYA

    mbah kok muncul

    “DataTables warning: table id=table – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    itu knapa yaa

    • i have the same problem ? Do you get the solution .. ?

  • ArtuRo Franco

    is possible the same crud with oracle? why is the model? i have the connection and oci8 configuration, only need the functions in the model

  • Matías Motta

    ¡Muchas gracias, amigo!

    This code works perfectly.
    Greetings from Argentina

  • Joms

    Hi can you add an image field to this tutorial? Thanks!

  • mochamad anwar

    Mbah Kenapa modelnya ada 2 , person_model, person, sedangkan pada model hanya ada model person_model, terimakasih, saha Udah run cuma Masih bingung sama model person itu

  • mochamad anwar

    Mbah Kenapa modelnya ada 2 , person_model, person, sedangkan pada model hanya ada model person_model, terimakasih, saha Udah run cuma Masih bingung sama model person itu

  • manikanadan

    Search box is not working.it says internal server error.please help me

  • Aliasgar Vanak

    Hello,

    Thank you for this wonderful tutorial.

    Here i have one small question that is it possible to use join query or anything where i need to fetch data from multiple tables using foreign keys ?

    I tried that thing and it fetched the records but issuing with the main tables primary key and sorting with another table’s value.

    Ignore what i tried. Please help me how can i fetch the records in multiple tables as well their sorting.

    Thanks in advance

    Ali

    • Of course. just edit on models.
      maybe you can add query builder on _get_datatables_query() function

      • julio cesar Salas

        I have a similar problem, I have a table with multiple foreign keys , but how to use the search field returns me the below error, you can help me .
        Any example .
        Thank you.
        best regards
        attached image of my model and error

  • Khairul Fajeri

    kalo join, misal sama folder status(jomblo,nikah) , pada persons ditambah field status, nah terus cara agar ketika edit statusnya nampilin pilihan semua status yang ada gimana ?

    • mainin model nya aja bro. kasih fungsi join di
      _get_datatables_query()

      kapan2 ane bikin contoh tutorial yg lebih kompleks deh 😉

      • Syaiful Abdad

        contoh sederhana nambah join di _get_datatables_query() giaman ya mbah, soalnya sdh saya coba bbrapa cara tapi ga bisa, trims mbah

  • Ryan Bastian

    kalau saya melihat demonya. ketika saya langsung buka link http://livedemo.mbahcoding.com/ajax_crud_datatable/ajax_add datanya masuk dan datanya kosong.. saya lg ngetes kesalahan algoritma yg anda buat. saya rasa perlu adanya kondisi tambahan. terima kasih

    • just add if post. hehe. itu cuma simple sederhana.
      klo ada validator nya coba tutorial selanjutnya

      mbahcoding.com/tutorial/php/codeigniter/codeigniter-ajax-crud-modal-server-side-validation.html

  • gop4sch4s

    maaf mbah … mau tanya kalo mau membedakan tabel setiap user dikasih id .. caranya bagaimana mbah… oh iya sebelum terimamkasih sudah mau berbagi scrip.( downloads )..

  • gop4sch4s

    maksud saya membedakan isi data table setiap user login mbah… saya menaruhkan id user di _get_datatables_query models tapi ngak bisa terus … tolong di bantu mbah terima kasih

  • gop4sch4s

    contoh mbah di kontroler
    public function ajax_list()
    {
    $this->load->helper(‘url’);
    $u_idlist=$this->session->userdata(‘u_id’);
    $list = $this->person->get_datatables(u_idlist);
    $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;

    di modulesnya

    private function _get_datatables_query()
    {

    $this->db->from($this->table);

  • gop4sch4s

    maksud saya membedakan isi data table setiap user login mbah… saya menaruhkan id user di _get_datatables_query models tapi ngak bisa terus … tolong di bantu mbah terima kasih

    • gak bisanya gimana ya? logikanya saya baca sudah benar. tp kalo pengen lebih simple tinggal kasih this->db->where nya di construktor model nya. dibawah $this->load->database();
      ga usah pake parameter tambahan lagi.

      • gop4sch4s

        mbah coding sudah benar scrip diatas bagus sekali dan saya terapkan sekarang.. script yg saya buat sama persis dengan mbah coding…tp saya menambahkan multi user login hingga masuk di tabel yang mbah buat tetapi saya rubah databasenya jadi isinya tabel brng dan saya hanya ingin memilah tampilan tabel itu…yaitu jika saya mengguakan login user1 maka tapilan isi barang user1 jika saya login user2 maka isi tabel barang dari database punya user2 … scrip td saya menambahkannya this->db->where juga tp id user loginnya harus sesuai dengan login mbah.. saya harus bagaimana ya.. terima kasih

  • Agus Setiawan

    Haloo mbah ada tutorial row inline editing pada saat edit data., jadi waktu edit data tidak muncul modal tetapi langsung di tablenya, 🙂

    • belum gan. mungkin bisa untuk referensi tulisan saya selanjutnya. thanks you 😉

      • Agus Setiawan

        di tunggu gan postingan selanjutnya row inline editing datatable jquery

  • denerfdasilva1

    Good!! How Create Export .xls?

  • Alonso

    Intregation AdminLte?…not working

  • Juan Alonso Duran

    Integration AdminALT not work…? erro js jquerry not work in adminlte template please solution

  • ranger

    bug : “”
    cuba buka url diatas, keluar pesan error.sesuaikan saja site_url nya sesuai config ci masing2.

  • ranger

    bug :
    buka url diatas.

  • ranger

    ajax url saat dibuka di new tab browse muncul error sebelum menampilkan json nya. gmn tu?

  • https://uploads.disquscdn.com/images/7107eedecf538222b43f033b52cb3211fe0dea60e7ff5a85854bd352b6bcf059.png

    Hello , i am trying to integrate this with my school project , but when i try to set base_url and $config[‘index.php’] =’index.php’ to $config[‘index.php’] =” and also set ,htaccess THAN the search box of datatable stop working with a error “Invalid json … ” .
    Help with this .
    Thanks in Advanced 🙂 And i liked your script , it work amazing

    • Stacy

      @Ahmedwebtech Hi I’m also encountering this error prompt did you find out the solution to get the project working? Please help?

      • @stacy . I don’t know what was the problem . But i find a work around . i wanted to use this script in my adminLTE admin panel . i just use the datatable default libarary of adminlte instead of the present library use in this script . this solved my problem . and i am still wondering what was wrong with current libraries 😛

        • stacy

          @Ahmedwebtech Thanks for the reply, another question is there a way to auto-update the datatable upon sensing the database table has made changes (new data inserted) by another external script based on the code context written here instead of user having to refresh/reload themselves? Thanks

          • @stacy , Can you give the sample code and explain more about your requirement . But yes we can , you will find a function reload_table(); . you can trigger this function on any event you want as your requirement . 🙂

          • stacy

            @ahmedwebtech:disqus Hi, I have a script that will run (inserting new row into the db) without the dependency of the code stated here. However, the front-end (which is the example stated here) will unilaterally sense that the db has made changes to trigger reload_table() function? Is it possible?

  • changing base_url and removing index.php from url using htaccess stop the search bar loading and shows error “invalid json ..”

  • Wahyu Artadianto

    Mbah coding kalau nambahin auto number jadi formatnya Kar-001 dan seterusnya help dunk

    • Wahyu Artadianto

      akhirnya dapat juga

      function buat_kode()
      {
      $this->db->select(‘RIGHT(tabel_mahasiswa.Kode,3) as kode’, FALSE);
      $this->db->order_by(‘Kode’,’DESC’);
      $this->db->limit(1);
      $query = $this->db->get(‘tabel_mahasiswa’);
      //cek dulu apakah ada sudah ada kode di tabel.
      if($query->num_rows() 0)
      {
      //jika kode ternyata sudah ada.
      $data = $query->row();
      $kode = intval($data->kode) + 1;
      }
      else
      {
      //jika kode belum ada
      $kode = 1;
      }
      $kodemax = str_pad($kode, 3, “000”, STR_PAD_LEFT);
      $kodejadi = “Kar-“.$kodemax;
      return $kodejadi;
      }

  • koprilia

    mbah untuk updatenya kok punya saya error trus napa yah…
    pertama ada satu field yang kosong tiap kali mau update seperti gambar dibawah ini

    • Wahyu Artadianto

      punya saya berhasil kok… coba kirim errornya seperti apa?

      • viana

        Mas error begini kenapa ya

        • Wahyu Artadianto

          mana?

  • koprilia

    Error adding / update data <<< itu mbah yang tampil saat update

  • koprilia
  • Ajat Darojat

    delete nya gak berfungsi

    • Wahyu Artadianto

      ngga berfungsi masksudnya ngga jalan? errornya seperti apa?

      • koprilia
        • Wahyu Artadianto

          bisa dikirimkan cooding di view nya? terutama di function save()

        • Wahyu Artadianto

          kok banyak kali yg dikirim? email kesini aja biar saya coba langsung wahyu.artadianto@gmail.com kalau bisa plus sqlnya

        • Wahyu Artadianto

          dah ku email ya …. kesalahannnya ada di nama controller dan nama modal salah

          sama di folder view tidak diikutkan id

          success: function(data)
          {

          $(‘[name=”id”]’).val(data.id);
          …….
          …….

          }

          • koprilia

            maaf mas saya masih kurang paham dengan kesalahan di nama controoller dan nama modal itu?? maksudnya bagaimana yah mas??

          • Wahyu Artadianto

            masih error apa udah bisa jalan?

          • koprilia

            masih error mas

          • Wahyu Artadianto

            errornya apa? ditempat saya jalan tuh

          • koprilia

            untuk update selain field photo tidak error, tapi untuk update photo ada error pada saat simpan, tapi saat di reload tablenya photonya berhasil di reload. pertanyaanya pass saat update itu kok masih erroradd/update munculnya begitu..apakah karena letak path uploadnyanya atau gimana??

          • Wahyu Artadianto

            ya letak root folder uploadnya kalau menggunakan firefox anda bisa cek errornya menggunakan webconsole (ctrl+shift+k) sehingga pada saat ada error kita bisa tau letak errornya ada dimana

          • koprilia

            eh sudah bisa mas…terimakasih banget mas….

          • Ajat Darojat

            jadi yang mesti di edit yang script yang mana mas?

          • Wahyu Artadianto

            just email mas bro in wahyu.artadianto@gmail.com
            and tell ur problem in detail and i will help u ASAP

  • Wahyu Artadianto

    mbah help dong kalau pake inner join sort, search nya ngga jalan

  • iman
  • iman

    mbah coding 😀
    bisa tolong jelasin array ini:
    $_POST[‘start’];
    itu array start dari mana ya ??

    • Wahyu Artadianto

      dear iman
      jangan separuh kalau ambil cooding
      $_POST[‘start’]; depannya ada $no =
      yg artinya hampir sama dengan no++ tetapi untuk membuat tiap halaman datatable tetap terus dihitung

      kalau tanpa $_POST[‘start’];

      maka nanti saat menekan tombol next 1,2,3 maka nomornya akan selalu mulai dari 1-10
      tetapi bila menggunakan $no = $_POST[‘start’]; maka nilainya akan dijumlahkan tiap halaman
      dari 1-10 lalu bila di next akan 11-20 dst

      semoga bisa membantu

      • iman

        oke mbah coding, hahahaha, maaf ya, soalnya kemarin nemu error di syntax itu.
        tapi sekarang solve kok..

        sekali lagi makasih banyak mbah coding, atas tutorialnya.
        semoga ilmu nya semakin berkah.

  • Edwin

    Mbah,, klo ngambil data dari 2 tabel yang berbeda atau inner join cara penulisan codingnya yang bener gimana ya mbah?

  • whendikz

    Mbah kalo pengen responsive tabelnya sama ukuran layar hp gimana ya? thanks

  • Paulinho Junior

    Excelent! Nice tutorial

  • azzam fikri

    Codenya jalan mbah, saya mau ganti querynya.
    Kalau query manual kaya gini :
    $value = date(“m”);
    $value2 = date(“Y”);
    $sql = “SELECT nik,nama_lengkap, tgl_masuk, DATE_FORMAT(tgl_masuk, ‘%d %M %Y’) AS tanggal_masuk
    FROM karyawan
    WHERE is_active=1 AND MONTH(tgl_masuk) = ‘{$value}’
    AND ‘{$value2}’ > YEAR(tgl_masuk) and
    NOT EXISTS
    (select id_karyawan,tahun from cuti_sisa where tahun= ‘{$value2}’ and karyawan.id_karyawan=cuti_sisa.id_karyawan) “;

    Saya pake Active Record codeigniter jadi gini :
    $value = date(“m”);
    $value2 = date(“Y”);
    $where = “is_active=1 AND MONTH(tgl_masuk) = ’04’
    AND ‘$value2’ > YEAR(tgl_masuk) and
    NOT EXISTS
    (select id_karyawan,tahun from cuti_sisa where tahun= ‘$value2’ and karyawan.id_karyawan=cuti_sisa.id_karyawan)”;
    $this->db->select(‘nik,nama_lengkap, tgl_masuk’);
    $this->db->from($this->table);
    $this->db->where($where, NULL, FALSE);

    Jadi ga jalan mas.

    kalau pake ini bisa jalan :
    var $table = ‘karyawan’;
    $this->db->from($this->table);

    Mohon koreksinya mas .

  • azzam fikri

    Mbah kalau mau tambahin alert sukses kalau selesai insert update gimana ya mbah ?

  • whendikz

    Mbah kenapa saat menambahkan data server error 500, apa yang salahnya?

  • Makasih source code membantu sekali, saya coba code ini berhasil tapi pas saya gabungin dengan code lain, jadi error mas ? sepertinya ada kesalahan, bingung dan juga gaada pesan errornya, data tabelnya ga tampil..

  • Muhammad Iqbal Ali

    maaf gan mau taya, saya sudah megikuti tutorial nya dengan baik, dan juga berhasil, tapi ketika nama tabel “id” pada database saya ganti dengan “id_users” dan di file Person_model.php nya sudah saya sesuaikan kok malah gak nampil, atau yang harus digantinya code “id” yang mana supaya bisa menyesuaikan dengan tabel di databasenya, terimakasih