Formatting PHP/MySQL data in correct JSON format for HighCharts

Question

I have been pulling my hair out for several days trying to get some data organized into the appropriate JSON format so that it can be plotted in a HighCharts graph.

My plan is to execute a jQuery function to call a PHP script which pulls a few columns of weather data from a MySQL database. I can re-ogranize data on the JavaScript side, but would prefer to have all of the data organization done in PHP and then have a single, correct JSON structure going into the HighCharts engine.

I am looking at weather data, so I have several columns of data sampled at every minute. The data columns I am retrieving from MySQL are: JSTimestamp (which is a unix timestamp * 1000 for JavaScript), avgOutdoorTemperature, avgIndoorTemperature, avgOutdoorHumidity, avgIndoorHumidity. I have populated each of these columns in their own array.

I can't seem to wrap my head around the PHP logic required to manipulate the data into the following JSON/array format:

[
  [
    [JSTimestamp1, avgOutdoorTemperature1],
    [JSTimestamp2, avgOutdoorTemperature2],
    [JSTimestamp3, avgOutdoorTemperature3],
    [JSTimestampN, avgOutdoorTemperatureN]
  ],
  [
    [JSTimestamp1, avgIndoorTemperature1],
    [JSTimestamp2, avgIndoorTemperature2],
    [JSTimestamp3, avgIndoorTemperature3],
    [JSTimestampN, avgIndoorTemperatureN]
  ],
  [
    [JSTimestamp1, avgOutdoorHumidity1],
    [JSTimestamp2, avgOutdoorHumidity2],
    [JSTimestamp3, avgOutdoorHumidity3],
    [JSTimestampN, avgOutdoorHumidityN]
  ],
  [
    [JSTimestamp1, avgIndoorHumidity1],
    [JSTimestamp2, avgIndoorHumidity2],
    [JSTimestamp3, avgIndoorHumidity3],
    [JSTimestampN, avgIndoorHumidityN]
  ]
]

I have populated the above structure with dummy data and it plots beautifully in HighCharts. Here's a sample demo showing what the graph will look like: http://highcharts.com/jsbin/afutef/11/edit#javascript,live

My current code is:

<?php

include_once('./include/mysqldatabase.php');
include_once('./include/mysqlresultset.php');

date_default_timezone_set('America/New_York');

$MYSQL_HOST = 'ip_address';
$MYSQL_USER = 'username';
$MYSQL_PASS = 'password';
$MYSQL_NAME = 'weatherdata';

$db = MySqlDatabase::getInstance();
try {
    $conn = $db->connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASS, $MYSQL_NAME);
}
catch (Exception $e) {
    die($e->getMessage());
}

$query = "
    select
        RecDate,
        unix_timestamp(RecDateTime)*1000 as JSTime,
        avgOutdoorTemperature,
        avgIndoorTemperature,
        avgOutdoorHumidity,
        avgIndoorHumidity
    from `rollup-minute-60`
    where 
        RecDate > 201301010000
    ";

$timeArray = array();
$avgOutdoorTemperatureArray = array();
$avgIndoorTemperatureArray = array();
$combinedArray = array();
$i = 0;

$seriesNames = array('Average Outdoor Temperature','Average Indoor Temperature', 'Average Outdoor Humidity', 'Average Indoor Humidity');

foreach ($db->iterate($query) as $r) {
    $jstime = $r->JSTime;
    $avgOutdoorTemperature = $r->avgOutdoorTemperature;
    $avgIndoorTemperature = $r->avgIndoorTemperature;
    $avgOutdoorHumidity = $r->avgOutdoorHumidity;
    $avgIndoorHumidity = $r->avgIndoorHumidity;

    $timeArray[$i] = (float)$jstime;
    $avgOutdoorTemperatureArray[$i] = (float)$avgOutdoorTemperature;
    $avgIndoorTemperatureArray[$i] = (float)$avgIndoorTemperature;
    $avgOutdoorHumidityArray[$i] = (float)$avgOutdoorHumidity;
    $avgIndoorHumidityArray[$i] = (float)$avgIndoorHumidity;    

    $combinedArray[$i] = array((float)$avgOutdoorTemperature, (float)$avgIndoorTemperature, (float)$avgOutdoorHumidity, (float)$avgIndoorHumidity);

    $i++;
}

