DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

DataTable with JSON and MYSQL

DataTable with JSON and MYSQL

kimkakimka Posts: 5Questions: 0Answers: 0
edited June 2013 in General
Please help

My JSON request looks like:

[code]
<?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);
[/code]

The JSON result looks like:

[code]
[{"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"}]
[/code]

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

[code]
[
{
"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"
}

]
[/code]

My HTML, DataTables has most features enabled by default:

[code]
<!DOCTYPE html>




Overview Accounting

@import "data/portal.css";
@import "data/datatables/media/css/demo_table.css";




$(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)" }
]

});
})










EventDate
Norm.CPU.time kSI2K hours
Norm.CPU.time kHSPEC06 hours
Total CPU time used hours
Elapsed.time kSI2K hours
Elapsed.time kHSPEC06 hours
Total CPU elapsed time hours
Task





EventDate
Norm.CPU.time kSI2K hours
Norm.CPU.time kHSPEC06 hours
Total CPU time used hours
Elap.time kSI2K hours
Elap.time kHSPEC06 hours
Total CPU elapsed time hours
Task









[/code]

When I 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:
[quote]
DataTables warning (table id = 'example'): Requested unknown parameter '0' from the data source for row 0
[/quote]

I would be very grateful for the help

Replies

  • allanallan Posts: 63,516Questions: 1Answers: 10,472 Site admin
    Set sAjaxDataProp to an empty string (telling DataTables to expect a plain array) and then use mData for each column to tell it what data property to use for each column.

    Allan
  • kimkakimka Posts: 5Questions: 0Answers: 0
    I did so. Is that right?

    [code]
    $(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)" }
    ]

    });
    })
    [/code]
  • allanallan Posts: 63,516Questions: 1Answers: 10,472 Site admin
    Looks it to me. Does it not work? If not, can you give us a link?

    Allan
  • kimkakimka Posts: 5Questions: 0Answers: 0
    Yes of course, here's the link:
    http://goo.gl/mtxCv
  • kimkakimka Posts: 5Questions: 0Answers: 0
    I did the same but without JSON and it works. But I really want to make it right with Json.
    That's how I work without JSON:
    http://goo.gl/tcRHm
  • allanallan Posts: 63,516Questions: 1Answers: 10,472 Site admin
    You are using an old version of DataTables. mData was introduced in 1.9.4. In 1.9.0 which are are using it was called mDataProp. I'd suggest upgrading.
  • kimkakimka Posts: 5Questions: 0Answers: 0
    Thank you for your response. Everything work smartly. I touched on [quote] mDataProp [/quote] and all instantaneous work. Thank you once again. I will updated to the newest version.
This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.