How to format to the right JSON format [SOLVED]

How to format to the right JSON format [SOLVED]

RodriguezRodriguez Posts: 14Questions: 0Answers: 0
edited October 2012 in DataTables 1.9
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]

Replies

  • allanallan Posts: 63,394Questions: 1Answers: 10,451 Site admin
    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
  • RodriguezRodriguez Posts: 14Questions: 0Answers: 0
    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.
  • allanallan Posts: 63,394Questions: 1Answers: 10,451 Site admin
    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
  • RodriguezRodriguez Posts: 14Questions: 0Answers: 0
    edited October 2012
    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.
  • allanallan Posts: 63,394Questions: 1Answers: 10,451 Site admin
    Likely a JS error somewhere then. What tides firebug / inspector say?

    Allan
  • RodriguezRodriguez Posts: 14Questions: 0Answers: 0
    Ok, my chrome console tells me:

    [code]
    Uncaught TypeError: Cannot read property 'length' of undefined jquery.dataTables.js:2649
    [/code]
  • allanallan Posts: 63,394Questions: 1Answers: 10,451 Site admin
    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
  • RodriguezRodriguez Posts: 14Questions: 0Answers: 0
    Yes!! Thank you so much Allan. Really appreciate this. Works like a charm!
This discussion has been closed.