10

I am trying to get DataTables to read the column names from an AJAX data source but it seems that there must be something that I must be missing here.

I made a fiddle fiddle in which I can manually define the data and columns that are being used by the table.

The table is declared in the HTML and there is no no need to define the column names (<thead>..</thead>):

<table id="example" class="display table table-striped table-bordered" 
       cellspacing="0" width="100%"></table>

In the JS we manually define the data:

var data = [
    [ "Row 1 - Field 1", "Row 1 - Field 2", "Row 1 - Field 3" ],
    [ "Row 2 - Field 1", "Row 2 - Field 2", "Row 2 - Field 3" ],
];

Then manually define the column names or titles:

var columns = [
    { "title":"One" },
    { "title":"Two" }, 
    { "title":"Three" }
];

Then when we initialise the table we simply pass the previously declared information across for DataTables to use:

$(document).ready(function() {
  $('#example').DataTable( {
    dom: "Bfrtip",
    data: data,
    columns: columns
  });
});

Which results in:

Resulting Table

Now my question is how would I get this to work if the data is included in the AJAX server side response?

I have tried this in various ways and forms but nothing really seems to work out here and I am battling to find relative documentation on this.

For example if the server side processing sent back a JSON response which includes the column names at the end:

{
  "data": [
    {
      "id": "1",
      "One": "Row 1 - Field 1",
      "Two": "Row 1 - Field 2",
      "Three": "Row 1 - Field 3"
    },
    {
      "id": "2",
      "One": "Row 2 - Field 1",
      "Two": "Row 2 - Field 2",
      "Three": "Row 2 - Field 3"
    }
  ],
  "options": [],
  "files": [],
  "columns": [
    {
      "title": "One",
      "data": "One"
    },
    {

      "title": "Two",
      "data": "Two"
    },
    {
      "title": "Three",
      "data": "Three"
    }
  ]
}

Given this is the response, I tried to configure DataTables to use an AJAX data source for the row information as follows:

$(document).ready(function() {
  $('#example').DataTable( {
    dom: "Bfrtip",
    "ajax": '/test.php',
    columns: columns
  });
});

But obviously columns is undefined here.

So I get the column data before hand:

function getPromise() {
  var deferred = $.Deferred();
  var dataUrl = document.location.origin+'/text.php';
  $.getJSON(dataUrl, function(jsondata) {
    setTimeout(function() {
      deferred.resolve(jsondata);
    }, 0);
  }).fail(function( jqxhr, textStatus, error ) {
    // ********* FAILED
    var err = textStatus + ", " + error;
    console.log( "Request Failed: " + err );
  });
  return deferred.promise();
}
// Get the columns
getPromise().done(function(jsondata) {
  columns = jsondata.columns;
  console.log(columns);
});

And pass it to DataTables as above. But this time all I get when running the example is an error in the console saying TypeError: p is undefined.

So then how could I make use of the dynamically generated columns that are being returned within the server side response? Is there not a simpler way to achieve this?

EDIT:

DataTables Editor code for server side processing / to generate the JSON response mentioned above:

<?php
// DataTables PHP library
require_once '/path/to/DataTables.php';

// Alias Editor classes so they are easy to use
use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$out = Editor::inst( $db, 'example' )
  ->fields(
    Field::inst( 'id' )->set(false),
    Field::inst( '`One`' )->validator( 'Validate::notEmpty' ),
    Field::inst( '`Two`' )->validator( 'Validate::notEmpty' ),
    Field::inst( '`Three`' )->validator( 'Validate::notEmpty' )
  )
  ->process( $_POST )
  ->data();

// On 'read' remove the DT_RowId property so we can see fully how the `idSrc`
// option works on the client-side.
if ( Editor::action( $_POST ) === Editor::ACTION_READ ) {
    for ( $i=0, $ien=count($out['data']) ; $i<$ien ; $i++ ) {
        unset( $out['data'][$i]['DT_RowId'] );
    }
}

// Create the thead data
if (count ($out) > 0) {
  $columns = array();
  foreach ($out['data'][0] as $column=>$relativeValue) {
    // Add all but the id value
    if ($column !== 'id') {
      // Add this column name
      $columns[] = array(
        "title"=>$column,
        "data"=>$column
      );
    }
  }
}
// Add the the thead data to the ajax response
$out['columns'] = $columns;
// Send the data back to the client
echo json_encode( $out );
4
  • What is the response of text.php? Commented Mar 16, 2016 at 23:11
  • @CMedina Hi Again! The response is the only JSON included in the question. So it's just the normal server side response but at the end I included the column names within "columns". So this is what I am returning as the promise and passing to DT in columns: columns. Commented Mar 17, 2016 at 7:42
  • This is a fun problem, bear with while I work up a JSFiddle. Commented Mar 17, 2016 at 8:08
  • Check your php code, I test your function js with your json and thats work! Commented Mar 17, 2016 at 16:41

2 Answers 2

9

If you don't use the built in DataTables ajax it should be easy enough given the structure of your data:

$(document).ready(function() {
    $.ajax({
        type: 'POST',
        dataType: 'json',
        url: '/echo/json/',
        data: {
            json: JSON.stringify(jsonData)
        },
        success: function(d) {
            $('#example').DataTable({
                dom: "Bfrtip",
                data: d.data,
                columns: d.columns
            });
        }
    });
});

Like this JSFiddle, you're limited then to loading all the data at once but that shouldn't be a huge issue... unless you alter it get the columns from the initial ajax call and once the DataTable is initiated then add the built-in ajax - I've not tried this though...

Sign up to request clarification or add additional context in comments.

Comments

0

You must create the table in html without header

<table id="tablaListadoExpediciones" class="table table-bordered table-striped  dt-responsive">  </table>

Now you have to generate the columns array dynamically. In this way, the columns will always be generated automatically even if the AJAX response varies over time.

function PintarTablaDinamicamente(datos) {//object "datos" comes from AJAX response

            //I take the keys of the object as columns of the table
            var arrColumnas = Object.keys(datos[0])

            ////In case you wanted to hide a column
            //var indiceColumna = arrColumnas.indexOf("nameOfTheColumnToHide")
            //if (indiceColumna !== -1) {
            //    arrColumnas.splice(indiceColumna, 1)
            //}

            var columns = []
            //columns must be in JSON format-->{data:'keyToSearchInJSON',title:'columnTitle'}
            for (var i in arrColumnas) {

                //if (arrColumnas[i] == "SELECCIONAR") { //if you want to add a checkbox in one colunm (for example column named SELECT
                //    columns.push({
                //        data: null,
                //        defaultContent: '',
                //        className: 'select-checkbox',
                //        orderable: false
                //    });
                //} else {
                    columns.push({ data: arrColumnas[i], title: arrColumnas[i] });

                //}

            }


           
            tabla = $('#tablaListadoExpediciones').DataTable({
                dom: "Blfrtip",
                data: datos,
                //order: [[0, 'desc']],
                "pageLength": 25,
                "scrollX": true,
                columns: columns
                ,  

                buttons: [
                    {
                        extend: 'copy'
                       
                    },
                    {
                        extend: 'excel'
                       
                    },
                    {
                        extend: 'print'
                        
                    }
                ],

                //select: {
                //    style: 'os',
                //    selector: 'td:first-child'
                //}
            });
        }

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.