Added data does not match known number of columns

jcrawford

Hello Everyone,

I am trying to use datatables again after a long break of not using it. Currently I am using the AJAX method for populating the table data. However I keep getting the error that the data does not match the number of columns. Below is an example of my data output.

[code]
{
sEcho: 1
iTotalRecords: "74093453"
iTotalDisplayRecords: "74093453"
aaData: [
{
+ mp_id: "56743235"
+ name: "name 1"
+ description: "description 1"
+ rate: "0"
+ enabled: "0"
+ notes: null
+ locale: null
+ agency_name: null
},
{
+ mp_id: "32423345"
+ name: "name 2"
+ description: "description 2"
+ rate: "0"
+ enabled: "0"
+ notes: "duplicate"
+ locale: null
+ agency_name: null
}
]
}
[/code]

As you can see I have 8 data elements. In my datatable I have 8 columns, below is the table javascript code.

[code]
public function indexAction() {
// action body
$script = '
$(document).ready(
function() {
$("#MyTable").dataTable(
{
"aaSorting": [[ 1, "desc" ]],
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/path/to/data/api/",
"bAutoWidth": false,
"aoColumns": [
{ "bSearchable": false, "bSortable": false },
null,
null,
null,
null,
null,
null,
{ "bSearchable": false }
]
}
);
}
);
';
$this->view->headScript()->appendScript($script);
}
[/code]

Now below is the actual HTML that I am using in my Zend Framework view.

[code]



ID
LOCALE
NAME
DESCRIPTION
AGENCY
ENABLED
RATE
NOTES




Loading data from server




 



[/code]

Any assistance is appreciated.

jcrawford

I wonder could this have something to do with NULL values?

Thanks,
Joseph Crawford

Cassianno

Your JSON isn't valid.

Check www.jsonlint.com and paste it.


An example (valid):

[code]{
"sEcho": 1,
"iTotalRecords": 108,
"iTotalDisplayRecords": 108,
"aaData": [
[
"1",
"testing",
"testing",
"testing"
],
[
"2",
"testing",
"testing",
"testing"
]
]
}[/code]

Also, i would check those NULL values as crawford suggested...


edit: rofl, just now noticed crawford is the TS, my bad JCraw :P

jcrawford

hmm well this is odd... I am using php's json_encode for the output maybe that is why it is using the object { } rather than the array [ and ].

printf("%s\n", json_encode($this->DataTables_JSON_response));

I will try and force it to the proper format with a foreach loop thanks.

jcrawford

From my understanding in json an array is represented by [ and ] right? I thought objects were { and } but maybe I am wrong. I do not see why this will not work with json_encode.

This is what my result set looks like:

[code]
Array
(
[0] => Array
(
[mp_id] => 31676756
[name] => Name 1
[description] => Description 1
[rate] => 0
[enabled] => 0
[notes] =>
[locale] =>
[agency_name] =>
)

[1] => Array
(
[mp_id] => 2162453
[name] => Name 2
[description] => Description 2
[rate] => 0
[enabled] => 0
[notes] =>
[locale] =>
[agency_name] =>
)
)
[/code]

waca

Cassianno's example shows the correct JSON format for aaData, which can be confusing at first.

aaData is a two-dimensional array (hence the "aa" for array-array, I believe), where one dimension is the row and the other are individual data entries (no column names!) for that row. Just data.

You are trying to do associative arrays using the column name for each piece of data (e.g. "mp_id" => 2162453), which is why json_encode() is converting those items to JSON objects {} -- they are not plain ordinary arrays.

What you need is to get the aaData portion to be a pure 2-dim array -- an array of array's -- so that the data ends up looking like this in PHP:

[code]$aaData = array(
array( 31676756, "Name 1", "Description 1", 0, 0, null, null, null ),
array( 2162453, "Name 2", "Description 2", 0, 0, null, null, null)
);[/code]

Notice how there are no column names there? This $aaData should be converted properly using json_encode() such that it looks like Cassianno's example.

Here's an example to build a complete $retJSON in PHP using $aaData from above:

[code]$retArr= array(
'sEcho' => $sEcho,
'iTotalRecords' => $iTotalRecords,
'iTotalDisplayRecords' => $iTotalDisplayRecords,
'aaData' => $aaData
);

$retJSON = json_encode( $retArr );

//DEBUG: print_r( $retJSON );

echo $retJSON ;[/code]

Obviously this includes extra steps to allow you to uncomment that DEBUG line and see that it looks like Cassianno's example (with the [ [row0_data], [row1_data], [row2_data] ... ]'s around the aaData 2-dim array).

Note also that some cases you might want null values returned but in others you might want to return "" empty strings -- I haven't gotten into aaData deep enough to know the difference well.