DataTables warning: table id=example - Invalid JSON response. For more information about this error,
DataTables warning: table id=example - Invalid JSON response. For more information about this error,
tester1
Posts: 53Questions: 14Answers: 1
Getting error " DataTables warning: table id=example - Invalid JSON response. For more information about this error, "
HTML
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Position</th>
<th>Office</th>
<th>Start date</th>
<th>Salary</th>
<!-- <th>Edit</th> -->
</tr>
</thead>
<tfoot>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Position</th>
<th>Office</th>
<th>Start date</th>
<th>Salary</th>
<!-- <th>Edit</th> -->
</tr>
</tfoot>
</table>
script
<script>
$(document).ready(function() {
$('#example').dataTable( {
"pagingType": "full_numbers",
"scrollY": 380,
"scrollX": true,
"processing": true,
"serverSide": true,
"ajax":{
"url": "server_processing.php",
"dataType": "jsonp"
}
</script>
server_processing.php
<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'memo_name', 'add_by_date', 'add_by_userid', 'add_by_id', 'add_by_datetime' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";
/* DB table to use */
$sTable = "memodn";
/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "";
$gaSql['db'] = "test";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}
/*
* MySQL connection
*/
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );
}
if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );
}
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc').", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
{
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' */
$sOutput .= ($aRow[ $aColumns[$i] ]=="0") ?
'"-",' :
'"'.str_replace('"', '\"', $aRow[ $aColumns[$i] ]).'",';
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$sOutput .= '"'.str_replace(
array( '"', "\n", "\r" ),
array( '\\"', "\\n", "\\n"),
$aRow[ $aColumns[$i] ] ).'",';
}
}
/*
* Optional Configuration:
* If you need to add any extra columns (add/edit/delete etc) to the table, that aren't in the
* database - you can do it here
*/
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';
echo $sOutput;
<?php
>
?>
This discussion has been closed.
Answers
please help me ... i am new to datatables ... i was using tablesorter but moved to datatables for more advance features ... i want to show table which 3-4 tables join... firstly i was using http://datatables.net/examples/server_side/simple.html and its working properly .. but i am not aware how to call 3-4 join table in this .. hence search and got above php code but its not working ... i know in http://datatables.net/examples/server_side/simple.html there is ssp.class.php and i can change there code with join structure ... but i am working on bigger project with N numbers of folder and .php files .. hence for each i cant create once .php and another ssp.class.php for each ..hence it would be easy if above works so that all will be in single .php file ... sorry for my english ..but pls help me ...
Did you go to the tech note linked to in the error message? What is being returned by the server? It isn't JSON, so we need to know what it is. Likely it contains an error message.
Also, in future, please link to a test case, as required in the forum rules.
Allan
hey allan , thanks for reply .. and sorry .. but i think your documentation on each examples is half .. like if some one want to use JOIN in http://www.datatables.net/development/server-side/php_mysql .. its giving error .. as a fresher it hard for us to make changes in such files .. but yes where as table-sorter was easy in such conditions .. we can make query ( with join etc ) easy ..search easy ... but here i am struggling for same ... sorry but it true .. i think u have u have provide only few examples ... not all basic ( like , all in on package example of : SEARCH , PAGINATION , QUERY WITH ANY JOIN ETC .. ) which are need in today coding life .. I AM ONLY TAKING ABOUT tableview structure... any way thanks.. i understand why u hiding many thing :)
The example you linked to is not designed for working with a Join. It is really a starting point for you to take an modify as you require.
There is a modification to that script which does do joins: http://datatables.net/dev/server_processing.txt , but again, it is a starting point for you to modify as you need.
The Editor PHP libraries do support joins for server-side processing if you require supported software.
Allan
Hey Allan , you know what .. why i shifted from tablesorter to datatables is for more easiness ... i found datatables has many features .. which take project to another level ... and really it is ... i like datatables table view with many other fetaures ...
but the thing is as a fresher.. i find difficult to merge all in one ...and in that also some works and some not... like i was working with
new $.fn.dataTable.FixedColumns( dt );new $.fn.dataTable.KeyTable( dt );and i found that its working but its getting collide with ROW DETAILS example ... means show hide is not working if i put above query ... hence i hide that and my show/hide started working ... anyways ... but i am exploring it more and more ... thanks
Yeah - there are a few limitations between there extensions and the features they offer. I'm going to be creating a page which documents the conflicting features soon as it is important to note these.
Allan