Problem with selected row, Server side,

Maxilboss93

I have a problem, when i reload the page I want that my table take in memory the last selected row but doesn,t work, why?
how can i implement my code with this function?

My datatable:

var oTable = $table.DataTable({
    "scrollY": "200px",
    "scrollCollapse": true,
    "processing": true,
    "serverSide": true,
    "paging": true,
    "info": false,
    "aLengthMenu": [
        [10, 50, 75, 100, 250, 500, -1],
        [10, 50, 75, 100, 250, 500, "All"]
    ],
    "pageLength": 50,
    //"pagingType": "numbers",
    "responsive": true,
    "autoWidth": false,
    "language": {
        "sEmptyTable": "Nessun dato presente nella tabella",
        "sInfo": "",
        "sInfoEmpty": "",
        "sInfoFiltered": "(filtrati da _MAX_ elementi totali)",
        "sInfoPostFix": "",
        "sInfoThousands": ".",
        "sLengthMenu": "<div class=elem_tot>Visualizza _MENU_ allievi su  <div class=totale></div></div>",
        "sLoadingRecords": "Caricamento...",
        "sProcessing": "Elaborazione...",
        "sSearch": "Cerca:",
        "sSearch[value]": "",
        "sZeroRecords": "La ricerca non ha portato alcun risultato.",
        "oPaginate": {
            "sFirst": "Inizio",
            "sPrevious": "<img src='assets/img/Arrow_Sx.svg' class='arrow_sx' >",
            "sNext": "<img src='assets/img/Arrow_Dx.svg' class='arrow_dx'>",
            "sLast": "Fine"
        },
        "oAria": {
            "sSortAscending": ": attiva per ordinare la colonna in ordine crescente",
            "sSortDescending": ": attiva per ordinare la colonna in ordine decrescente"
        },
    },
    "ajax": {
        "url": "ajax/mediaweb/getallievi.php",
        "dataSrc": function(json_data) {
            var start = document.getElementById("start_page");
            $(start).text(json_data.start + " , " + json_data.limit);
            var allievitot = document.getElementsByClassName("totale");
            $(allievitot).text(json_data.recordsTotal);
            return json_data.rows;

        },
    }, //end ajax call

    "fnInitComplete": function(index, row, data) {
        positionFirst();

    },
    //function(oSettings, json) {

    //$("#table_paginate").appendTo('div#table_wrapper');

    "columns": [
        { responsivePriority: 1, "width": "75px", "data": "__rank", className: "dt-center" },
        { responsivePriority: 1, "width": "auto", "data": "Allievo", className: "dt-center myAnag" },
        { responsivePriority: 5, "width": "300px", "data": "MediaX", className: "dt-center myMediaX" }, //defaultContent: "<div id='mediaQuiz[]'></div>" },
        { responsivePriority: 3, "width": "300px", "data": "Media", className: "dt-center myMedia" }, //defaultContent: "<div id='mediaEsami[]'></div>" },
        { responsivePriority: 4, "width": "150px", "data": "Giudizio", className: "dt-center" },
        { responsivePriority: 6, "width": "150px", "data": "Patente", className: "dt-center" },
        { responsivePriority: 9, "width": "150px", "data": "ScadFr", className: "dt-center" },
        { responsivePriority: 7, "width": "75px", "data": "LTesti", className: "dt-center" },
        { responsivePriority: 8, "width": "75px", "data": "LAudio", className: "dt-center" }
    ],
    "select": true,
    "createdRow": function(row, data, index) {
        fixPatenteAB1(row, data, index);
        fixDataEsame(row, data, index);
        setGiudizio(row, data, index);
        setFlags(row, data, index);
    },
    "rowCallback": function(row, data, index) {
        $.post("ajax/mediaweb/allieviscuola.php", { "id_allievo": data.Codice, "id_sedeClienteNeca": data.id_sedeClienteNeca }, function(data) {
            if (data.Presente === "0") {
                $(row).addClass("warning");
            }
        });
        $.post("ajax/mediaweb/schedeesamiallievi.php", { "id_allievo": data.Codice, "id_sedeClienteNeca": data.id_sedeClienteNeca }, function(data) {
            data = JSON.parse(data);
            row.cells[3].innerHTML = data.html;
            setGiudizio(row, data, 0);

        });
        $.post("ajax/mediaweb/schedequizallievi.php", { "id_allievo": data.Codice, "id_sedeClienteNeca": data.id_sedeClienteNeca }, function(html) {
            row.cells[2].innerHTML = html;
        });



    },
    "drawCallback": function(settings) {
        var api = new $.fn.dataTable.Api(settings);

        var pagina = Math.ceil(settings._iDisplayStart / settings._iDisplayLength) + 1;

        var paginacorrente = document.getElementById("settings");
        var pagina = $(paginacorrente).text(pagina);
        pagina = pagina[0].innerHTML; //numero di pagina corrente
    }

});

php page where the table fetch the data,

<?php

require('../../includes/config.php');
require('../../includes/necaDB.php');

$start = $_GET["start"];
$length = $_GET["length"];
$search = $_GET["search"];
$order = $_GET["order"];
$columns =$_GET['columns'];

$id_sedeClienteNeca = myLib::sessionParam("id_sedeClienteNeca", 0);
$initRow = $start;
$stepRowIndex = 1;
$sqlOrder = 'ASC ';
$asc = true;
if (isOrderedByRank($order) && $order[0]['dir'] == 'desc') {
$initRow = $start + $length;
$stepRowIndex = -1;
$sqlOrder = 'DESC ';
$asc = false;
}

