Datatables server-side automatic row id

Question

I'm trying to render a DataTable that automatically inserts and id to each row.

According to http://datatables.net/release-datatables/examples/server_side/ids.html and I understood ok, if I pass a DT_RowId "column" with each row, it should do it automatically.

I'm using the sample PHP they provide here: http://datatables.net/release-datatables/examples/data_sources/server_side.html

I made a MySQL View to have a dynamic table which turns the table index into a column named DT_RowId and added to the $aColumns variable so it outputs in the JSON.

The value is printed in the JSON but nothing happens. I'm 99% sure it's because the PHP code they provide doesn't echo the name of the column

In the example the JSON should be:

"aaData": [
    {
      "0": "Gecko",
      "1": "Firefox 1.0",
      "2": "Win 98+ / OSX.2+",
      "3": "1.7",
      "4": "A",
      "DT_RowId": "row_7",
      "DT_RowClass": "gradeA"
    }, { ... }

but the PHP outputs a keyless

"aaData": [
    {
      "Gecko",
      "Firefox 1.0",
      "Win 98+ / OSX.2+",
      "1.7",
      "A",
      "row_7",
      "gradeA"
    }, {...}

Seeing that I thought "how is the client-side javascript supposed to know the last column in the row is DT_RowId?". So I edited the PHP to output the JSON with the key data as in their example and I get:

{
    "sEcho": 1,
    "iTotalRecords": "4",
    "iTotalDisplayRecords": "4",
    "aaData": [
        {
            "Nombre": "Some Name",
            "username": "some username",
            "Email": "some email",
            "lastModified": "0000-00-00 00:00:00",
            "lastLogin": "2012-02-23 12:04:55",
            "rolname": "Some string",
            "DT_RowId": "2"
        }, {...},{...},{...} ] }

but I get an alert saying "DataTables warning: Requested unknown parameter '0' form the data source for row 0"

My JSON is formatted like theirs and I'm using

"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "scripts/id.php"

so I don't know why it's not a valid JSON for the DataTable to render. I may be missing something stupid but I'm stuck here.

Thanks for any help

Answer 1

I know this is old but I've cracked the nut on this without any crazy jS code or stuff on the server side script. You MUST name the columns after keying with the "columns" feature as such:

var meowTable = $('#meow').dataTable( {
        "bProcessing": true,
        "bServerSide": true, 
        "sAjaxSource": "get_meow_table.php",
        "columns": [ //needed for when you have keys with JSON
            { "data": "meow" },
            { "data": "woof" },
            { "data": "moo" },
            { "data": "cluck" }
        ]
});

Happy datatable'ing...

Answer 2

The aaData you refer to as "keyless" is invalid json. The {} should be array brackets []. I retracted my comment that data was formatted correctly except for the bracket issue

Answer 3

I got the same error for a null value stupidly assigned. This was mine wrong response:

{"aaData": [
   null,
   {
      "0": "Gecko",
      "1": "Firefox 1.0",
      "DT_RowId": "row_7",
      "DT_RowClass": "gradeA"
   }, { ..row2.. }
 ]}

printed by:

echo json_encode($out);

This is how i custruct the response:

function dittaInfoTable($db, $id) {
    $ret['aaData'][]=null; <-- wrong line to correct in $ret['aaData']=null;
    $res=$db->getA($id,$db->getTabDoc(),'id_ditta');
    $line=count($res);
    for($i=0;$i<$line;$i++){
        $row=array();
        $col=count($res[$i]);
        for ( $j=0 ; $j<$col ; $j++ ){
            if($j == $col - 1)
                $row['DT_RowId']= 'row_'.$res[$i][$j];
            else 
                $row[] = $res[$i][$j];
        }

        $ret['aaData'][]=$row;
    }

    return $ret;
}

And here how i get data from db:

private function queryNotAss($sql){
     $this->last_sql=$sql;
     $arr=array();

     $r = mysqli_query($this->link,$sql);
     for ($i=0; $i<mysqli_num_rows($r);$i++)
        $arr[$i]=mysqli_fetch_row($r);

     return $arr;
}

So yes, you have a bad json row elements on your json response.

Answer 4

here is my way:

/**
 * Output
 */
$output = array(
    "sEcho"                => intval($input['sEcho']),
    "iTotalRecords"        => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData"               => array(),
);

while ( $aRow = $rResult->fetch_assoc() ) {
    $row = array();
    $row['DT_RowId'] = $aRow['id'];
    for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
        if ( $aColumns[$i] == 'version' ) {
            // Special output formatting for 'version' column
            $row[] = ($aRow[ $aColumns[$i] ]=='0') ? '-' : $aRow[ $aColumns[$i] ];
        } elseif ( $aColumns[$i] != ' ' ) {
            // General output
            $row[] = $aRow[ $aColumns[$i] ];
        }
    }
    $output['aaData'][] = $row;
}

echo json_encode( $output );