irvan sandoval irvan sandoval - 1 month ago 27
JSON Question

Generate JSON From Mysql Using PHP

i have little problem here, i want to generate some data to specific JSON format from Mysql using PHP, this is my PHP code

<?php


/*
Get data from the mysql database and return it in json format
*/


//setup global vars
$debug = $_GET['debug'];
$format = $_GET['format'];

if($format=='json'){
header("Content-type: text/json");
}


$db = new mysqli('localhost', root, 'kudanil123', 'PT100', 3306);

if (mysqli_connect_error()) {
die('Connect Error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}

if ($debug == 1) {echo 'Success... ' . $db->host_info . "\n";}

// get data
$sql = "select meas_date,ai0_hist_value";
$sql .= " from ai0_hist";
$sql .= " where board_temp_hist_value > 30"; //filter out bad data
$sql .= " group by 1";
$sql .= " order by meas_date desc"; //highcarts requires you order dates in asc order
$sql .= " limit 5;";


if ($result = $db->query($sql)) {

if ($debug == 1) {echo "fetched data! <br/><br/>";}


while($row = $result->fetch_array()){
$rows[] = $row;
}

foreach($rows as $row){

$text[] = (float)$row['ai0_hist_value'];
$date[] = strtotime($row['meas_date'])*1000;

}
}
//$data[0] = $names;
$data1 = $date;
$data = $text;
$data2 = array($data1, $data);
//$data[2] = $text;
echo (json_encode($data2));
// echo(json_encode($names));
$result->close();

} else {
echo "Error: " . $sql . "<br>" . $db->error;
}

$db->close();

?>


With this code, the result was

[
[1478616679000, 1478616677000, 1478616675000, 1478616673000, 1478616671000],
[28.4126, 28.5361, 28.4126, 28.4126, 28.2891]
]


Yes, that is valid JSON but, i want to use this JSON for chart in highcharts.com, so i need the JSON format like this

[
[1257811200000, 29.00],
[1257897600000, 29.04],
[1257984000000, 28.86],
[1258070400000, 29.21],
[1258329600000, 29.52],
[1258416000000, 29.57],
[1258502400000, 29.42],
[1258588800000, 28.64],
[1258675200000, 28.56],
[1258934400000, 29.41],
[1259020800000, 29.21],
[1259107200000, 29.17],
[1259280000000, 28.66],
[1259539200000, 28.56]
]


Gladly if someone can help me, i'm stuck for a days try to solving this issue

Answer

Could construct the formatted series data to begin with like below:

<?php


/*
    Get data from the mysql database and return it in json format
*/


//setup global vars
$debug = $_GET['debug'];
$format = $_GET['format'];

if($format=='json'){
    header("Content-type: text/json");
}


$db = new mysqli('localhost', root, 'kudanil123', 'PT100', 3306);

if (mysqli_connect_error()) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

    if ($debug == 1) {echo 'Success... ' . $db->host_info . "\n";}

// get data
$sql = "select meas_date,ai0_hist_value";
$sql .= " from ai0_hist";
$sql .= " where board_temp_hist_value > 30"; //filter out bad data
$sql .= " group by 1";
$sql .= " order by meas_date desc"; //highcarts requires you order dates in asc order
$sql .= " limit 5;";


if ($result = $db->query($sql)) {

    if ($debug == 1) {echo "fetched data! <br/><br/>";}


    while($row = $result->fetch_array()){
        $rows[] = $row;
    }

    foreach($rows as $row){
        $seriesData[] = [ strtotime($row['meas_date'])*1000, (float)$row['ai0_hist_value'] ];

    }


       echo (json_encode($seriesData));

       $result->close();

} else {
    echo "Error: " . $sql . "<br>" . $db->error;
}

$db->close();