Issue Passing Variables From Generated ServerSide JSON To Clientside Ajax

Tabloid

Hi there!

Currently Moved My Datatables from being loaded client side to use serverside functionality instead (for speed), I am trying to get the sum of the entire column from the server and passing that information to the client. Here is a part of the codebase I have now.

$('#dynamictable').DataTable({
        "ajax":{
            url:'serverside.php',

        } ,
        "dataSrc": function (jsonString) {
                       var jsonData = JSON.parse(jsonString);
                       return jsonData.data;
        },
        "processing": true,
        "serverSide": true,
        "dataType": "json",
        "autoWidth": true,
        "colReorder": true,
        "keys": true,
        "deferRender": true,
        "ordering": true,

 //Adds Top Calculation (Client Side)
        "fnDrawCallback": function ( row, data, start, end, display ) {
            const table = $('#dynamictable').DataTable;
            var api = this.api(), data;
 
            // Remove the formatting from val
            var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
            
            var disp_count = 0;
            var currentcolpos = "";
            var card_built = false;
            const sumlist = [];
            const keysumwords = ["Subtotal", "Total", "HST", "Balance Subtotal", "Balance HST", "Balance Total"];
            

            this.api()
            .columns()
            .every(function () {

                let column = this;
                let title = column.header().textContent;


                if (keysumwords.includes(title)){
                    if (card_built == false){
                        var element = document.getElementById("sum_card");
                        element.classList.add("card");
                        card_built = true;
                    }
                    //Result Total (Variable Im hoping to replace with the serverside result)
                    total = api
                        .column( this.index(), {search:'applied'})
                        .data()
                        .reduce( function (a, b) {
                            return intVal(a) + intVal(b);
                        }, 0 );

                        underscored_title = title.split(' ').join('_');
                        divid = 'sum_'+ underscored_title;

                        if (disp_count % 3 == 0){
                            var newDiv = document.createElement('div');
                            colid = 'sum_'+ disp_count;
                            currentcolpos = colid;
                            newDiv.setAttribute("id",colid);
                            newDiv.setAttribute("class","col-4");
                            document.getElementById('Summary').appendChild(newDiv);

                            var newDiv2 = document.createElement('div');
                            newDiv2.setAttribute("id",divid);
                            document.getElementById(colid).appendChild(newDiv2);
                            $(`#${divid}`).html(`<b>${title}:</b> <span class="dollars">`+ numberWithCommas(total.toFixed(2)) + '</span>');
                            sumlist.push(`${title}:${total.toFixed(2)}`)
                        }
                        else{
                            var newDiv2 = document.createElement('div');
                            newDiv2.setAttribute("id",divid);
                            document.getElementById(currentcolpos).appendChild(newDiv2);
                            $(`#${divid}`).html(`<b>${title}:</b> <span class="dollars">`+ numberWithCommas(total.toFixed(2)) + '</span>');
                            sumlist.push(`${title}:${total.toFixed(2)}`)
                            
                        }

                        disp_count += 1;
                }
        
            
            });
        }
 }

I have edited the ssp.class.php file to provide me with the data and the json output looks something like this:

{"draw":1,"recordsTotal":12,"recordsFiltered":12,"Sum":[{"sum_Subtotal":"200"},{"sum_HST":"100"},{"sum_Total":"100"},{"sum_Balance_Subtotal":"300"},{"sum_Balance_HST":"20.34"},{"sum_Balance_Total":"70.35"}]

(data portion has been removed but is present in the json and tables display properly, JSON is validated)

//ssp.class.php modified section

    $keysumwords = ["Subtotal", "Total", "HST", "Balance_Subtotal", "Balance_HST", "Balance_Total"];
        include 'base/config/dbconnect.php'; // DB Connect file inclusion

        $colNameQuery = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '".$dbname."' AND table_name = ?";
                        $colName = $conn->prepare($colNameQuery);
                        $colName->execute([$table]);
                        $colNameCount = $colName->rowCount();
                        $colNames = $colName->fetchAll(PDO::FETCH_ASSOC);
        
        $SumCol = [];

        foreach ($colNames as $name) {
            $colname = $name['COLUMN_NAME'];
            
            if (in_array($colname, $keysumwords)){

                $divid = 'sum_' . $colname;
                $Sum = self::sql_exec( $db, $bindings,
                    "SELECT SUM(`$colname`)
                    FROM   `$table`
                    $where"
                );
                $array_col = array( $divid => $Sum[0][0]);
                array_push($SumCol ,$array_col);

            }

        } 



        /*
         * Output
         */
        return array(
            "draw"            => isset ( $request['draw'] ) ?
                intval( $request['draw'] ) :
                0,
            "recordsTotal"    => intval( $recordsTotal ),
            "recordsFiltered" => intval( $recordsFiltered ),
            "Sum"             => $SumCol,
            "data"            => self::data_output( $columns, $data )
        );
    }

The goal is to access the array passed from the server and displayed to the client eg. data['Sum']['sum_Subtotal'] to get the value of 200, however i'm having trouble even accessing the json properly through some examples on the forum.

The Addtional Code is The Old Client Side Summation Of Relavent Columns And Display, I intend to replace the calculated total with the total already calculated on the serverside and present with the JSON, though its not neccesary relavent as once I can get the variable from json I should be fine, I have included it anyways. The function currently works incorrectly since it only sums whats visible on the page hence needing to be calculated serverside and passed in.

PS. Sorry If i'm not clear enouph with this, this is my first time using datatables and posting on this forum. Also I won't be able to provide as a test case as there is way more code than whats currently posted.

kthorngren

Here are some options to access the JSON data:

1. Use the xhr event which has a json parameter.
2. Or use the ajax.dataSrc which gives access to the JSON data.
3. Use the ajax.json() API to access the JSON data outside of 1 and 2.

Looks like you might want to use this in drawCallback. You can do something like this:

  drawCallback: function () {
    var api = this.api();
    
    console.log(api.ajax.json());
  }

Running example:
https://live.datatables.net/qoliyehi/45/edit

Kevin

Tabloid

Thank you so much! Can't believe it was that simple! Been scratching my head at this for too long.