几天来,我一直在努力将一些数据组织成适当的JSON格式,以便将其绘制在HighCharts图中。
我的计划是执行一个jQuery函数来调用一个PHP脚本,该脚本从MySQL数据库中提取几列天气数据。我可以在JavaScript端重新编排数据,但我更希望所有的数据组织都用PHP完成,然后在HighCharts引擎中输入一个正确的JSON结构。
我正在查看天气数据,所以我每分钟都会对几列数据进行采样。我从MySQL中检索的数据列是:JSTimestamp(这是JavaScript的unix时间戳*1000)、avgOutdoorTemperature、avgIndoorTemperature,avgOutDoorHumidy、avgIndiorHumidy。我已经将这些列中的每一列填充到它们自己的数组中。
我似乎无法理解将数据处理成以下JSON/数组格式所需的PHP逻辑:
[
[
[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]
]
]
我已经用伪数据填充了上面的结构,它在HighCharts中绘制得很漂亮。下面是一个示例演示,展示了图形的外观:http://highcharts.com/jsbin/afutef/11/edit#javascript,实时
我当前的代码是:
<?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);
?>
对于上面的当前代码,我的输出数组只显示$outputJSONArray中的最后一个元素,而不是多维构造。
有人能帮我指明正确的方向吗?我在这个网站上找到了很多好的信息来帮助我开始,但需要一些帮助来克服最后的障碍。
谢谢!
查看Highcharts常见问题解答:http://docs.highcharts.com/#preprocessing-数据库中的数据第二个例子告诉你如何做到这一点:
<?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, ',') ?>]
}]
});
感谢您的回复Paweł。我一直在使用你发布的例子,并很好地理解了一个时间序列。然而,我对多个系列的正确格式感到困惑。
我很难在PHP端操作数据,以获得正确格式的数据:
[
[
[time1, y1],
[time2, y2],
[time3, y3]
]
[
[time1, z1],
[time2, z2],
[time3, z3]
]
[
...
]
]
然而,在今天早些时候处理了一些事情并最终在白板上绘制了一个解决方案后,我发现了一个似乎有效的解决方案,并要求我为每个系列遍历一次收集的数据阵列。它可能不是最佳的,但对于我测试的几千点来说效果很好。这是我的代码供参考:
$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);
我想我本可以手动执行多个查询并添加每个系列,但我最终打算使用的查询相当密集,我更愿意只调用一次。