MySQL query result

mlino

Hello,

I'm new with Datatables, and I haven't figured ou how to show MySQL data in a datatable table. I'm trying to use the following code:

[code]









#myDiv{
width: 800px;
}


$(document).ready(function() {

$('#tb_data').dataTable( {
"bProcessing": true,
"bServerSide": true,
"bDeferRender": true,
"bLengthChange": false,
"bJQueryUI": true,
"bAutoWidth": false,
"sPaginationType": "full_numbers",
"sAjaxSource": "script/get_status.php",
"aoColumns": [
{ "mDataProp": "radar"},
{ "mDataProp": "status"},
{ "mDataProp": "aeronaves"},
{ "mDataProp": "ultima"},
{ "mDataProp": "estacao"}
]
});

jQuery.fn.center = function (){
this.css("position","fixed");
//this.css("top", ($(window).height() / 2) - (this.outerHeight() / 2));
this.css("left", ($(window).width() / 2) - (this.outerWidth() / 2));
return this;
}

$('#myDiv').center();
$(window).resize(function(){
$('#myDiv').center();
});



} );







Estacao
Status
Aeronaves
Atualizacao
Radar









[/code]

PHP code

[code]
<?php

include 'database_connection.php';

$query_names = mysqli_query($dblink, "SET NAMES 'utf8'");

$query_stat = mysqli_query($dblink, "select NodeID AS Radar, (CASE WHEN (Connected = 1) THEN 'Ativo' ELSE 'Inativo' END) AS Status, CurrentTracks AS Aeronaves, Max AS Ultima_Atualizacao, StationID AS Estacao FROM serverStat order by Status, Radar Asc");

$data = array();
if ( $query_stat && mysqli_num_rows($query_stat) )
{
while( $row = mysqli_fetch_array($query_stat, MYSQL_ASSOC) )
{
$data[] = array(
'radar' => $row['Radar'],
'status' => $row['Status'],
'aeronaves' => $row['Aeronaves'],
'ultima' => $row['Ultima_Atualizacao'],
'estacao' => $row['Estacao']
);
}
}

echo json_encode($data);
flush();

?>
[/code]

I'm getting the error bellow:



Any help will be appreciated. Thanks!

Marcio

allan

What does your script `script/get_status.php` return?

Allan

mlino

Hi Allan,

Bellow you will find the return of script/get_status.php:

[code]
[{"radar":"BR0002","status":"Ativo","aeronaves":"5","ultima":"2014-04-09 13:33:57","estacao":"Brasilia"},{"radar":"BR0006","status":"Ativo","aeronaves":"0","ultima":"2014-04-09 13:33:57","estacao":"Oliveira"},{"radar":"BR0008","status":"Ativo","aeronaves":"25","ultima":"2014-04-09 13:33:57","estacao":"Indaiatuba"},{"radar":"BR0011","status":"Ativo","aeronaves":"4","ultima":"2014-04-09 13:33:57","estacao":"Radar Porto Alegre (POA)"},{"radar":"BR0012","status":"Ativo","aeronaves":"2","ultima":"2014-04-09 13:33:57","estacao":"Radar Porto Alegre 2 (POA)"},{"radar":"BR0015","status":"Ativo","aeronaves":"11","ultima":"2014-04-09 13:33:57","estacao":"Radar Uberaba (SBUR)"},{"radar":"BR0019","status":"Ativo","aeronaves":"1","ultima":"2014-04-09 13:33:57","estacao":"Radar Bel\u00e9m (BEL)"},{"radar":"BR0020","status":"Ativo","aeronaves":"0","ultima":"2014-04-09 13:33:57","estacao":"Radar Macap\u00e1 (MCP)"},{"radar":"BR0021","status":"Ativo","aeronaves":"0","ultima":"2014-04-09 13:33:57","estacao":"Radar Cuiab\u00e1 (CGB)"},{"radar":"BR0022","status":"Ativo","aeronaves":"0","ultima":"2014-04-09 13:33:57","estacao":"Radar Palmas (PMW)"},{"radar":"BR0023","status":"Ativo","aeronaves":"2","ultima":"2014-04-09 13:33:57","estacao":"Radar Manaus (SBGL)"},{"radar":"BR0003","status":"Inativo","aeronaves":"0","ultima":"2013-12-07 17:53:42","estacao":"Guarulhos"},{"radar":"BR0007","status":"Inativo","aeronaves":"0","ultima":"2012-12-17 22:25:11","estacao":"Sao Paulo"},{"radar":"BR0009","status":"Inativo","aeronaves":"0","ultima":"2013-12-07 17:54:42","estacao":"Congonhas"},{"radar":"BR0014","status":"Inativo","aeronaves":"0","ultima":"2014-03-30 14:01:10","estacao":"Radar Nova Petropolis (RS)"},{"radar":"BR0016","status":"Inativo","aeronaves":"0","ultima":"2013-07-01 00:39:11","estacao":"Radar Congonhas (SBSP)"},{"radar":"BR0017","status":"Inativo","aeronaves":"0","ultima":"2014-03-12 22:22:59","estacao":"Radar Louveira (SP)"},{"radar":"BR0018","status":"Inativo","aeronaves":"0","ultima":"2014-04-09 06:17:57","estacao":"Radar Juazeiro (JUO)"}]
[/code]

allan

You can't return just an array of objects is you are using server-side processing. Extra information is required, as described in the documentation here: http://datatables.net/usage/server-side

Allan

mlino

Thank you Allan,

I have read the documentation but I couldn't realize how to implement aaData in my case. Do you have an exemple on how to do this?

Regards,

Marcio

allan

Are you sure you need server-side processing? Are you working with 20'000+ rows? If not, disable server-side processing and set sAjaxDataProp to an empty array. Then it will work.

Allan

mlino

Hi Allan,

No, I'm not. I'm working with no more than 100 rows. In fact my programming skills are very basic and because that I have choosed the server-side processing.

I read the the section about sAjaxDataProp and did the following changes to my initialization:

[code]
$('#tb_data').dataTable( {
"bProcessing": true,
"bServerSide": false,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "script/get_status.php",
"sAjaxDataProp": "radar",
"sAjaxDataProp": "status",
"sAjaxDataProp": "aeronaves",
"sAjaxDataProp": "ultima",
"sAjaxDataProp": "estacao"
});
[/code]

The result was the same. No data shown in the table.

Again, my json result is the following:

[code]
[{"radar":"BR0002","status":"Ativo","aeronaves":"7","ultima":"2014-04-09 16:44:56","estacao":"Brasilia"},
{"radar":"BR0006","status":"Ativo","aeronaves":"0","ultima":"2014-04-09 16:44:56","estacao":"Oliveira"}]
[/code]

Thank you!

allan

Try this:

[code]
$('#tb_data').dataTable( {
"bProcessing": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "script/get_status.php",
"sAjaxDataProp": "",
aoColumns: [
{ "mData": "radar" },
{ "mData": "status" },
{ "mData": "aeronaves" },
{ "mData": "ultima" },
{ "mData": "estacao" }
]
});
[/code]

Allan

mlino

Allan,

Thank you very much! Now my table is populated.

Regards,

Marcio