PHP Array to .json to Datatable

johnyb0y

I have a question regarding using a JSON file as a Datasource
I'm using Datatables for the first time and I've also never used JSON before - please bear with me in case of obvious errors
I'm parsing a folder of files with some additional information in an PHP array.
This array gets converted into a .json file. I'm trying to display a DataTable using the .json file as source.

HTML

<table id="example" class="display table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th>FileName</th>
<th>Format</th>
<th>Size</th>
<th>InProgress</th>
<th>Done</th>
</tr>
</thead>
<tbody>
</tbody>
</table>

My generated .JSON File

{
   "0":{
      "FileName":"5c0d0b8f93638_flower.jpg",
      "Extension":"jpg",
      "FileSize":0.3,
      "InProgress":0,
      "Done":0
   },
   "1":{
      "FileName":"5c096d022abdf_flower2.jpg",
      "Extension":"jpg",
      "FileSize":0.3,
      "InProgress":0,
      "Done":0
   },
   "2":{
      "FileName":"5c07c196b4a53_Test.zip",
      "Extension":"zip",
      "FileSize":0.02,
      "InProgress":0,
      "Done":0
   },
   "3":{
      "FileName":"5c07b69d1b976_Test2.zip",
      "Extension":"zip",
      "FileSize":0.12,
      "InProgress":0,
      "Done":0
   }
}

My DataTables Config

    ajax: {
        url: '../backend/db/db.json',
        dataSrc : '',
        columns: [
                    { data: 'FileName' },
                    { data: 'Extension' },
                    { data: 'FileSize' },
                    { data: 'InProgress' },
                    { data: 'Done' }
                       ]
    },

My File-Parser

<?php
foreach (new DirectoryIterator('../backend/files') as $file) {
    if($file->isDot()) continue;

    $fileinfos[] = [
        "FileName"=>$file->getFilename(),
        "Extension"=>$file->getExtension(),
        "FileSize"=>round ($file->getSize() / 1048576, 2),
        "InProgress"=>0,
        "Done"=>0
    ];    
}
?>

<script type="text/javascript">
var json_encode = <?php echo json_encode($fileinfos, JSON_FORCE_OBJECT) ?>;
var json_stringify = JSON.stringify( json_encode );
        $.ajax({
        type: "POST",
        url: "process.php",
        data: json_stringify,
        dataType: 'json',
        cache: false,
        })
</script>

DataTables just shows "No data available in table". I confirmed that the .JSON file is found, so it's not a "missing file" error.
I guess the reason is the formatting of my .JSON or my DataTables config (or both...), but so far I couldn't figure it out.

Any help would be much appreciated. Thank you!

colin

Hi @johnyb0y ,

The problem is because you have an object of objects (see this SO thread here. What you need to do is to convert that to an array of objects, see example here.

Cheers,

Colin

johnyb0y

Thanks @colin ! That helps a lot

kthorngren

The problem is you have your columns config inside the ajax opton. Move it outside, like this:

ajax: {
    url: '../backend/db/db.json',
     
    dataSrc : 'data',
},
     
columns: [
                { data: 'FileName' },
                { data: 'Extension' },
                { data: 'FileSize' },
                { data: 'InProgress' },
                { data: 'Done' }
 ],

You can also remove the -ajax dataSrc option since that is the default.

Kevin

johnyb0y

@kthorngren Thank you! It's working now!

johnyb0y

It seems like I deleted the comment that kthrongren replied to by mistake.

For future reference. Maybe helpful to someone:

I fixed the problem of the wrong .JSON layout by altering my File Parser Code:

<?php
foreach (new DirectoryIterator('../backend/files') as $file) {
    if($file->isDot()) continue;

    $fileinfos[] = [
        "FileName"=>$file->getFilename(),
        "Extension"=>$file->getExtension(),
        "FileSize"=>round ($file->getSize() / 1048576, 2),
        "InProgress"=>0,
        "Done"=>0
    ];
    
}

$return_array = array('data'=> $fileinfos);
?>

<script type="text/javascript">
var json_encode = <?php echo json_encode($return_array) ?>;
var json_stringify = JSON.stringify( json_encode );


        $.ajax({
        type: "POST",
        url: "process.php",
        data: json_stringify,
        dataType: 'json',
        cache: false,
        })

</script>

Now i get a .JSON with a valid array of objects.

With this Datatable Config everything is now working as expected:

ajax: {
        url: '../backend/db/db.json',    
    },

        columns: [
                    { "data": "FileName" },
                    { "data": "Extension" },
                    { "data": "FileSize" },
                    { "data": "InProgress" },
                    { "data": "Done" }
                    ],               

Again, Thanks @colin and @kthorngren for your help!