DataTables mysql json

Question

Please help to create datatable (plug-in for the jQuery Javascript library) using JSON data source.

My JSON request looks like:

<?php
$con = mysql_connect("localhost","user","password");

if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("accounting", $con);

$sth = mysql_query("SELECT DATE_FORMAT(date,'%Y-%b') AS EventDate, sum(ksi2k), sum(hepspec), sum(cpu_used), sum(elapsed_ksi2k), sum(elapsed_hepspec), sum(elapsed_cpu_used), sum(jobs) FROM accounting where date like '2012%' and site like 'UA-KNU' group by year(date), month(date)");
$rows = array();

while($r = mysql_fetch_assoc($sth)){
    $rows[] = $r;

}

echo json_encode($rows);

mysql_close($con);

The JSON result looks like:

[{"EventDate":"2012","sum(ksi2k)":"54","sum(hepspec)":"216","sum(cpu_used)":"34","sum(elapsed_ksi2k)":"334","sum(elapsed_hepspec)":"1336","sum(elapsed_cpu_used)":"209","sum(jobs)":"6964"},{"EventDate":"2012","sum(ksi2k)":"71","sum(hepspec)":"284","sum(cpu_used)":"44","sum(elapsed_ksi2k)":"275","sum(elapsed_hepspec)":"1100","sum(elapsed_cpu_used)":"173","sum(jobs)":"5794"},{"EventDate":"2012","sum(ksi2k)":"29","sum(hepspec)":"116","sum(cpu_used)":"17","sum(elapsed_ksi2k)":"317","sum(elapsed_hepspec)":"1268","sum(elapsed_cpu_used)":"198","sum(jobs)":"4376"}]

I tested it with "The JSON Validator" and it is valid:

[
    {
        "EventDate": "2012-Jan",
        "sum(ksi2k)": "54",
        "sum(hepspec)": "216",
        "sum(cpu_used)": "34",
        "sum(elapsed_ksi2k)": "334",
        "sum(elapsed_hepspec)": "1336",
        "sum(elapsed_cpu_used)": "209",
        "sum(jobs)": "6964"
    },
    {
        "EventDate": "2012-Feb",
        "sum(ksi2k)": "71",
        "sum(hepspec)": "284",
        "sum(cpu_used)": "44",
        "sum(elapsed_ksi2k)": "275",
        "sum(elapsed_hepspec)": "1100",
        "sum(elapsed_cpu_used)": "173",
        "sum(jobs)": "5794"
    },
    {
        "EventDate": "2012-Mar",
        "sum(ksi2k)": "29",
        "sum(hepspec)": "116",
        "sum(cpu_used)": "17",
        "sum(elapsed_ksi2k)": "317",
        "sum(elapsed_hepspec)": "1268",
        "sum(elapsed_cpu_used)": "198",
        "sum(jobs)": "4376"
    }

]

My HTML, DataTables has most features enabled by default:

<!DOCTYPE html>
<html>
        <head>
            <meta http-equiv="content-type" content="text/html; charset=utf-8" />
            <link rel="shortcut icon" type="image/ico" href="" />
            <title>Overview Accounting</title>
            <style type="text/css" title="currentStyle">
            @import "data/portal.css";
            @import "data/datatables/media/css/demo_table.css";
            </style>
            <script type="text/javascript" language="javascript" src="data/datatables/media/js/jquery.js"></script>
            <script type="text/javascript" language="javascript" src="data/datatables/media/js/jquery.dataTables.js"></script>
            <script type="text/javascript" charset="utf-8">
                    $(document).ready(function(){
                        $('#example').dataTable({
                            "sAjaxSource": "json/json_table_overview_cluster_progress_chronicles_2013.php",
                            "sAjaxDataProp": "",
                            "aoColumns": [
                            { "mData": "EventDate" },
                            { "mData": "sum(ksi2k)" },
                            { "mData": "sum(hepspec)" },
                            { "mData": "sum(cpu_used)" },
                            { "mData": "sum(elapsed_ksi2k)" },
                            { "mData": "sum(elapsed_hepspec)" },
                            { "mData": "sum(elapsed_cpu_used)" },
                            { "mData": "sum(jobs)" }
                            ]

                        });
                    })
            </script>

        </head>
    <body id="dt_example" class="ex_highlight_row">
        <div id="container">
            <div id="dynamic">
                <table cellpadding="0" cellspacing="0" border="0" class="display" id="example">

                    <thead>
                    <tr>
                        <th width="5%">EventDate</th>
                        <th width="15%">Norm.CPU.time kSI2K hours</th>
                        <th width="17%">Norm.CPU.time kHSPEC06 hours</th>
                        <th width="14%">Total CPU time used hours</th>
                        <th width="15%">Elapsed.time kSI2K hours</th>
                        <th width="17%">Elapsed.time kHSPEC06 hours</th>
                        <th width="14%">Total CPU elapsed time hours</th>
                        <th width="3%">Task</th>
                    </tr>
                </thead>

                <tfoot>
                        <tr>
                            <th>EventDate</th>
                            <th>Norm.CPU.time kSI2K hours</th>
                            <th>Norm.CPU.time kHSPEC06 hours</th>
                            <th>Total CPU time used hours</th>
                            <th>Elap.time kSI2K hours</th>
                            <th>Elap.time kHSPEC06 hours</th>
                            <th>Total CPU elapsed time hours</th>
                            <th>Task</th>
                        </tr>
                </tfoot>

                </table>
            </div>
            <div class="spacer"></div>
        </div>
    </body>
</html>

When you refresh the page with the table, the browser gives an error, then you see the table with no data. The text of the error message:

DataTables warning (table id = 'example'): Requested unknown parameter '0' from the data source for row 0

I would be very grateful for the help

Jay Rizzi Jay Rizzi · Accepted Answer · 2013-06-17T19:55:46

Try putting your array inside of an aaData node, believe its needed

 /*
     * Output
     */
    $output = array(        
        "aaData" => array()
    );

    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }

    echo json_encode( $output );

DataTables mysql json

2 Answers