Tuesday 6 December 2016

DataTables - Server-side Processing in Codeigniter using Ajax



From this tutorial we have started learning one new topics in Codeigniter tutorial. In this topics we will discuss how can we make crud system by using Ajax with Codeigniter framework. In this system we will use DataTables Jquery plugin for display data in tabular format and we will also use Bootstrap modals for inserting and updating data. Here we will implement DataTables server side processing by using Ajax. This is the first part in which we will fetch data from Mysql table by using Ajax in Codeigniter Application and then after we will displaying that data in on web page by using DataTables jquery plugin with server side processing. In server side processing we have to write query for search, order and pagination at server side. This all things we will learn in this part. We have make simple query for all different condition like searching, ordering or even pagination and this query has been called by Ajax in codeigniter framework. So we can smoothly search table, can see data in different order or pagination which has been handle by Ajax server side processing in data tables. We can perform different operation in data table by using Ajax in Codeigniter application.




Source Code


Controllers - crud.php


 <?php  
 defined('BASEPATH') OR exit('No direct script access allowed');  
 class Crud extends CI_Controller {  
      //functions  
      function index(){  
           $data["title"] = "Codeigniter Ajax CRUD with Data Tables and Bootstrap Modals";  
           $this->load->view('crud_view', $data);  
      }  
      function fetch_user(){  
           $this->load->model("crud_model");  
           $fetch_data = $this->crud_model->make_datatables();  
           $data = array();  
           foreach($fetch_data as $row)  
           {  
                $sub_array = array();  
                $sub_array[] = '<img src="'.base_url().'upload/'.$row->image.'" class="img-thumbnail" width="50" height="35" />';  
                $sub_array[] = $row->first_name;  
                $sub_array[] = $row->last_name;  
                $sub_array[] = '<button type="button" name="update" id="'.$row->id.'" class="btn btn-warning btn-xs">Update</button>';  
                $sub_array[] = '<button type="button" name="delete" id="'.$row->id.'" class="btn btn-danger btn-xs">Delete</button>';  
                $data[] = $sub_array;  
           }  
           $output = array(  
                "draw"                    =>     intval($_POST["draw"]),  
                "recordsTotal"          =>      $this->crud_model->get_all_data(),  
                "recordsFiltered"     =>     $this->crud_model->get_filtered_data(),  
                "data"                    =>     $data  
           );  
           echo json_encode($output);  
      }  
 }  

