remyremy remyremy - 6 months ago 149
MySQL Question

DataTables with Codeigniter MYSQL query

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>

Answer

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.

Comments