Bootstrap DataTable(Pagination) with MySQl in Codeigniter PHP

0

Most of time, we need to display large amount of data in the table. If we load all data in a page then, it takes too much time. To reduce load time to render data in table and getting data from MySQL, we will integrate Bootstrap DataTable with MYSQL using JQUERY, PHP in Codeiniter Framework. We will display pagination. We will define quantity of data in a page which request to MySQL with every pagination link.

FOR LIVE DEMO CLICK HERE

Bootstrap DataTable


In this tutorial, we will provide database Table, we will learn how to request data table to controller using Ajax.



Create Database Table

Below, We will provide MySql Command to create new table with Data. If you have already created table then ignore it.

--
-- Table structure for table `order_details`
--

CREATE TABLE `order_details` (
  `id` int(11) NOT NULL,
  `order_id` varchar(128) NOT NULL,
  `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(60) NOT NULL,
  `city` varchar(60) NOT NULL,
  `status` varchar(128) NOT NULL,
  `customer_payable` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `order_details`
--

INSERT INTO `order_details` (`id`, `order_id`, `order_date`, `name`, `city`, `status`, `customer_payable`) VALUES
(1, '000012457', '2017-07-14 13:53:27', 'Ram', 'Mumbai', 'Completed', '450.00'),
(2, '000012457', '2017-07-15 14:53:27', 'Abhay Anand', 'Delhi', 'Completed', '250.00'),
(3, '000012457', '2017-07-16 13:30:00', 'Williams', 'Surat', 'Pending', '300.00'),
(4, '00001258', '2017-07-18 13:53:27', 'Nilanjan ', 'Kolkatta', 'Pending', '500.00'),
(5, '00001258', '2017-07-20 13:53:27', 'Sachin Kumar', 'Allahabad', 'Pending', '0.00'),
(6, '000012459', '2017-08-01 13:53:27', 'Anuj Aggrawal', 'New Delhi', 'Completed', '250.00'),
(7, '000012459', '2017-08-03 13:53:27', 'Nitin Malhotra', 'Indirapuram', 'Completed', '0.00'),
(8, '00001256', '2017-08-03 19:53:27', 'Devendar Singh', 'Gurgaon', 'Completed', '250.00'),
(9, '00001256', '2017-08-08 13:53:27', 'Deepak Jaiswal', 'Noida', 'Completed', '0.00'),
(10, '000012457', '2017-08-10 13:53:27', 'Vikash Singh', 'Fridabad', 'Completed', '300.00'),
(11, '000012457', '2017-08-12 13:53:27', 'Aditya Gupta', 'Patna', 'Cancelled', '250.00'),
(12, '00001258', '2017-08-13 12:53:27', 'Vijya', 'Ranchi', 'Cancelled', '0.00'),
(13, '000012458', '2017-08-14 09:35:00', 'Ranju ', 'Jabalpur', 'Completed', '0.00'),
(14, '000012459', '2017-08-14 13:53:27', 'Ranju ', 'Jabalpur', 'Cancelled', '0.00');

ALTER TABLE `order_details`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `order_details`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Create Controller & Model File

We need to create Orderdetails.php Controller file in the Controller folder an also create Order_model.php in model folder. In this Controller, We will create a script to get POST data. And Also build data for DataTable and return as JSON. Model file is used to get data from Database.

Create & Load View Page

To display table, we need to create a order_view.php file in the view folder. We will load data from Controller.

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

class Orderdetails extends CI_Controller {

    /**
     * load list modal and helpers
     */
    function __Construct() {
        parent::__Construct();

        $this->load->helper(array('form', 'url'));      
        $this->load->model('order_model');

    }
    
    function index(){
        $this->load->view("order_view");
    }
}

Add CSS/JS File in Project

We need to create a table and also use JQUERY to call data from backend(Controller). To use Bootstarp DataTable, we need to load some CSS and JS file. Download CSS & JS and unzip downloaded file to add in your project. Add below code in order_view page:

<link href="https://studytutorial.in/css/bootstrap.min.css" rel="stylesheet">
<script src="<?php echo base_url(); ?>js/jquery.min.js"></script>
<script src="<?php echo base_url(); ?>js/bootstrap.min.js"></script>
<script src="<?php echo base_url(); ?>js/jquery.dataTables.min.js"></script>
<script src=<?php echo base_url(); ?>js/dataTables.bootstrap.min.js"></script>


Create Bootstrap Table

We need to create table that display with pagination. We must have to define id of the Table. In this tutorial, we will use datatable1. Bootstarp DataTable create search bar automatically. We will explain, how to search bar connect to MySql below. There is no need to load data from PHP(Controller). We will use Ajax to send request to server to load data. Use the below code to create the table.

<link href="<?php echo base_url(); ?>css/bootstrap.min.css" rel="stylesheet">
<script src="<?php echo base_url(); ?>js/jquery.min.js"></script>
<script src="<?php echo base_url(); ?>js/bootstrap.min.js"></script>
<script src="<?php echo base_url(); ?>js/jquery.dataTables.min.js"></script>
<script src=<?php echo base_url(); ?>js/dataTables.bootstrap.min.js"></script>


<center><h2>This is an example of Bootstap Data Table. </h2>
    <h3>How To make Table with Pagination that Link with MySql in PHP </h3>
    <h4>Total Count: <span id="countData"></span></h4>
    </center>
<div class="container" style="margin-top: 20px; ">
    <table  id="datatable1" class="table table-striped table-bordered">
        <thead> 
            <tr>
                <th>No.</th>
                <th>Order ID</th>
                <th>Name</th>
                <th>City</th>
                <th>Status</th>
                <th>Charge</th>
                <th>Order Date</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
</div>

AJAX Request to load Data

We will define Length of DataTable and also define column who has no need to sort using JQYERY. Below script is used to send request to server to load data. We do not need to do any extra work in order_view.php Page.

<script>
var datatable;
$(document).ready(function () {
        
        //datatables
        datatable = $('#datatable1').DataTable({
            "processing": true, //Feature control the processing indicator.
            "serverSide": true, //Feature control DataTables' 
                                     server-side processing mode.
            "order": [], //Initial no order.
            "pageLength": 5, // Set Page Length
            // Load data for the table's content from an Ajax source
            "ajax": {
                "url": "https://studytutorial/DemoBootstarp/orderdetails/getdata",
                "type": "POST",
                //You can post any data from here. If you do not need then remove it.
                "data": {"YOUR CUSTOM POST NAME": "YOUR VALUE"}
                
            },
            
            //Set column definition initialisation properties.
            "columnDefs": [
                {
                    "targets": [0,4,7], //first, fourth & seventh column
                    "orderable": false //set not orderable
                }
            ],
           "fnInitComplete": function (oSettings, response) {
            // here you to do after load data
            //response.recordsTotal is used to get Total count data
            $("#countData").text(response.recordsTotal);
          }
            
        });
 });
    
</script>

Model Script

We need to create some method in the model to get data from Database.

<?php

class Order_model extends CI_Model {
    
    var $order = array('order_date' => 'desc'); // default order

    function __construct() {
        parent::__Construct();
    }
    
    function get_order_list($post){
        $this->_get_order_list_query($post);
        if ($post['length'] != -1) {
            $this->db->limit($post['length'], $post['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }
    
    function _get_order_list_query($post){
        $this->db->select("*");
        $this->db->from('order_details');
        
        if(!empty($post['where'])){
            $this->db->where($post['where']);
        }
        
        foreach ($post['where_in'] as $index => $value){
           
            $this->db->where_in($index, $value);
        }
        
        if (!empty($post['search_value'])) {
            $like = "";
            foreach ($post['column_search'] as $key => $item) { // loop column 
                // if datatable send POST for search
                if ($key === 0) { // first loop
                    $like .= "( ".$item." LIKE '%".$post['search_value']."%' ";
                   
                } else {
                    $like .= " OR ".$item." LIKE '%".$post['search_value']."%' ";
                     
                }
             }
             $like .= ") ";

           $this->db->where($like, null, false);
        }

        if (!empty($post['order'])) { // here order processing
            
            $this->db->order_by($post['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 count_all($post){
        $this->_count_all_bb_order($post);
        $query = $this->db->count_all_results();
       
        return $query;
    }
    
    public function _count_all_bb_order($post){
        $this->db->from('order_details');
        $this->db->where($post['where']);
        foreach ($post['where_in'] as $index => $value){
            $this->db->where_in($index, $value);
        }
    }
    
    function count_filtered($post){
        $this->_get_order_list_query($post);
        
        $query = $this->db->get();
        return $query->num_rows();
    }
}

Controller

Below we have provide method that used to call database and build data for DataTable.

  • getdata(): In this method, we will call process_get_data() to get data and also call count of data & Filtered data( which have we searched). Return all these data as a json.

    Note: Do not change the Index name of draw, recordsTotal, recordsFiltered, data.

    draw- Number of request to hit server.
    recordsTotal- Total count of data in Database
    recordsFiltered- Total count of filtered data
    data- The Array which will have to build cell(TD) of the Table.

  • process_get_data(): In this method, we will get input post data and create an array in which we stored where & where in clause(We can store where clause whatever you wan to apply in MySQL query), column_order by which we can sort data, column_search(This is used to search text in database table column whatever we write in the search bar). We will call model method to get filtered data with parameter as array. Finally, create another array that display in cell(TD) of the Table with the help of filtered data.

    Note: column name should have same position as your table in column_order variable. If we do not want to order data from some th of the table then use NULL.

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

class Orderdetails extends CI_Controller {

    /**
     * load list modal and helpers
     */
    function __Construct() {
        parent::__Construct();

        $this->load->helper(array('form', 'url'));      
        $this->load->model('order_model');

    }
    
    function index(){
        $this->load->view("order_view");
    }
    
    function getdata(){
       // log_message("info",  json_encode($_POST));
         $data = $this->process_get_data();
         $post = $data['post'];
          $output = array(
            "draw" => $post['draw'],
            "recordsTotal" => $this->order_model->count_all($post),
            "recordsFiltered" =>  $this->order_model->count_filtered($post),
            "data" => $data['data'],
        );
        unset($post);
        unset($data);
        echo json_encode($output);
        
    }
    
    function process_get_data(){
        $post = $this->get_post_input_data();
        $post['where'] = array( 'order_date >= ' => date('Y-m-d',strtotime("-30 days")));
        $post['where_in'] = array('status' => array('Pending', 'Cancelled', 'Completed'));
        $post['column_order'] = array( NULL, 'order_id','name', 'city', NULL,'customer_payable', 'order_date',NULL);
        $post['column_search'] = array('name','order_id', 'city','order_date','status','customer_payable');
        
        $list = $this->order_model->get_order_list($post);
        $data = array();
        $no = $post['start'];
        
        foreach ($list as $order_list) {
            $no++;
            $row =  $this->order_table_data($order_list, $no);
            $data[] = $row;
        }
        
        return array(
                'data' => $data,
                'post' => $post
                );
    }
    
    function get_post_input_data(){
        $post['length'] = $this->input->post('length');
        $post['start'] = $this->input->post('start');
        $search = $this->input->post('search');
        $post['search_value'] = $search['value'];
        $post['order'] = $this->input->post('order');
        $post['draw'] = $this->input->post('draw');
        $post['status'] = $this->input->post('status');

        return $post;
    }
    
    function order_table_data($order_list, $no){
        $row = array();
        $row[] = $no;
        $row[] = "<a target='_blank' href='#'>$order_list->order_id</a>";

        $row[] = $order_list->name;
        $row[] = $order_list->city;
        $row[] = $order_list->status;
        $row[] = $order_list->customer_payable;
        $row[] = date("d-m-Y",strtotime($order_list->order_date));
        $row[] = "<button class='btn btn-md'>Delete</button>";
        
        return $row;
    }
}