CRUD Datatables Server Side Using Ignited Datatables [COMPLETE TUTORIAL]

CRUD Datatables Server Side Using Ignited Datatables [COMPLETE TUTORIAL]

Ignited datatables is really awesome.

If you want to make load millions record faster. You need to learn how to use ignited databases.

Let me tell you the story.

Two years ago, I started getting to know datatables. Datatables is really awesome.

I have done many projects by using datatables. because simple, easy, and include filter, show per-page, and pagination.

I'm pretty sure you're familiar with datatables, that's why you're reading this article.

A few months ago, I worked on a Point Of Sale (POS) project using datatables and codeigniter.

It all running well at the first time, when product data has reached 12,000 records, problems arise. The data is loaded very slowly and often does not respond (not responding) to the browser.

Hmmm..,

I'm sure you've also had the same problem.

Confused with the problem, I almost decided not to use datatables and switch to default pagination codeigniter and create a filter manually.

And then, I realized something. Apparently pagination default codeigniter is process on the server (server-side processing).

And then I start to surf on google about server-side processing with datatables. Finally, I found a awesome library. it's ignited datatables.

I try to use this library. It is proven to load data 12,000 records much faster.

Awesome right.

Ignited-datatables is a library for creating server-side processing datatables without need to write complex program code.

Ignited-datatables is also easy for JOIN Table, number format, image rendering, etc.

How lucky you are finding this article. Because I will explore thoroughly how to create CRUD (Create Read Update Delete) server-side processing datatables from join table.

Let’s get start it.

 

Step 1. Preparation

This is important!

If you missed this step, it means you missed the whole of this tutorial.

Good preparation will determine your success following this tutorial. The better your preparation, the more likely you will succeed in following this tutorial.

Do not skip this step, though it feels complex.

So, what do you need to prepare?

Here’s the list:

1. Codeiginter

Codeigniter is the main php framework we will use in this tutorial. If you do not have it yet, please download it at the official website: www.codeigniter.com

2. Jquery

Jquery is a javascript framework that works to help simplify the writing of AJAX-based scripts.

If you do not have it yet, please download it first at the official website: www.jquery.com

3. Bootstrap

Bootstrap is a framework to beautify the user interface (UI). If you do not have it yet, please download it first at the official website: www.getbootstrap.com  

4. Ignited-datatables

This is library you need to download. You could download it on github: https://github.com/IgnitedDatatables/Ignited-Datatables

5. Datatables

Datatables is a plugin built from jquery to display data in table form and has integrated with filters, show per page, and pagination.

To download datatables please download at following link: https://datatables.net/download/index.

6. Bootstrap Datatables

Bootstrap datatables is useful to give style to datatable. To download bootstrap datatables please download at following link: https://datatables.net/manual/styling/bootstrap

 

Step 2. Creating of database structures

In this tutorial, I use mysql as Database Management System (DBMS).

If you also use mysql, you will love this tutorial.

But,

If you are using other DBMS like Oracle, SQL Server, Maria DB, or MongoDB.

No, Problem!

Provided you understand SQL (Structured Query Language) better.

Ok, Let's continue!

Please create a database, here I create a database with named crud_ignited.

If you create a database with the same name it will be better.

Please execute this query to create the database:

CREATE DATABASE crud_ignited;

That query will generate a database with name crud_ignited.

After that,

Create a table with name categories by following this query:

CREATE TABLE categories(
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(50)
)ENGINE INNODB;

And then create one more table by following this query:

CREATE TABLE product(
product_code VARCHAR(10) PRIMARY KEY,
product_name VARCHAR(100),
product_price DOUBLE,
product_category_id INT,
FOREIGN KEY (product_category_id) REFERENCES categories (category_id) ON UPDATE CASCADE
)ENGINE INNODB;

So that, we have two relational tables, categories and product.

like this:

Relational Database

After that,

Insert a few record to categories table by following this query:

insert into categories(category_name) 
values ('Drinks'),('Foods');

 

Step 3. Setup Codeigniter