$limit = '';
if ($length != '-1') {
$limit ='LIMIT '.$start.','.$length;
}
$cSQLHead ='SELECT
@curRow := @curRow + ' . $stepRowIndex . ' AS row_number,
CS.id_sedeClienteNeca,
A.id_allievo Codice,
trim(concat(IFNULL(CS.cognome, ""), " ", IFNULL(CS.nome, ""))) Allievo,
"0" Schede,
"0" Media,
"0" SchedeX,
"0" MediaX,
"Non valutabile" Giudizio,
IFNULL(A.listatoQuiz, "") Patente,
DATE_FORMAT(IFNULL(PR.dataVariazioneStato, "0000-00-00 00:00:00"), "%m-%d-%Y") ScadFr,
IFNULL(LQ.prefisso, "IT") LTesti,
IFNULL(LA.prefisso, "IT") LAudio,
IFNULL(LI.errori, 0) Errori,
LI.Domande,
A.id_allievo Presente ';

$cSQLPart2 = 'FROM allievo A
left join clientescuola CS ON CS.id_clienteScuola = A.id_clienteScuola
left join pratica PR ON PR.id_pratica = A.id_pratica
left join mediaweb MW ON MW.id_mediaWeb = A.id_mediaWeb
left join lingua LQ ON LQ.id_lingua = MW.linguaQuiz
left join lingua LA ON LA.id_lingua = MW.linguaAudio
left join listato LI ON LI.tipo = A.listatoQuiz
JOIN (SELECT @curRow := ' . $initRow . ') r
WHERE A._stato!="D" AND CS.id_sedeClienteNeca = ' . $id_sedeClienteNeca. ' ';

if (isset($search['value']) && $search['value']!=""){
$cSQLPart2 .='AND (CS.nome LIKE "%' . $search['value'] . '%" OR CS.cognome LIKE "%' .$search['value'] . '%") ';
}
$cSQLPart2.='ORDER BY ';
if (isset($order[0]['column']) && $order[0]['column'] > 0) {
$cSQLPart2 .= $columns[$order[0]['column']]['data'] . ' ' . $order[0]['dir'] . ', ';
}
$cSQLPart2.='Allievo ' . $sqlOrder .', ScadFr ' .$sqlOrder . ' ';

if ($length != '-1') {
$cSQLPart2 .= $limit;
}

$cSQLTot = 'SELECT COUNT(*) AS TOTAL FROM allievo A
left join clientescuola CS ON CS.id_clienteScuola = A.id_clienteScuola
WHERE A._stato!="D" AND CS.id_sedeClienteNeca = ' . $id_sedeClienteNeca;

//echo $cSQL;
$myDB = new NecaDB();
$cSQL = $cSQLHead . $cSQLPart2;
$data = $myDB->class_arraySQL($cSQLTot);
$total = $data[0]['TOTAL'];
//echo $total . ", " . $start . ", " . $length;
//echo $cSQL;
if (!$asc) $initRow = $total - $start;

$recordsTotal=intval($total);
$recordsFiltered=intval($total);
$data = $myDB->class_arraySQL($cSQL, false, false, $initRow, $asc);

$json_data = array(
"limit" => $limit,
"start" => intval( $start ),
"recordsTotal" => intval( $total ),
"recordsFiltered" => intval( $total ),
"rows" => $data
);

echo str_replace('},{', '},' . "\n" . '{', json_encode($json_data, JSON_UNESCAPED_UNICODE));

function isOrderedByRank($order) {
return isset($order[0]['column']) && $order[0]['column'] == 0;
}

please help!

colin

Hi @Maxilboss93 ,

You can use stateSave to store the state of the table. By default, it doesn't work with the Select extension, but this thread here shows how it can be easily integrated.

Cheers,

Colin

Maxilboss93

how can i save the state the selected row and selected Page of table?
Reload of the Page restart the page 1 of the table without selection in this moment

colin

Yep, stateSave preserves ordering, paging and searches by default. It just needs the selection added. If you don't want those other things preserved, they can be removed in the same code that adds the selection to the state info.

Maxilboss93

In stateSaveCallback can i modify and Add the selectioin of the row?
in StateLoad Callback how can I add the new parameters?

colin

Sorry, I forgot to include the link in my earlier comment. This is the thread, with this example.

Maxilboss93

Wow, great!!! It's Perfect, Thanks a lot, you are my new Hero!!!!

Maxilboss93

I have a little Problem,
if I load Page the firstTime i wanna see the first page , with this method i show ever the last session selected row!!!

If I delete the cache , when i return on the page there are all rows selected... why? :-(

colin

If I delete the cache , when i return on the page there are all rows selected... why? :-(

On the first load, the savedSelected will be empty, which means the initComplete function will be selecting all rows. You could do a check in that function to only call rows().select() if savedSelect isn't undefined (or whatever the empty value will be).

if I load Page the firstTime i wanna see the first page , with this method i show ever the last session selected row!!!

I don't understand this one - if it's the first time, that should be the same as the above scenario (i.e. no saved state cache). If you can modify my example to demonstrate the problem I'll take a look.

Maxilboss93

Ok the first part it's correct, now it's correct , but there is another problem, when you do for example logout , if u wanna return in page with Login the table load again the last selected row, however i wanna take the first line when i load for the first session and after ,ever the selected row when i go on all button of the page that change page and after return page!

are there method of dataTable or other that are related to session?

colin

You'll need to have some code then to determine whether this is a new session or not. If it is, you can just call

table.row(':eq(0)').select()

to select the first row.

Maxilboss93

Is there a method where if the reload it's doing >10 minutes start with the first page
else start with saved selected data?

colin

Yep, you can set stateDuration - this will invalidate the saved state information after the defined period (default is 2 hours), after which it will load the table like the very first load.

Maxilboss93

Wow, you're really Genius! Thanks a lot!!!
For example if I wanna 30 minutes i can insert stateDuration: 60*30 and the game is made?

colin

Yep, that'll do the trick