Search code examples
jquerydatatablescodeigniter-3

datatables.draw() does not draw the table


I am fetching result from database in codeigniter and showing it in datatables using datatables.draw() method, everything works, ajax response is working fine, filters are working but datatable not getting draw and it is showing blank, when i inspect element, i can see the data hidden in row and columns.

here is my html code

                  <div class="dropdown bootstrap-select show-tick bs3" style="width: 100%;">
                    <select class="selectpicker" id="sel_lang" data-width="100%" data-none-selected-text="Filter by Language" tabindex="-98">
                      <option value="">Select language</option>
                       <option value="en">English</option>
                       <option value="es">Spanish</option>

                  </select>
              </div>
      

                  <div class="dropdown bootstrap-select show-tick bs3" style="width: 100%;">
                    <select class="selectpicker" id="sel_dest" data-width="100%" data-none-selected-text="Filter by Destination" tabindex="-98">
                      <option value="">Select Destination</option>
                      <option value="India">India</option>
                      <option value="Nepal">Nepal</option>
                      <option value="Nepal">Bhutan</option>
                  </select>
              </div>

              <table class="apitable table dt-table" id="tourlist_table">
                 <thead>
                    <th><?php echo _l('tour_id'); ?></th>
                    <th><?php echo _l('tour_name'); ?></th>
                    <th><?php echo _l('tour_price'); ?></th>
                    <th><?php echo _l('tour_countries'); ?></th>
                 </thead>
               </table>

this is my jquery code

<script type="text/javascript">

   $(document).ready(function(){

   var tourlistDataTable = $('#tourlist_table').DataTable({
     'bDestroy': true,
     'processing': true,
     'serverSide': true,
     'serverMethod': 'post',
     "searching": true,
     'ajax': {
        'url':'<?php echo admin_url('tourismo/tourismo/gettotaltours'); ?>',
        'data': function(data){
           data.searchlang = $('#sel_lang').val();
           data.searchDest = $('#sel_dest').val();
        }
     },
     'columns': [
        { data: 'tourid' },
        { data: 'tourname' },
        { data: 'tourprice' },
        { data: 'countries' },
     ]
   });

   $('#sel_lang,#sel_dest').change(function(){
      tourlistDataTable.draw();
   });
});
</script>

this is my controller

    public function gettotaltours()
{
  // POST data
 $postData = $this->input->post();

  // Get data
  $data = $this->tourismo->fetch_total_tours($postData);

  echo json_encode($data);
}

this is my model

    public function fetch_total_tours($postData=null)
{
    $response = array();

    ## Read value
    $draw = $postData['draw'];
    $start = $postData['start'];
    $rowperpage = $postData['length']; // Rows display per page
    $columnIndex = $postData['order'][0]['column']; // Column index
    $columnName = $postData['columns'][$columnIndex]['data']; // Column name
    $columnSortOrder = $postData['order'][0]['dir']; // asc or desc
    $searchValue = $postData['search']['value']; // Search value

    // Custom search filter
     $searchLang = $postData['searchlang'];
     $searchDest = $postData['searchDest'];


     ## Search
    $search_arr = array();
    $searchQuery = "";
    if($searchValue != ''){
       $search_arr[] = " (tourname like '%".$searchValue."%' or
        countries like '%".$searchValue."%' or
        tourplaces like'%".$searchValue."%' ) ";
    }
    if($searchLang != ''){
       $search_arr[] = " language='".$searchLang."' ";
    }
    if($searchDest != ''){
       $search_arr[] = " countries like '%".$searchDest."%' ";
    }

    if(count($search_arr) > 0){
       $searchQuery = implode(" and ",$search_arr);
    }

    ## Total number of records without filtering
     $this->db->set_dbprefix('');
     $this->db->select('count(*) as allcount');
     $records = $this->db->get('tourtable')->result();
     $totalRecords = $records[0]->allcount;

     ## Total number of record with filtering
     $this->db->select('count(*) as allcount');
     if($searchQuery != '')
     $this->db->where($searchQuery);
     $records = $this->db->get('tourtable')->result();
     $totalRecordwithFilter = $records[0]->allcount;

     ## Fetch records
     $this->db->select('*');
     if($searchQuery != '')
     $this->db->where($searchQuery);
     $this->db->order_by($columnName, $columnSortOrder);
     $this->db->limit($rowperpage, $start);
     $records = $this->db->get('tourtable')->result();

     $data = array();

     foreach($records as $record ){

       $data[] = array(
         "tourid"=>$record->tourid,
         "tourname"=>$record->tourname,
         "tourprice"=>$record->tourprice,
         "countries"=>$record->countries,
         "banner"=>$record->banner,
         "duration"=>$record->duration,
         "slug"=>$record->tourslug
       );
     }

     ## Response
    $response = array(
      "draw" => intval($draw),
      "iTotalRecords" => $totalRecords,
      "iTotalDisplayRecords" => $totalRecordwithFilter,
      "aaData" => $data
    );

    return $response;

}

Sample of Response what i got by datatable ajax

   {
  "draw": 3,
  "iTotalRecords": "58",
  "iTotalDisplayRecords": "3",
  "aaData": [
    {
      "tourid": "97",
      "tourname": "16 Días Nepal Bhutan Y India ",
      "tourprice": "",
      "countries": "India,Nepal,Bhutan",
      "banner": "https://www.myurl.com/upload/highlights-of-nepal.jpg",
      "duration": "16",
      "slug": "16-dias-nepal-bhutan-y-india"
    },
    {
      "tourid": "104",
      "tourname": "6 Días Nepal Viajes",
      "tourprice": "",
      "countries": "Nepal",
      "banner": "https://www.myurl.com/upload/highlights-of-nepal.jpg",
      "duration": "6",
      "slug": "6-dias-nepal-viajes"
    },
    {
      "tourid": "105",
      "tourname": "11 Dias Nepal Viajes",
      "tourprice": "",
      "countries": "Nepal",
      "banner": "https://www.myurl.com/upload/pokhra-annapurna-tour-nepal.jpg",
      "duration": "11",
      "slug": "11-dias-nepal-viajes"
    }
  ]
}

This is how its looking ( A blank table )

current_look

When i inspect element and change table_loading class opacity from 0 to 1, This show that data is there, but it is not able to draw final table.

inspector_image

Any Help would be thankful.


Solution

  • Update**

    I solved this by replacing old jquery datatable version with latest version.

    Just Included this in view and it worked as expected.

        <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>