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

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

WeaponX86WeaponX86 Posts: 40Questions: 0Answers: 0
edited March 2014 in DataTables 1.9
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

Replies

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • WeaponX86WeaponX86 Posts: 40Questions: 0Answers: 0
    edited March 2014
    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
  • WeaponX86WeaponX86 Posts: 40Questions: 0Answers: 0
    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]
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
This discussion has been closed.