Extract codeigniter that has been downloaded earlier to www folder (if you use wampserver) or htdocs (if you use XAMPP).

Because I use wampserver. So, I extract it to c:/wamp/www/

And then, rename codeigniter project to be crud_serverside.

Like this:

Web Root

Open crud_serverside folder and create assets folder. And then include the bootstrap, jquery datatables, bootstrap datatables, and jquery files inside the assets folder.

So that look like this:

Project Structures

 

Step 4. Codeigniter Configuration

Next step is the configuration on the codeigniter.

Here are some files you need to configure:

1. Autoload.php

To configure the autoload.php, please open the folder:

application/config/autoload.php

like this:

Autoload

Open autoload.php using text editor like Notepad++ or Sublime Text.

And then find this code:

$autoload['libraries'] = array();
$autoload['helper'] = array();

Set to be like this:

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url');

2. Config.php

To configure the config.php, please open the folder:

application/config/config.php

like this:

Config

Open config.php file using text editor, and then find this code:

$config['base_url'] = '';

Set to be like this:

$config['base_url'] = 'http://localhost/crud_serverside/';

3. Database.php

To configure the database.php, please open the folder:

application/config/database.php

like this:

database

Open database.php file using text editor, and then find this code:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
        'dsn'   => '',
        'hostname' => 'localhost',
        'username' => '',
        'password' => '',
        'database' => '', 
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => (ENVIRONMENT !== 'production'),
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
);

Set to be like this:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
        'dsn'   => '',
        'hostname' => 'localhost',
        'username' => 'root',
        'password' => '',
        'database' => 'crud_ignited', 
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => (ENVIRONMENT !== 'production'),
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
);

 

Step 5. Controller

Go ahead and create a controller file controllers/Crud.php with the following this code:

<?php
class Crud extends CI_Controller{
  function __construct(){
    parent::__construct();
      $this->load->library('datatables'); //load library ignited-dataTable
      $this->load->model('crud_model'); //load model crud_model
  }
  function index(){
      $x['category']=$this->crud_model->get_category();
      $this->load->view('crud_view',$x);
  }

  function get_product_json() { //get product data and encode to be JSON object
      header('Content-Type: application/json');
      echo $this->crud_model->get_all_product();
  }

  function save(){ //insert record method
      $this->crud_model->insert_product();
      redirect('crud');
  }

  function update(){ //update record method
      $this->crud_model->update_product();
      redirect('crud');
  }

  function delete(){ //delete record method
      $this->crud_model->delete_product();
      redirect('crud');
  }

}

 

Step 6. Model

Go ahead and create a model file models/Crud_model.php with the following this code:

<?php
class Crud_model extends CI_Model{
  //get all categories method
  function get_category(){
      $result=$this->db->get('categories');
      return $result;
  }
  //generate dataTable serverside method
  function get_all_product() {
      $this->datatables->select('product_code,product_name,product_price,category_id,category_name');
      $this->datatables->from('product');
      $this->datatables->join('categories', 'product_category_id=category_id');
      $this->datatables->add_column('view', '<a href="javascript:void(0);" class="edit_record btn btn-info" data-code="$1" data-name="$2" data-price="$3" data-category="$4">Edit</a>  <a href="javascript:void(0);" class="delete_record btn btn-danger" data-code="$1">Delete</a>','product_code,product_name,product_price,category_id,category_name');
      return $this->datatables->generate();
  }
  //insert data method
  function insert_product(){
      $data=array(
        'product_code'        => $this->input->post('product_code'),
        'product_name'        => $this->input->post('product_name'),
        'product_price'       => $this->input->post('price'),
        'product_category_id' => $this->input->post('category')
      );
      $result=$this->db->insert('product', $data);
      return $result;
  }
  //update data method
  function update_product(){
      $product_code=$this->input->post('product_code');
      $data=array(
        'product_name'         => $this->input->post('product_name'),
        'product_price'        => $this->input->post('price'),
        'product_category_id'  => $this->input->post('category')
      );
      $this->db->where('product_code',$product_code);
      $result=$this->db->update('product', $data);
      return $result;
  }
  //delete data method
  function delete_product(){
      $product_code=$this->input->post('product_code');
      $this->db->where('product_code',$product_code);
      $result=$this->db->delete('product');
      return $result;
  }
}

 

