Server side info (Showing X to Y of Z entries) incorrect

WeaponX86

Is our JSON response correct? In this example we are showing 250 entries per page. I took a screenshot of the entry count and JSON response for pages 1-3 in this example.

http://imgur.com/8fA2gvR,h2TN6YC,XKrCijP

allan

The documentation says:

> iTotalRecords - Total records, before filtering (i.e. the total number of records in the database)

> iTotalDisplayRecords - Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned in this result set)

If your script is returning those numbers and it still isn't working, please link to a test case showing the issue so I can debug it.

Allan

WeaponX86

iTotalRecords and iTotalDisplay Records both show the total number of records in the set which in the screenshots was 1,048.

If we set bStateSave to false the entry counts are correct. We are using the fnStateSave callback from your example here verbatim:
https://datatables.net/docs/DataTables/1.9.beta.2/DataTable.defaults.html#fnStateSave

CODE REMOVED See followup post

WeaponX86

Okay I built a stripped down reproducer for you using PHP/sqlite:
https://dev.propertypreswizard.com/test_scripts/beta/ajax_test2.php

For the server side code I used your server side script and converted it to run on sqlite.
[code]
<?php
ini_set( 'display_errors', true );
error_reporting( E_ALL );

session_start();
//echo session_id();

//$db = new SQLite3(':memory:');
$db = new SQLite3('db.sqlite');

if (isset($_REQUEST['event']) && $_REQUEST['event'] == "stateSave"){
stateSave();
} elseif (isset($_REQUEST['event']) && $_REQUEST['event'] == "stateLoad"){
stateLoad();
} else {

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "work_orders";

$contents1 = file_get_contents('ajax_columns.json');
$json1 = json_decode($contents1);

$aColumns = array();
foreach ($json1 as $k => $v){
$aColumns[] = $k;
}

$sColumns = implode(',',$aColumns);

//build();


/* $sql = "SELECT * FROM ajax";
$result3 = $db->query($sql);
while ($row = $result3->fetchArray()) {
var_dump($row);
}*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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( 'engine', 'browser', 'platform', 'version', 'grade' );

/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $iquery($sQuery) or die($db->lastErrorMsg());

$aResultFilterTotal = $rResultFilterTotal->fetchArray();
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
SELECT COUNT(`".$sIndexColumn."`)
FROM $sTable
";
$rResultTotal = $db->query($sQuery) or die($db->lastErrorMsg());
$aResultTotal = $rResultTotal->fetchArray();
$iTotal = $aResultTotal[0];

/*
* Output
*/
$output = array(
//"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = $rResult->fetchArray() )
{
$row = array();
for ( $i=0 ; $iexec("DROP TABLE `state`");

$sql = "CREATE TABLE `state` (`session_id` TEXT PRIMARY KEY,`data` BLOB)";
$db->exec($sql) or die($db->lastErrorMsg()."
");

$db->exec("DROP TABLE `".$sTable."`");

$sql = "CREATE TABLE IF NOT EXISTS `".$sTable."` (
`id` INTEGER PRIMARY KEY";
foreach ($aColumns as $sColumn){
$sql .= sprintf(",`%s` varchar(255) NOT NULL default ''",$sColumn);
}
$sql .= ");";

/* $sql = "CREATE TABLE IF NOT EXISTS `ajax` (
`id` INTEGER PRIMARY KEY,
`engine` varchar(255) NOT NULL default '',
`browser` varchar(255) NOT NULL default '',
`platform` varchar(255) NOT NULL default '',
`version` float NOT NULL default '0',
`grade` varchar(20) NOT NULL default ''
);";*/

$db->exec($sql) or die($db->lastErrorMsg()."
");

//$db->exec("DELETE FROM ".$sTable);

$contents1 = file_get_contents('ajax_sample.json');
$json2 = json_decode($contents1);
//print_r($json2->aaData);
$aaData = $json2->aaData;

$aRows = array();

// Convert object into array
foreach ($aaData as $k => $v){
$aRows[$k] = $v;
}

//echo "";
//var_dump($aRows);
//echo "";

foreach ($aRows as $aRow){

$sql = "INSERT INTO work_orders (".$sColumns.") VALUES (";

unset ($aTemp);

foreach ($aColumns as $sColumn){
$aTemp[] = $db->escapeString($aRow->$sColumn);
}

$sql .= "'".implode("','",$aTemp)."')";

/* $sql = "
INSERT
INTO ajax ( engine, browser, platform, version, grade )
VALUES (".$values.");";
*/
//echo $sql."
";
$result2 = $db->exec($sql) or die($db->lastErrorMsg());
//echo $db->lastErrorMsg()."
";
}
}

function stateSave() {
global $db;

ob_clean();

header('Content-Type: application/json');

$dataTable_state = json_encode($_POST);

$sql = sprintf("REPLACE INTO `state` (session_id, data) VALUES ('%s','%s')",session_id(),$dataTable_state);
$db->exec($sql) or die($db->lastErrorMsg());

exit;
}

function stateLoad() {
global $db;

ob_clean();

header('Content-Type: application/json');

$sql = sprintf("SELECT * FROM `state` WHERE session_id = '%s'",session_id());
$result = $db->query($sql) or die($db->lastErrorMsg());

$row = $result->fetchArray();

echo $row['data'];

exit;
}
?>
[/code]

allan

Thanks for the test case. I don't think there is anything wrong with your server-side processing script, it looks like the state saving that is going wrong.

It looks like the iStart parameter is being given as a string, and not an integer. I think this is probably because it is being sent as a plain HTTP variable which of course has no type.

What I'd suggest you do is `JSON.stringify` the state object before sending it to the server and then using that to save and return. Then type is retained.

I'll update my example to reflect this.

Allan