Server side, how can i have the total table length data?

Maxilboss93

This my datatable situation,:

in ajax how can i take the length parameter to use for customize for example the aLengthMenu?

$(document).ready(function() {
    FormValidation.init();
    handleDatePickers();
    var dataSet = [];

    var oTable = $table.DataTable({
        "scrollY": "200px",
        "scrollCollapse": true,
        "processing": true,
        "serverSide": true,
        "paging": true,
        "info": true,
        "aLengthMenu": [
            [10, 50, 75, 100, 250, 500, -1],
            [10, 50, 75, 100, 250, 500, "All"]
        ],
        "pageLength": 50,
        //"pagingType": "numbers",
        //                    "select": true,
        "responsive": true,
        "autoWidth": false,
        "language": {
            "sEmptyTable": "Nessun dato presente nella tabella",
            "sInfo": "Vista da _START_ a _END_ di _TOTAL_ elementi",
            "sInfoEmpty": "Vista da 0 a 0 di 0 elementi",
            "sInfoFiltered": "(filtrati da _MAX_ elementi totali)",
            "sInfoPostFix": "",
            "sInfoThousands": ".",
            "sLengthMenu": "Visualizza _MENU_ elementi",
            "sLoadingRecords": "Caricamento...",
            "sProcessing": "Elaborazione...",
            "sSearch": "Cerca:",
            "sSearch[value]": "",
            "sZeroRecords": "La ricerca non ha portato alcun risultato.",
            "oPaginate": {
                "sFirst": "Inizio",
                "sPrevious": "Precedente",
                "sNext": "Successivo",
                "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": "",
        },
        "fnInitComplete": function() {
            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" }
        ],
        "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) {
            $.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;
            });
        },


    });

colin

Hi @Maxilboss93 ,

Are you asking for the total number of records? If so, this is part of the response, recordsTotal, which you can get at any time with ajax.json(),

Cheers,

Colin

Maxilboss93

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

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


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

table result empty!!!
but json it's full!

colin

Are you also including draw? The protocol is discussed here.

Cheers,

Colin

Maxilboss93

<?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;
$rows = $myDB->class_arraySQL($cSQLTot);
$total = $rows[0]['TOTAL'];
//echo $total . ", " . $start . ", " . $length;
//echo $cSQL;
if (!$asc) $initRow = $total - $start;

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


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

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

this is my php page of ajax,

if i use $rows there isn't info of length, if i wanna this info i can use array of array,
$json_data = array( "rows" => $rows, "recordsTotal" => intval( $total ), "recordsFiltered" => intval( $total ) );
but there are many problems to format the data.

allan

I don't really understand the original question I'm afraid. You want to show the number of rows in the table as an option in the length menu? Why, when you already have the "All" option?

Allan