Step 7. View

Go ahead and create a view file views/crud_view.php with the following this code:

<html lang="en">
<head>
        <meta charset="utf-8">
        <title>Crud ignited datatables in CodeIgniter</title>
  <link href="<?php echo base_url().'assets/css/bootstrap.css'?>" rel="stylesheet" type="text/css"/>
        <link href="<?php echo base_url().'assets/css/jquery.datatables.min.css'?>" rel="stylesheet" type="text/css"/>
  <link href="<?php echo base_url().'assets/css/dataTables.bootstrap.css'?>" rel="stylesheet" type="text/css"/>
</head>
<body>
  <div class="container">
    <h2>Product List</h2>
                <button class="btn btn-success pull-right" data-toggle="modal" data-target="#myModalAdd">Add New</button>
    <table class="table table-striped" id="mytable">
      <thead>
        <tr>
          <th>Product Code</th>
          <th>Product Name</th>
          <th>Price</th>
          <th>Category</th>
          <th>Action</th>
        </tr>
      </thead>
    </table>
  </div>

        <!-- Modal Add Product-->
          <form id="add-row-form" action="<?php echo site_url('crud/save');?>" method="post">
             <div class="modal fade" id="myModalAdd" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
                <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>
                           <h4 class="modal-title" id="myModalLabel">Add New</h4>
                       </div>
                       <div class="modal-body">
                           <div class="form-group">
                               <input type="text" name="product_code" class="form-control" placeholder="Product Code" required>
                           </div>
                                                                                 <div class="form-group">
                               <input type="text" name="product_name" class="form-control" placeholder="Product Name" required>
                           </div>
                                                                                 <div class="form-group">
                               <select name="category" class="form-control" placeholder="Category" required>
                                                                                                          <?php foreach ($category->result() as $row) :?>
                                                                                                                        <option value="<?php echo $row->category_id;?>"><?php echo $row->category_name;?></option>
                                                                                                                <?php endforeach;?>
                                                                                                 </select>
                           </div>
                                                                                 <div class="form-group">
                               <input type="text" name="price" class="form-control" placeholder="Price" required>
                           </div>

                       </div>
                       <div class="modal-footer">
                                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                                <button type="submit" class="btn btn-success">Save</button>
                       </div>
                                </div>
                </div>
             </div>
         </form>

         <!-- Modal Update Product-->
          <form id="add-row-form" action="<?php echo site_url('crud/update');?>" method="post">
             <div class="modal fade" id="ModalUpdate" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
                <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>
                           <h4 class="modal-title" id="myModalLabel">Update Product</h4>
                       </div>
                       <div class="modal-body">
                           <div class="form-group">
                               <input type="text" name="product_code" class="form-control" placeholder="Product Code" readonly>
                           </div>
                                                                                 <div class="form-group">
                               <input type="text" name="product_name" class="form-control" placeholder="Product Name" required>
                           </div>
                                                                                 <div class="form-group">
                               <select name="category" class="form-control" required>
                                                                                                         <?php foreach ($category->result() as $row) :?>
                                                                                                                 <option value="<?php echo $row->category_id;?>"><?php echo $row->category_name;?></option>
                                                                                                         <?php endforeach;?>
                                                                                                 </select>
                           </div>
                                                                                 <div class="form-group">
                               <input type="text" name="price" class="form-control" placeholder="Price" required>
                           </div>

                       </div>
                       <div class="modal-footer">
                                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                                <button type="submit" class="btn btn-success">Update</button>
                       </div>
                                </div>
                </div>
             </div>
         </form>

         <!-- Modal delete Product-->
          <form id="add-row-form" action="<?php echo site_url('crud/delete');?>" method="post">
             <div class="modal fade" id="ModalDelete" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
                <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>
                           <h4 class="modal-title" id="myModalLabel">Delete Product</h4>
                       </div>
                       <div class="modal-body">
                               <input type="hidden" name="product_code" class="form-control" required>
                                                                                                 <strong>Are you sure to delete this record?</strong>
                       </div>
                       <div class="modal-footer">
                                <button type="button" class="btn btn-default" data-dismiss="modal">No</button>
                                <button type="submit" class="btn btn-success">Yes</button>
                       </div>
                                </div>
                </div>
             </div>
         </form>

