Problems with Json data - DataTabels, database and server sfide process
Problems with Json data - DataTabels, database and server sfide process
Thanks as always for your time and help.
There are many peoples experiencing the error "DataTables warning (table id = 'example'): DataTables" with DataTables and server side processing with Ajax, but I've tried many of them and read all, without any success.
I've also checked dev answers and they say that every time you that error is because you have or a json formatting problem, or a database connection problem or a column selection problem.
Well it seems to me I have none of them.
My code (got from this example: here) is (try.php):
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<!-- DataTables CSS -->
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
<title>test</title>
</head>
<body>
<table id="example">
<thead>
<tr>
<th>ID</th>
<th>country Name</th>
<th>country Currency</th>
</tr>
</thead>
</table>
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
<script>
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing2.php"
} );
} );
</script>
</body>
</html>
And here is server_processing2.php:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Processing</title>
</head>
<body>
<?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('ID', 'country_Name', 'country_Currency' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "ID";
/* DB table to use */
$sTable = "CountryInfos";
/* Database connection information */
$gaSql['user'] = "...";
$gaSql['password'] = "...";
$gaSql['db'] = "...";
$gaSql['server'] = "...";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );
mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_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] ) ]."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
}
}
$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++ )
{
$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 die(mysql_error());
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$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 die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}
echo json_encode( $output );
?>
</body>
</html>
Any try I've done, returned me that error but here is ARRAY I receive from server_processing2.php:
{"sEcho":0,
"iTotalRecords":"287",
"iTotalDisplayRecords":"287",
"aaData":[
["10768","Western Sahara","EH"],["10767","Vietnam","VND"],
["10765","Uzbekistan","UZS"],["10766","Venezuela","VEF"],
["10764","USA","USD"],["10763","Uruguay","UYU"],["10762","United Kingdom","GBP"],
["10761","United Arab Emirates","AED"],["10760","Ukraine","UAH"],
["10759","Uganda","UGX"],["10758","Turkmenistan","TMT"],.........
["10546","Argentina","ARS"],["10545","Albania","ALL"],
["10544","Afghanistan","AFN"]
]
}
So what I can desume is that:
1) my database works correctly (data is correct);
2) Json is well formatted (so it tell Json validator);
3) data arrived to try.php (trunked after some results, but I guess is because DataTable works with 10 results a time...
So why it doesn't work? Sorry but I'm able to understand it alone.
Thx.
Alberto
Answers
Welcome.
First, I see that you are using the legacy syntax and legacy version. If this is your first project, you should use the current versions.
You are pulling your .js file from a CDN that isn't the main dataTables CDN. I'd use the main one if possible.
https://cdn.datatables.net/
For your data source, you have an array called "aaData". This page shows how to tell DT to use a different array in the source using the dataSrc option.
https://datatables.net/examples/ajax/custom_data_property.html
Hi ThomD,
thx for your answer, first of all.
So I've check the two links you have indicated to me and I've updated both CSS and .js files, but it keeps on giving me the same error:
"DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1"...
Can't really understand why... I'll keep on testing and pls if you have any idea share it with me.
Thx.
Well that's really strange.
I've made a test: I've used your code to call a .txt file in which I saved the array I had in return from "server_processing2.php", and what happened?
It worked (obviously I've changed the same of the array to "demo")...
Than I've replaced .txt file with .php server processing file, and again I had the same problem.
It seems It's a matter related to transmission, so that array arriving from php gives problems and array arriving from txt, doesn't give...
Why...?? @_@
Got it...
It was a problem with "html" part of server processing file.
For some reason, transmission of array with "html" part in server side file gives problems.
So in "server_processing.php" leaves on <?php ... ?> code!