load datatable from custom response

Question

I am working on datatables. I have posted 2 questions already related to my this question this and this.

AJAX CALL:

API.call("getBasicCallAnalysisData.json", 'POST', function(data) {
    basicData = data;

    createTable(basicData);
}, function(error) {
    console.log(error);
}, jsonStr);

this my json response from server:

{"msg":null,"code":null,"status":null,"result":[{"aNumber":"3224193861","bNumber":"3217284244","cellID":"410-01-604-30226","dateTime":"2017-06-24 23:08:09.0","duration":801,"imei":"27512671195807","imsi":"35788979525680","operatorId":1,"mscId":"2","fileId":"1"},{"aNumber":"3224193861","bNumber":"3217280585","cellID":"410-01-738-13433","dateTime":"2017-06-24 06:53:13.0","duration":198,"imei":"46341570864238","imsi":"33270572168878","operatorId":2,"mscId":"4","fileId":"2"}],"draw":1,"limit":1000,"recordsFiltered":13442,"recordsTotal":13442}

HTML TABLE :

<table id="table" class="display responsive nowrap" cellspacing="0" width="100%">
                                <thead style="background-color:#303641;">
                                    <tr>
                                        <th>ANUMBER</th>
                                        <th>BNUMBER</th>
                                        <th>DATETIME</th>
                                        <th>DURATION</th>
                                        <th>IMEI</th>
                                        <th>IMSI</th>
                                        <th>CELLID</th>
                                        <th>OPRID</th>
                                        <th>MSC ID</th>
                                        <th>FILE ID</th>
                                    </tr>
                                </thead>
                            </table>

and this is my function to load datatable BUT I am facing two errors:

function createTable(data) {
    console.log(data);
    $('#table').DataTable({
        "processing": true,
        "serverSide": true,
        "bFilter": false,
        "iDisplayLength": configData,
        dom: 'Bfrtip',
        buttons: ['colvis', 'print', 'csv', 'excel', 'pdf'],
        searching: false,
        language: {
            buttons: {
                colvis: 'Show/Hide Columns'
            }
        },
        "aaData": data


    });
}

The parameter data is response from server. Errors are :

DataTables warning: table id=table - Requested unknown parameter 'aNumber' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

and

Invalid JSON Response.

any idea, how Can i populate datatable with JSON response ?

Answer 1

There is two possibility to get your json into DataTables...

1. Use a jQuery Ajax request
2. Use the DataTables remote preocessing

You use the first one.

There is two main problems to your code:

1. A superflous DataTables attribute.
2. A valid JSON, but not structured as DataTables expects it.

#1
The attribute "serverSide": true, basically says that you intend to use the "remote processing". So DataTables expects now an "ajax" attribute holding the PHP address to get the JSON. This missing attribute triggered the "invalid JSON" error.

I hear you shouting loud about unclear error messages now!!! (lol)

#2
DataTables expects the data structured as an array for the whole table...
This array has to hold arrays of "row" values. Example here.

That is not what you actually have.
So I made a function to "re-arrange" the data to satisfy DataTables expectations.

console.clear();

// A function to run trhough your json and restructure the data as an array of array.
function arrangeData(data){
  var datatableData = [];

  for(i=0;i<data.length;i++){
    var row = [
      data[i].aNumber,
      data[i].bNumber,
      data[i].cellID,
      data[i].dateTime,
      data[i].duration,
      data[i].imei,
      data[i].imsi,
      data[i].operatorId,
      data[i].mscId,
      data[i].fileId
    ];
    datatableData.push(row);
  }
  console.log(datatableData);

  return datatableData;
}

// Your original function not really changed...
function createTable(data) {

  $('#table').DataTable({
    "processing": true,
    //"serverSide": true,     // This works with the "remote preocessing" ajax attribute.
    "bFilter": false,
    //"iDisplayLength": configData,   // I commented this one only because I did not have the "configData" variable.
    dom: 'Bfrtip',
    buttons: ['colvis', 'print', 'csv', 'excel', 'pdf'],
    searching: false,
    language: {
      buttons: {
       colvis: 'Show/Hide Columns'
      }
    },
    "aaData": arrangeData(data)   // I'm calling the "arrange" function here.
  });
}

// This is to simulate the response you get form jQuery Ajax.
var dataset = {
  msg:null,
  code:null,
  status:null,
  result:[
    {
      "aNumber":"3224193861",
      "bNumber":"3217284244",
      "cellID":"410-01-604-30226",
      "dateTime":"2017-06-24 23:08:09.0",
      "duration":801,
      "imei":"27512671195807",
      "imsi":"35788979525680",
      "operatorId":1,
      "mscId":"2",
      "fileId":"1"
    },
    {
      "aNumber":"3224193861",
      "bNumber":"3217280585",
      "cellID":"410-01-738-13433",
      "dateTime":"2017-06-24 06:53:13.0",
      "duration":198,
      "imei":"46341570864238",
      "imsi":"33270572168878",
      "operatorId":2,
      "mscId":"4",
      "fileId":"2"
    }
  ],
  "draw":1,
  "limit":1000,
  "recordsFiltered":13442,
  "recordsTotal":13442
};

// Call the table draw function... Certainly in your ajax success callback.
createTable(dataset.result);

CodePen