MySQL query result

MySQL query result

mlinomlino Posts: 5Questions: 0Answers: 0
edited April 2014 in DataTables 1.9
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

Replies

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin
    What does your script `script/get_status.php` return?

    Allan
  • mlinomlino Posts: 5Questions: 0Answers: 0
    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]
  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin
    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
  • mlinomlino Posts: 5Questions: 0Answers: 0
    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
  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin
    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
  • mlinomlino Posts: 5Questions: 0Answers: 0
    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!
  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin
    edited April 2014
    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
  • mlinomlino Posts: 5Questions: 0Answers: 0
    Allan,

    Thank you very much! Now my table is populated.

    Regards,

    Marcio
This discussion has been closed.