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