Models - crud_model.php


 <?php  
 class Crud_model extends CI_Model  
 {  
      var $table = "users";  
      var $select_column = array("id", "first_name", "last_name", "image");  
      var $order_column = array(null, "first_name", "last_name", null, null);  
      function make_query()  
      {  
           $this->db->select($this->select_column);  
           $this->db->from($this->table);  
           if(isset($_POST["search"]["value"]))  
           {  
                $this->db->like("first_name", $_POST["search"]["value"]);  
                $this->db->or_like("last_name", $_POST["search"]["value"]);  
           }  
           if(isset($_POST["order"]))  
           {  
                $this->db->order_by($this->order_column[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);  
           }  
           else  
           {  
                $this->db->order_by('id', 'DESC');  
           }  
      }  
      function make_datatables(){  
           $this->make_query();  
           if($_POST["length"] != -1)  
           {  
                $this->db->limit($_POST['length'], $_POST['start']);  
           }  
           $query = $this->db->get();  
           return $query->result();  
      }  
      function get_filtered_data(){  
           $this->make_query();  
           $query = $this->db->get();  
           return $query->num_rows();  
      }       
      function get_all_data()  
      {  
           $this->db->select("*");  
           $this->db->from($this->table);  
           return $this->db->count_all_results();  
      }  
 }  

Views - crud_view.php


 <html>  
 <head>  
   <title><?php echo $title; ?></title>  
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
      <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>  
      <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>            
      <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />  
   <style>  
           body  
           {  
                margin:0;  
                padding:0;  
                background-color:#f1f1f1;  
           }  
           .box  
           {  
                width:900px;  
                padding:20px;  
                background-color:#fff;  
                border:1px solid #ccc;  
                border-radius:5px;  
                margin-top:10px;  
           }  
      </style>  
 </head>  
 <body>  
      <div class="container box">  
           <h3 align="center"><?php echo $title; ?></h3><br />  
           <div class="table-responsive">  
                <br />  
                <table id="user_data" class="table table-bordered table-striped">  
                     <thead>  
                          <tr>  
                               <th width="10%">Image</th>  
                               <th width="35%">First Name</th>  
                               <th width="35%">Last Name</th>  
                               <th width="10%">Edit</th>  
                               <th width="10%">Delete</th>  
                          </tr>  
                     </thead>  
                </table>  
           </div>  
      </div>  
 </body>  
 </html>  
 <script type="text/javascript" language="javascript" >  
 $(document).ready(function(){  
      var dataTable = $('#user_data').DataTable({  
           "processing":true,  
           "serverSide":true,  
           "order":[],  
           "ajax":{  
                url:"<?php echo base_url() . 'crud/fetch_user'; ?>",  
                type:"POST"  
           },  
           "columnDefs":[  
                {  
                     "targets":[0, 3, 4],  
                     "orderable":false,  
                },  
           ],  
      });  
 });  
 </script>  

40 comments:

  1. Thank you so much for this help. But I'd like to know how I'm gonna do join in my queries.

    ReplyDelete
  2. Excelente!!! Muchas gracias por compartir

    ReplyDelete
  3. I get a 403 error on this: table id="user_data".
    I went to datatables.net - all they say is that it is a file permission problem. I checked the files and folders, they are 0644 as stated by datatables.net any help is appreciated.

    ReplyDelete
  4. Hello again,

    I fixed the 403 error!! It is a codeigniter config issue.

    Now I have a 404 error (I use HMVC):

    "ajax":{
    url:"",

    ingredient is the name of my controller.

    In the modules folder, I have a folder (module) named recipes and the ingredient controller is in the controllers folder of the module.

    -app
    -/modules
    -/-/recipe
    -/-/-/controllers
    -/-/-/-/Ingredient.php

    Can I use APP_Path for this or??

    Thanks

    ReplyDelete
  5. Thank you very much for sharing this material. It helped me a lot. But I think you can improve the condition of the make_query () method in the following way: if(isset($_POST["search"]["value"]) && $_POST["search"]["value"] != "")

    ReplyDelete
  6. Where did you get the POST variable that you are using in the Model

    ReplyDelete
  7. below code what is length in make_datatable function in model

    ReplyDelete
  8. DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    ReplyDelete
  9. I got this error, Help pls!!

    Severity: Notice

    Message: Undefined index: length

    Filename: models/Crud_model.php

    Line Number: 26

    Backtrace:

    File: /var/www/html/example.com/public_html/weblesson/application/models/Crud_model.php
    Line: 26
    Function: _error_handler

    File: /var/www/html/example.com/public_html/weblesson/application/controllers/Crud.php
    Line: 15
    Function: make_datatables

    File: /var/www/html/example.com/public_html/weblesson/index.php
    Line: 315
    Function: require_once

    ReplyDelete
  10. Hi, I' have some query, how we can use stored procedures to perform server side processing on Data-Tables ?

    ReplyDelete
  11. Hi, Will you guide me how to use stored procedures to perform server side processing on data-tables.

    ReplyDelete
  12. if i want to fetch data from multiple table how i will do it ?

    ReplyDelete
  13. How i fetch data from multiple table in the code

    ReplyDelete
  14. DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7


    how can i fix this error for above code?

    ReplyDelete
  15. I have tried your code.. but I experience "DataTables warning: table id=mydata - Ajax error. For more information about this error, please see http://datatables.net/tn/7", help me please.. thank you in advanced..

    ReplyDelete
  16. Call to undefined function base_url()

    ReplyDelete
  17. Call to undefined function base_url()

    ReplyDelete
  18. Call to undefined function base_url()

    ReplyDelete
  19. It's all work but it will display only one data in the table. It's redundancy depends how many data are there in the database, please help me!

    ReplyDelete
  20. i'v got an error about unidentified index length and start. please help

    ReplyDelete
  21. thanks so much please create more tutorial in codeigniter

    ReplyDelete
  22. how to add export option buttons in this function

    ReplyDelete
  23. I get an error
    DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7
    and i really don't know how to cope with it.
    Could someone help me
    tks

    ReplyDelete
  24. How to make it join with other tables?

    ReplyDelete
  25. Hello sir, Thanks for the video.
    Every thing is working except filters.
    Please help me.. Thanks in advance

    ReplyDelete
  26. thanks you soo much please create more tutorials in code igniter awsome work

    ReplyDelete
  27. I'm getting this error while copypasting your same code" DataTables warning: table id={id} - Ajax error" https://datatables.net/manual/tech-notes/7. how should i solve it??

    ReplyDelete
  28. DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    face this type of error, how can i fix it???

    ReplyDelete
  29. This is a very good tutorial.

    I have a question, what is the function of the below code and where was the index draw defined
    "draw" => intval($_POST["draw"]),

    ReplyDelete