echo "Combined array:<br>";
echo json_encode($combinedArray);
echo "<br><br><br>";

echo "Time array:<br>";
echo json_encode($timeArray);
echo "<br><br><br>";

$arraySize = sizeof($timeArray);
$seriesNameSize = sizeof($seriesNames);
$outputJSONArray = array();
$tempArray = array();

for ($i=0; $i<$seriesNameSize; $i++) {

    for ($j=0; $j<$arraySize; $j++) {
        $tempArray = array();

        array_push($tempArray, $timeArray[$j], $combinedArray[$j][$i]);
        echo "i=".$i.", j=".$j.", tempArray: " . json_encode($tempArray) ."<br>";

        $outputJSONArray[$i] = $tempArray;

     } 
}

echo json_encode($outputJSONArray);

?>

With the current code above, my output array is only showing the last element in $outputJSONArray instead of the multi-dimensional construct.

Can anyone help point me in the right direction? I have found a lot of good information on this site to help me get started but need a little assistance getting over the final hurdle.

Thank you!

This is just a hunch, but I think json_encode will only work properly when the keys are strings. Try: $outputJSONArray[(string)$i] = $tempArray; — mglowe
– mglowe, Commented Feb 18, 2013 at 5:50

Paweł Fus · Accepted Answer · 2013-02-18 11:46:59Z

1

Check Highcharts FAQ: http://docs.highcharts.com/#preprocessing-data-from-a-database Second example tells you how could this be done:

<?php
while ($row = mysql_fetch_array($result)) {
   extract $row;
   $datetime *= 1000; // convert from Unix timestamp to JavaScript time
   $data[] = "[$datetime, $value]";
}
?>
var chart = new Highcharts.Chart({
      chart: {
         renderTo: 'container'
      },
      series: [{
         data: [<?php echo join($data, ',') ?>]
      }]
});

answered Feb 18, 2013 at 11:46

Paweł Fus

45.1k3 gold badges65 silver badges79 bronze badges

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

Comments

William · Accepted Answer · 2013-02-19 01:47:48Z

Thank you for the response Paweł. I have been working with the example you posted and understand it well for a single time series. However my confusion comes to the proper formatting of multiple series.

I struggled in manipulating the data on the PHP side to get the data in the correct format:

[
  [
    [time1, y1],
    [time2, y2],
    [time3, y3]
  ]
  [
    [time1, z1],
    [time2, z2],
    [time3, z3]
  ]
  [
    ...
  ]
]

However, after playing with things earlier today and ultimately drawing out a solution on a whiteboard I found a solution that appears to work, and required me to traverse the collected data arrays once for each series. It may not be optimal but works well for the few thousand points I tested it with. Here is my code for reference:

$seriesNames = array('Average Outdoor Temperature','Average Indoor Temperature');

foreach ($db->iterate($query) as $r) {
    $time = (float)$r->RecDateTime;
    $jstime = (float)($r->Timestamp*1000);
    $avgOutdoorTemperature = (float)$r->avgOutdoorTemperature;
    $avgIndoorTemperature = (float)$r->avgIndoorTemperature;

    $dataArray[] = array(array($jstime, $avgOutdoorTemperature), array($jstime, $avgIndoorTemperature));
}

$arraySize = sizeof($dataArray);
$seriesNameSize = sizeof($seriesNames);

for ($i=0; $i<$seriesNameSize; $i++) {
    $tempArray = array();
        for ($j=0; $j<$arraySize; $j++) {
            $tempArray[] = $dataArray[$j][$i];  
        } 
    $outputJSONArray[] = $tempArray;
}

echo json_encode($outputJSONArray);

I suppose I could have performed multiple queries and added each series manually, but the query I ultimately intend to use is fairly intensive and would prefer to call it only once.

Collectives™ on Stack Overflow

Formatting PHP/MySQL data in correct JSON format for HighCharts

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related