DataTables with Codeigniter MYSQL query

Question

I'm trying to display three data tables as shown on this page with the variant of getting the data from my database, as shown here.

I first tested the page with the static data (from arrays.txt - first link) and it worked fine. However I'm now struggling with the MYSQL data and the JSON. An info message showing "Processing..." shows up but the tables stay empty.

My Javascript:

$(document).ready(function(){
        $('a[data-toggle="tab"]').on( 'shown.bs.tab', function (e) {
        $.fn.dataTable.tables( {visible: true, api: true} ).columns.adjust();
    } );

    $('table.table').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "dataSrc": "Data", // Tried adding this but didn't help
            "url": "/hireStaffController/getDataTable",
            "type": "POST"
        },
        "columns": [
            { "data": "id_staff" },
            { "data": "name_english" },
            { "data": "name_french" },
            { "data": "position" },
            { "data": "efficiency" },
            { "data": "salary" }
        ]
    } );  
}

My controller:

public function getDataTable(){
   $data = $this->staffModel->get_all_staff_DB();   
   echo json_encode($data);     
}

My Model:

public function get_all_staff_DB(){
  $query = $this->db
        ->select('*')
        ->from('game_staff')
        ->get();
  return $query->result();
}

The JSON Response from Firebug seems correct:

   [{
        "id_staff": "1",
        "name_english": "Ski patrol 1",
        "name_french": "Pisteur secouriste 1",
        "position": "skipatrol",
        "efficiency": "50",
        "salary": "1500"
    }, {
        "id_staff": "10",
        "name_english": "Bus driver 2",
        "name_french": "Chauffeur de bus 2",
        "position": "driver",
        "efficiency": "55",
        "salary": "1380"
    }]

Firebug throws this error:

TypeError: c is undefined
...iRecordsDisplay=parseInt(f,10);d=0;for(e=c.length;d<e;d++)N(a,c[d]);a.aiDisplay=...
                                           ^

So I've tried to add "dataSrc": "Data", in the Ajax, as described here but no luck, same error. WHat is this Data? I tried with a small "d" as well.

Can you see what is wrong?

My HTML code:

<div class="tab-pane active" id="tab-table1">
    <table id="myTable1" class="table table-striped table-bordered" cellspacing="0" width="100%">
         <thead>
             <tr>
                 <th>Name</th>
                 <th>Position</th>
                 <th>Office</th>
                 <th>Extn.</th>
                 <th>Start date</th>
                 <th>Salary</th>
             </tr>
          </thead>
      </table>
  </div>
<div class="tab-pane" id="tab-table2">
    <table id="myTable2" class="table table-striped table-bordered" cellspacing="0" width="100%">
             //same ...
     </table>
</div>

if you are still stuck there are some excellent libraries. i am using this one with CI 3: https://github.com/zepernick/Codeigniter-DataTables his blog: http://www.paulzepernick.com/open-source/codeigniter/codeigniter-datatable-library/ — cartalot, May 19 '16 at 17:59

score 3 · Accepted Answer · answered May 19 '16 at 18:43

try including "Data" as key to the echoed data:

public function getDataTable(){
   $data = $this->staffModel->get_all_staff_DB();   
   echo json_encode(array('Data' => $data));     
}

When you specify dataSrc as "Data", datatables looks for a "Data" property in the json returned by the ajax call, and uses that property as source for the table.

score 0 · Answer 2 · answered May 19 '16 at 17:14

I hope the HTML table with is present there. Try using ID instead of class.
Everything else you have done looks fine.

$('#ID').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "/hireStaffController/getDataTable",
            "type": "POST"
        },
        "columns": [
            { "data": "id_staff" },
            { "data": "name_english" },
            { "data": "name_french" },
            { "data": "position" },
            { "data": "efficiency" },
            { "data": "salary" }
        ]
    } );

I tried with `$('#myTable1').DataTable( {` but it doesn't change anything. I added my HTML to the question — remyremy, May 19 '16 at 17:28

DataTables with Codeigniter MYSQL query

2 Answers2