<script src="<?php echo base_url().'assets/js/jquery-3.2.1.js'?>"></script>
<script src="<?php echo base_url().'assets/js/bootstrap.js'?>"></script>
<script src="<?php echo base_url().'assets/js/jquery.datatables.min.js'?>"></script>
<script src="<?php echo base_url().'assets/js/dataTables.bootstrap.js'?>"></script>

<script>
        $(document).ready(function(){
                // Setup datatables
                $.fn.dataTableExt.oApi.fnPagingInfo = function(oSettings)
      {
          return {
              "iStart": oSettings._iDisplayStart,
              "iEnd": oSettings.fnDisplayEnd(),
              "iLength": oSettings._iDisplayLength,
              "iTotal": oSettings.fnRecordsTotal(),
              "iFilteredTotal": oSettings.fnRecordsDisplay(),
              "iPage": Math.ceil(oSettings._iDisplayStart / oSettings._iDisplayLength),
              "iTotalPages": Math.ceil(oSettings.fnRecordsDisplay() / oSettings._iDisplayLength)
          };
      };

      var table = $("#mytable").dataTable({
          initComplete: function() {
              var api = this.api();
              $('#mytable_filter input')
                  .off('.DT')
                  .on('input.DT', function() {
                      api.search(this.value).draw();
              });
          },
              oLanguage: {
              sProcessing: "loading..."
          },
              processing: true,
              serverSide: true,
              ajax: {"url": "<?php echo base_url().'index.php/crud/get_product_json'?>", "type": "POST"},
                        columns: [
                                                                                                {"data": "product_code"},
                                                                                                {"data": "product_name"},
                                                                                                //render number format for price
                        {"data": "product_price", render: $.fn.dataTable.render.number(',', '.', '')},
                        {"data": "category_name"},
                        {"data": "view"}
                  ],
                        order: [[1, 'asc']],
          rowCallback: function(row, data, iDisplayIndex) {
              var info = this.fnPagingInfo();
              var page = info.iPage;
              var length = info.iLength;
              $('td:eq(0)', row).html();
          }

      });
                        // end setup datatables
                        // get Edit Records
                        $('#mytable').on('click','.edit_record',function(){
            var code=$(this).data('code');
                                                var name=$(this).data('name');
                                                var price=$(this).data('price');
                                                var category=$(this).data('category');
            $('#ModalUpdate').modal('show');
            $('[name="product_code"]').val(code);
                                                $('[name="product_name"]').val(name);
                                                $('[name="price"]').val(price);
                                                $('[name="category"]').val(category);
      });
                        // End Edit Records
                        // get delete Records
                        $('#mytable').on('click','.delete_record',function(){
            var code=$(this).data('code');
            $('#ModalDelete').modal('show');
            $('[name="product_code"]').val(code);
      });
                        // End delete Records
        });
</script>
</body>
</html>

Now, go ahead and access our custom page at http://localhost/crud_serverside/index.php/crud and you will see the final result like this:

Final Result

Please click add new button to add new record, click edit button to update record, and click delete button to delete record.

If you want to see how awesome this is. Go ahead insert more than 20000 records.

You will see how fast the data is loaded.

So, that's it,

We've done it!

If you feel this tutorial is useful for you. Please share! Perhaps, this tutorial is useful also for your friend!

 

CONCLUSION

This tutorial is about how to create a CRUD (Create Read Update Delete) application with ignited-datatables using codeigniter.

