Generate JSON From Mysql Using PHP

Question

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

megavexus · Accepted Answer · 2016-11-08 17:26:51Z

2

If you want the code like that, you must fix the code:

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));

must be something like this:

while($row = $result->fetch_array()){
    $rows[] = array(
           (float)$row['ai0_hist_value'],
           strtotime($row['meas_date'])*1000);
}

echo (json_encode($rows));

You were saving in $data2 an array with two arrays, the text and the data. You must save a row for each pair of 'text' and 'data'.

answered Nov 8, 2016 at 17:26

megavexus

1314 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

apokryfos · Accepted Answer · 2016-11-08 17:22:17Z

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();

RiggsFolly · Accepted Answer · 2016-11-08 17:21:01Z

This will generate the array you want, there is no need to do all that fiddling with the data from the database

// 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;";


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

    while($row = $result->fetch_array()){

        $rows[] = array(strtotime($row['meas_date'])*1000, 
                        $row['ai0_hist_value']
                        );
    }
}
echo json_encode($rows);

Now you will need to convert the text to float in the javascript. This is because JSON is passed as text and not any other data type, so it has to be converted, if necessary in the receiving javascript.

Collectives™ on Stack Overflow

Generate JSON From Mysql Using PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related