How to format to the right JSON format [SOLVED]

Rodriguez

I'm trying to generate a JSON file that I can use with DataTables. After looking through these forums I guess my knowledge of javascript is too limited (honestly: non existant) in order to understand it. The thing is like this: I have a php file that generates a json file from a mysql query. It looks like this:

[code]
<?php

require "mysql.con.php";

$query = "SELECT spel_id, locatie, speler, sum(punten)
FROM `whiteboard_games`
group by spel_id, speler";

$result = mysql_query($query);

$rows = array();

while($r = mysql_fetch_assoc($result)){
$rows[] = array('data' => $r);
}

echo json_encode($rows);

?>
[/code]

this generates the following (valid) json (I simplified this to 4 rows, its actually +2000 rows and growing):

[code]
[
{
"data": {
"spel_id": "2012-09-24 15:43:56",
"locatie": "white room",
"speler": "Arne",
"sum(punten)": "17"
}
},
{
"data": {
"spel_id": "2012-09-24 15:43:56",
"locatie": "white room",
"speler": "Bjorg",
"sum(punten)": "26"
}
},
{
"data": {
"spel_id": "2012-09-24 15:43:56",
"locatie": "white room",
"speler": "Bram",
"sum(punten)": "-11"
}
},
{
"data": {
"spel_id": "2012-09-24 15:43:56",
"locatie": "white room",
"speler": "Filip",
"sum(punten)": "-32"
}
}
]
[/code]

From what I read in the forums and in the tutorials, the above json should look like the following, in order to work with DataTables:


[code]
{
"aaData": [
[
"2012-09-24 15:43:56",
"white room",
"Arne",
"17"
],
[
"2012-09-24 15:43:56",
"white room",
"Bjorg",
"26"
],
[
"2012-09-24 15:43:56",
"white room",
"Bram",
"-11"
],
[
"2012-09-24 15:43:56",
"white room",
"Filip",
"-32"
]
]
}

[/code]

now, how do I have to change my php in order to get the above format? I'm really new to programming, so my knowledge of php is pretty limited and absolutely zero for js.

I thank you very much for your answer in advance!

Edit: maybe I should also add the page where the DataTable is generated too:

[code]


Test DataTables









$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"sAjaxSource": "json.php",
"bDeferRender": true
} );
} );








kaarting
locatie
kaarter
punten








[/code]

allan

DataTables will work with just about any JSON format - see http://datatables.net/blog/Extended_data_source_options_with_DataTables . In the case here, I would suggest, you change:

> $rows[] = array('data' => $r);

to:

[code]
$rows[] = $r;
[/code]

and then simply use mData (as described in the blog) to read the data. If you don't want to change your PHP script you can use the ability of mData to work with Javascript dotted object notation - for example `mData: "data.locatie"` would work.

Allan

Rodriguez

Alright Allan, thank you for the fast response!

So, I changed my php script as you suggested. But I guess I'm doing something wrong when using the mData to work correctly. Right now my code with mData looks like this, but it doesn't work:


[code]
html xmlns="http://www.w3.org/1999/xhtml">

Test DataTables









$(document).ready(function() {
var oTable = $('#example').dataTable( {
"sAjaxSource": "json.php",
"aoColumns": [
{ "mData": "engine" },
{ "mData": "browser" },
{ "mData": "platform.inner" },
{ "mData": "platform.details.0" },
{ "mData": "platform.details.1" }
]
} );
} );








kaarting
locatie
kaarter
punten








[/code]

I'm probably making a huge noobish mistake here, but as stated above I'm totally not familiar with javascript.

allan

Assuming your JSON data looks like this now:

[code]
[
{
"spel_id": "2012-09-24 15:43:56",
"locatie": "white room",
"speler": "Filip",
"sum(punten)": "-32"
}
]
[/code]

Then you don't want to be using my example's values for mData - you want to use your own :-). `locatie` and `spel_id` for example.

Allan

Rodriguez

Oh noes... that was so bad! Should have seen this for myself... ::shame::

Almost there I suppose. Now I get "loading" message that stays like that.

My js looks like what follows and my json data looks like you discribed in your last post:


[code]

$(document).ready( function() {
var oTable = $('#example').dataTable( {
"sAjaxSource": "json.php",
"aoColumns": [
{ "mData": "spel_id" },
{ "mData": "locatie" },
{ "mData": "speler" },
{ "mData": "sum(punten)" }
]
} );
} );


[/code]

I tried running the debugger but I guess my corporate pc won't let me do that.

allan

Likely a JS error somewhere then. What tides firebug / inspector say?

Allan

Rodriguez

Ok, my chrome console tells me:

[code]
Uncaught TypeError: Cannot read property 'length' of undefined jquery.dataTables.js:2649
[/code]

allan

Ah - its because you are just returning an array, rather than the `aaData` parameter in an object. Simply set sAjaxDataProp to be an empty string and that should do it - i.e.:

[code]
sAjaxDataProp: ""
[/code]

in the DataTables initialisation.

Allan

Rodriguez

Yes!! Thank you so much Allan. Really appreciate this. Works like a charm!