Ignited-datatables is a library that is enabled to make server-side processing easier on datatables.

Server-side processing is a method to accelerate the load of large data from the database.

With server-side processing can improve the User Experience (UX) quality.

Download Source


Comments (25)

St. Indah M, 14 April 2018 03:22 -

Very helpfull... thank you

M Fikri, 22 April 2018 08:02 -

You are welcome.

cucusugiono, 31 May 2018 11:02 -

thanks, really fast and usefull

M Fikri, 20 October 2018 08:56 -

You are welcome.

Dipak, 31 May 2018 22:15 -

Hello Sir, I am facing problem with retrieve data for edit_record after 10th column. After 10th column I retrieve column index instead of getting value. Please help me. Thanking You. Regards, Dipak

Antoniohdez, 07 June 2018 01:36 -

Hi! When I want to see the value of "product_code" it returns only "product_code" and not returning the actual value of. Can you help me, please?

Albertus Kusuma, 10 June 2018 06:18 -

Fikri , kemarin ak coba cuma ada error undefined function base_url itu krn apa y? thx ya

Andre, 09 October 2018 16:33 -

Great job. But I want to know how I can to pass variable from url in aja call. I tryed many solutions and nothing. I use this to do a filter in database. My solution(example): ajax: { url: "imigrante/json", type: "POST", "data": {value:1}, },

M Fikri, 20 October 2018 09:03 -

Hi Andre,
Thank for Asking.
I Think, It will be better if you pass the variable to php variable inside the page and then pass the variable to AJAX call.

irhas, 20 February 2019 08:11 -

pada model: function get_all_product() { $this->datatables->select('product_code,product_name,product_price,category_id,category_name'); $this->datatables->from('product'); $this->datatables->join('categories', 'product_category_id=category_id'); $this->datatables->add_column('view', '<a href="[removed]void(0);" class="edit_record btn btn-info" data-code="$1" data-name="$2" data-price="$3" data-category="$4">Edit</a> <a href="[removed]void(0);" class="delete_record btn btn-danger" data-code="$1">Delete</a>','product_code,product_name,product_price,category_id,category_name'); return $this->datatables->generate(); } jika JOIN itu menggunakan JOIN LEFT itu bagaimana? dan jika lebih dari satu bagaimana?

M Fikri, 11 March 2019 10:54 -

Jika menggunakan LEFT JOIN tinggal tambah left aja mas, seperti ini:

$this->datatables->join('categories', 'product_category_id=category_id', 'left');

 

irhas, 20 February 2019 08:12 -

mas fikri, ada kontak wa nya kah? ada yang ingin ditanyakan lebih detil. trims.

ahhhsiiyyaaapp, 19 March 2019 23:02 -

Sebagai orang iindonesia, saya bangga melihat hasil kerja anda. LanjutKeun..

naruto, 28 April 2019 13:45 -

mas yg ini apa ya maksudnya? saya cari merujuk kmn belum ketemu :D data-code="$1" data-name="$2" data-price="$3" data-category="$4" di crud_model. tks

M Fikri, 29 April 2019 09:07 -

Merujuk kesini mas:

$this->datatables->add_column('view',' ','product_code,product_name,product_price,category_id,category_name');

$1 = product_code, $2 = product_name, $3 = product_price, $4 = $category_id, $4 = category_name

jenal, 22 May 2019 12:58 -

mantap mas, ditunggu advance tutorial CI lainnya

M Fikri, 25 May 2019 21:43 -

Siap mas

Ridwan, 15 June 2021 09:03 -

Duplicate column name 'id_store' SELECT COUNT(*) FROM (SELECT * FROM `product_stok` LEFT JOIN `store` ON `product_stok`.`id_store`=`store`.`id_store`) bagaimana cara mengatasi Duplicate column name

M Fikri, 07 July 2021 16:36 -

Jangan gunakan select * from, melainkan select field1, field2, product_stok.id_store from .....

Leave a Comment