How to format to the right JSON format [SOLVED]
How to format to the right JSON format [SOLVED]
Rodriguez
Posts: 14Questions: 0Answers: 0
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]
[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]
This discussion has been closed.
Replies
> $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
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.
[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
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
[code]
Uncaught TypeError: Cannot read property 'length' of undefined jquery.dataTables.js:2649
[/code]
[code]
sAjaxDataProp: ""
[/code]
in the DataTables initialisation.
Allan