DataTables warning: table id=example - Invalid JSON response.
DataTables warning: table id=example - Invalid JSON response.
jiejiang
Posts: 12Questions: 2Answers: 0
Link to test case:
Debugger code (debug.datatables.net): https://debug.datatables.net/inedez
Error messages shown: DataTables warning: table id=example - Invalid JSON response.
Description of problem:
Hello,
I kept having "DataTables warning: table id=example - Invalid JSON response." message for my code. I have tried all the method I found but it still didn't work.
I tried:
- check the number of columns in the database matches the html table;
- add charset=utf8 to ssp.class.php;
- removed abnormal characters;
html:
<body>
<div>
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>Chromoson</th>
<th>Position</th>
<th>Strand </th>
<th>Cell </th>
<th>Condition</th>
<th>Modification</th>
<th>Software</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Chromosome</th>
<th>Position</th>
<th>Cell </th>
<th>Condition</th>
<th>Modification</th>
<th>Software</th>
</tr>
</tfoot>
</table>
</div>
</body>
<script>
$(document).ready(function() {
$('#example').DataTable( {
"processing": true,
"serverSide": true,
"deferRender": true,
"ordering": false,
"ajax": {url : "example.php",
type: "POST",
dataType: "JSON",},
"columns": [
{ "data": "chromosome" },
{ "data": "position" },
{ "data": "cell" },
{ "data": "condition" },
{ "data": "modification" },
{ "data": "software" }
]
} );
} );
</script>
php:
// DB table to use
$table = 'data';
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'chr', 'dt' => 0 ),
array( 'db' => 'pos', 'dt' => 1 ),
array( 'db' => 'cell', 'dt' => 3 ),
array('db' => 'condition','dt' => 4 ),
array('db' => 'modification','dt' => 5),
array('db' => 'software','dt' => 6)
);
// SQL server connection information
$sql_details = array(
'host' => 'localhost',
'user' => 'root',
'pass' => '',
'db' => '******'
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'ssp.class.php' );
echo json_encode(
SSP::complex( $_POST, $sql_details, $table, $primaryKey, $columns)
);
If i run the php file, I got:
{"draw":0,"recordsTotal":117,"recordsFiltered":117,"data":[{"0":"chr1","1":631704,"3":"H9","4":"FAK14675","5":"m6A","6":"ELIGOS"},{"0":"chr1","1":631714,"3":"H9","4":"FAK14675","5":"m6A","6":"ELIGOS"},
also if i use SSP::complex, the result had "draw":0 but if i use SSP::simple it changed to "draw":1. But either way I still have invalid json error message.
Could someone please help me with it? Thank you very much in advance!
Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.
This question has an accepted answers - jump to answer
Answers
It looks like you've got an additional column in the head, "Strand", that isn't in footer or the
columns- that's likely to be the issue,Colin
Hi Colin,
Thank you very much for your quick answer. I deleted the footer and changed the code. But it didn't fix it.
$table = 'data';
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The
dbparameter represents the column name in the database, while thedt// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'chr', 'dt' => 0 ),
array( 'db' => 'pos', 'dt' => 1 ),
array( 'db' => 'cell', 'dt' => 3 ),
array('db' => 'condition','dt' => 4 ),
array('db' => 'modification','dt' => 5),
array('db' => 'software','dt' => 6)
);
// SQL server connection information
$sql_details = array(
'host' => 'localhost',
'user' => 'root',
'pass' => '',
'db' => 'xxxxx'
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'ssp.class.php' );
echo json_encode(
SSP::complex( $_POST, $sql_details, $table, $primaryKey, $columns)
);
It looks like your data is being sent back with numeric keys:
whereas you've defined the data to have strings:
Those need to match - so either change the client code to use the numbers, or the server to use the strings (I'd say go with the strings, as more readable),
Colin
{"draw":1,"recordsTotal":117,"recordsFiltered":117,"data":[{"chr":"chr1","pos":631704,"cell":"H9","condition":"FAK14675","modification":"m6A","software":"ELIGOS"},{"chr":"chr1","pos":631714,"cell":"H9","condition":"FAK14675","modification":"m6A","software":"ELIGOS"},{"chr":"chr1","pos":631848,"cell":"H9","condition":"FAK14675","modification":"m6A","software":"ELIGOS"},{"chr":"chr1","pos":632344,"cell":"H9","condition":"FAK14675","modification":"m6A","software":"ELIGOS"},
Still not working
Now what happens?
Looks like your
columns.datadefinition doesn't match the data returned. For example you have{ "data": "chromosome" }but your data has{"chr":"chr1"....Kevin
I changed the columns data as well
$(document).ready(function() {
$('#example').DataTable( {
"processing": true,
"serverSide": true,
"ajax": {url : "example.php",
type: "GET",
dataType: "JSON",},
"columns": [
{ "data": 'chr'},
{ "data": 'pos'},
{ "data": 'cell' },
{ "data": 'condition' },
{ "data": 'modification' },
{ "data": 'software'}
]
Same error message....
I also changed it to numerical value, 0,1,2,3. Didn't work either.
I'm not familiar with PHP so won't be much help but the response should contain the JSON you posted above but it looks like the response is the actual PHP script. Maybe others on the forum familiar with PHP can help debug why. Have you looked at your server log for errors?
Kevin
As Kevin said, the PHP script is being returned, and not being executed. That's a server config issue (I had the same issue recently) - see this SO thread, it has steps to resolve,
Colin
Thank oyu so much Colin!!!!!! It's indeed that my Apache didn't have proper php loaded! It works now.
Thank you both for your kindly help!