"columns" property problem

culter

Hi, I'm struggling with columns property. I have server-side processing feature turned on and everything works perfectly, but when I define "columns" : [{"data": "ID" }, ...] I get this error:

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

and after I click ok, I get the table with blank rows.

JSON is:

{"draw":1,"recordsTotal":36127,"recordsFiltered":36127,"data":[["464637","5517"],["529457","7710"],["10464377","1468"],["10464397","7728"],["10464398","466"],["10464399","1377"],["10464400","1297"],["10464401","9865"],["10464417","747"],["10464418","7435"]]}

My column count in "columns" is the same as defined in html table. The names are the same as in html table. I tried to figure it out. According to your manual, when parameter is an string, the data property doesn't exist or value of property is null. I don't think this is the case of problem, because without defining "columns" everything works fine.

I need to define columns, because I want to display row details as it is described here :
https://datatables.net/blog/2017-03-31

Thank you

kthorngren

If you want to use columns.data then your returned JSON needs to be an array of objects not an array of arrays. More info here:
https://datatables.net/manual/data/#Data-source-types

You can use arrays with child detail rows. You just need to access the array elements by index. If you are using that ajax example then instead of this:

    $.ajax( {
        url: '/api/staff/details',
        data: {
            name: rowData.name
        },

You would do something like this:

    $.ajax( {
        url: '/api/staff/details',
        data: {
            name: rowData[0]
        },

Kevin

culter

Perfect. This worked for me:

"ajax": {
    url: "script/server_processing.php",
    data: "{ name: data[0] }",
    data: "{ name: data[1] }",
    ...
}

Thank you, Kevin.

culter

My bad, it's not working. It doesn't matter if I enter data[0] or data[5], it always display the same table

kthorngren

data: "{ name: data[0] }"

This is a parameter you are passing to the script/server_processing.php script. Does that script look for the name parameter?

In your format function use console.log(data); to see what data is.

It doesn't matter if I enter data[0] or data[5], it always display the same table

Without an example or more info of what you are doing its hard to help.

Kevin

culter

Ok, here is my html table:

    <table id="tickets" class="table table-striped table-bordered" style="width:100%">
            <thead>
                <tr>
                    <th>NO</th>
                    <th>ID</th>
                </tr>
            </thead>
          </table>

My dataTables script:

$(document).ready(function() {
   var table = $('#tickets').DataTable( {
        "processing": true,
        "serverSide": true,
        columns: [
                { data: 'data[0]' }
        ],
//      data: "data",
        "ajax": {
                "url": "scripts/server_processing.php",
//              "columns": [
//                      {data: "data[1]"},
//                      {data: "data[1]"}
//              ],
//              "data": {
//                      "NO": "data[0]"
//              },
//              "columns": [
//                      { "NO": data[2] }
//              ]
//                      { "NO": "data[0]" },
//                      { "ID": "data[1]" }
//                      { "ID": "data[1]" }
//              "data": "{ NO: data[1] }",
//              data: "{ name: data[2] }",
//              data: "{ name: data[6] }",
//              data: "{ name: data[4] }",
//              data: "{ name: data[5] }"
                },
        "lengthMenu": [[10,25,50,100],[10,20,50,100]],
} );

In the commented lines is what I tried already.
I don't know if it's good idea, but is it possible to get from the server-side script just array of objects instead of array of arrays?

kthorngren

I'm not familiar with the PHP scripts but there is an 'objects.php` SSP processing script that uses object based data.

I'm confused by the above code. Is this the code for your main table or what you are trying to use for the child details?

Kevin

culter

Ok, the main goal is to get data from mysql and display the table. Then, after click on a table row, additional data from the same table and other 2 tables will be displayed in a roll-out window.

I have the working table and also the clicking on a row creates roll-out window (without data).

I have server-side php script and SSP script from here https://datatables.net/examples/server_side/object_data.html .

So far I discovered several potential problems.
1. I need to grab data from several tables, but the sample php server-side script is made just for one table. I found some advice by Allan which partially solves this problem with loading more columns than the displayed columns in the main table
2. To display the row details, I tried to implement this: https://datatables.net/blog/2017-03-31 . I just tried to avoid the first column with plus sign and make clickable the entire row, which is working ok, but I don't know how to send the data to row details window.

Is it possible to modify this part of server-side script to return array of objects instead of array of arrays and then use the standard columns:[{data: 'ID'},..] ?

$columns = array(
    array(
        'db' => 'id',
        'dt' => 'DT_RowId',

Thank you for your patience.

allan

1) I need to grab data from several tables

Three options here:

1. Modify the SSP script to allow the use of joins
2. Create a VIEW which will do the joins and allow the demo SSP script to query just that view (this is probably the way I'd do it myself in this case).
3. Use the Editor PHP libraries to read the data which allow for joins.

2) To display the row details, [...] but I don't know how to send the data to row details window.

In that blog post the data for the row is sent to the format function. You could either process it there to pass on to another window, or use window.open with the data in query string parameters. Another option would be to just pass the row id and then have the new window query the server to get the row data for that id.

Is it possible to modify this part of server-side script to return array of objects instead of array

This example shows server-side processing with object based row data.

Allan

culter

Thank you, Allan. The idea of creating view is great. I'll try to do it this way.

To display columns, when you have array of arrays, I found this part of documentation and it looks that its working, so

$('#myTable').DataTable( {
    ajax: ...,
    columns: [
        { data: 0 },
        { data: 1 },
        { data: 2 },
        { data: 3 },
        { data: 4 },
        { data: 5 }
    ]
} );

Thank you.

culter

Allan, just one more question. When I want to implement this isn't more simple solution to create second serverside script and use it in the Ajax request?

You're using this json in basic datatables script:

ajax: '/api/staff',

and this in the ajax call

url: '/api/staff/details',

Is it possible to use

    "ajax": {
        url: 'scripts/server_processing.php'
    }

in the datatables script and

    $.ajax( {
        url: 'scripts/server_processing_2.php'
    });

in the ajax call?

Thank you very much.