难以在highstock上绘制多个系列的数据 [英] trouble plotting multiple series of data on highstock
问题描述
有一个个人温度监控器项目，它将家里各个房间的所有温度记录到mysql数据库中，前几天我遇到了高库存/图表，并且一直在使用它，但是我似乎无法使其与多个环境一起使用系列数据.
如何将数据记录到表中
datetime<-(例如2013-07-18 15:52:26)每个位置的每个传感器的时间将不同
位置<-(休息室，厨房，餐厅，外面，主人，备用)中的6个
温度<-(例如12.34)
mysql中的记录是什么样子； 2013-07-18 15:52:26/大师/12.34
我已经设法使所有数据都能正常工作，但是我不确定我需要做什么以及最好的方式是如何格式化json和mysql查询，以使highcharts可以读取json/mysq数据并在一张图表上绘制所有6个位置的温度数据.
完整代码；主要的highstock图表文件. http://pastebin.com/XWkThfc8 ，这是从mysql数据库生成JSON的文件. http://pastebin.com/RXBFr24P
这是使用上述查询获得的一组数据的当前外观....[1374593356000,17.31]，[1374593427000,17.25]，[1374593497000,17.31]，[1374593567000,17.31]，[1374593638000， 17.31]，[1374593708000,17.25]，[1374593778000,17.25]，[1374593849000,17.25]，[1374593919000,17.25]，[1374593989000,17.25]，[1374594060000,17.25]，[1374594130000,17.25] .... etc
我的问题是
更改此设置的最佳方法是什么，以便它可以绘制所有数据，但现在看来似乎不起作用；
mysql_select_db("mqtt", $con);
$return_arr = array();
$fetch = mysql_query("SELECT timeof, message FROM temperatures WHERE DATE(timeof) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() AND locationmap = 'master'");
$i=0;
while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
$rows[$i]=array(strtotime($row[timeof])*1000 ,(float)$row[message]);
$i++;
}
echo json_encode($rows);
我假设对于多组数据，它必须采用这种格式.
[{name:'kitchen'，data:[[date，temp]，[date，temp]，[date，temp]]}}，{name:'lounge'，data:[[date ，temp]，[date，temp]，[date，temp]]}]
不确定如何更改查询，以便将其提取到所有位置，然后正确编码.
UPDATE-1
最新代码
$return_arr = array();
$fetch = mysql_query("SELECT timeof, locationmap AS location, message AS temp FROM temperatures WHERE DATE(timeof) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE() ORDER BY location, timeof ASC");
$i=0;
while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
$rows[$i]=array(strtotime($row[timeof])*1000 ,(float)$row[temp],$row[location]);
$i++;
}
echo json_encode($rows);
但是，它没有返回如上所述的正确格式.
它像这样出来. (显然一共有1000个.)
[1375272426000,22.63,"cupboard"],[1375272496000,22.69,"cupboard"],[1375272566000,22.75,"cupboard"],[1375272637000,22.75,"cupboard"],[1375272707000,22.69,"cupboard"],[1375272777000,22.63,"cupboard"],[1375106429000,17.69,"kitchen"],[1375106500000,17.69,"kitchen"],[1375106570000,17.63,"kitchen"],[1375106640000,17.63,"kitchen"],[1375106711000,17.63,"kitchen"],[1375106781000,17.63,"kitchen"],[1375106851000,17.63,"kitchen"],[1375106921000,17.56,"kitchen"],[1375106992000,17.56,"kitchen"],[1375107062000,17.56,"kitchen"],[1375107132000,17.56,"kitchen"],[1375107203000,17.56,"kitchen"],[1375107273000,17.5,"kitchen"],[1375107343000,17.5,"kitchen"],[1375107413000,17.5,"kitchen"]
我认为我的查询现在是正确的！?只需要一些有关json输出的帮助即可！
UPDATE-2 到highcharts论坛，这是我需要JSON的格式，只需要php/mysql方面的帮助...
[{
name: 'kitchen',
data: [
[time, value],
[time, value]
]
}, {
name: 'attic',
data: [
[time, value],
[time, value]
]
}]
UPDATE-3 它可以处理多个系列数据，没关系，我不得不多次调用我的json php脚本，已将这两个文件放到GIST上供有类似问题的人使用.
https://gist.github.com/matbor/8854385 https://gist.github.com/matbor/8853902
我认为最好的方法是遵循比较演示"，并使您的php代码仅生成一个数组，具体取决于指定房间的url参数:json.php?room=attic
json.php?room=cupboard
您只输出会议室的数据
然后，highstock用以下命令调用单独的php文件:
names = ['attic', 'cupboard', '...'],
$.each(names, function(i, name) {
$.getJSON('jsonp.php?room='+ name.toLowerCase() +'-c.json&callback=?', function(data) {
...
但是，老实说，我仍在寻找一种避免多次http调用/sql查询的方法:-)，尤其是对于[date1，value1，value1，value3]，[date2，value1，value1，value3]之类的数据. ..
Have a personal temperature monitor project that logs all temperatures in separate rooms at home into a mysql database and I came across highstock/charts the other day and have been playing with it, but I cant seem to get it to work with multiple series of data.
how the data is logged into the table;
datetime <-- (eg; 2013-07-18 15:52:26) time will be different for each sensor for each location
location <-- 6 of (lounge,kitchen,dinning,outside,master,spare)
temperature <-- (eg. 12.34)
What a record looks like in mysql; 2013-07-18 15:52:26 / master / 12.34
I have managed to get it all working for set of data, but I'm not sure what I need to do and how the best way is to format the json and mysql query so that highcharts can read the json/mysq data and plot ALL 6 locations temperature data on the one graph.
Complete code; The main highstock chart file. http://pastebin.com/XWkThfc8 and this is the file that generates the JSON from the mysql database. http://pastebin.com/RXBFr24P
This is what it currently looks like for one set of data using the above queries.... [1374593356000,17.31],[1374593427000,17.25],[1374593497000,17.31],[1374593567000,17.31],[1374593638000,17.31],[1374593708000,17.25],[1374593778000,17.25],[1374593849000,17.25],[1374593919000,17.25],[1374593989000,17.25],[1374594060000,17.25],[1374594130000,17.25]....etc
So my questions are;
What is the best way to change this so it plots all the data, at the moment it doesn't seem to work;
mysql_select_db("mqtt", $con);
$return_arr = array();
$fetch = mysql_query("SELECT timeof, message FROM temperatures WHERE DATE(timeof) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() AND locationmap = 'master'");
$i=0;
while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
$rows[$i]=array(strtotime($row[timeof])*1000 ,(float)$row[message]);
$i++;
}
echo json_encode($rows);
I assume for multiple sets of data that it needs to be in this format.
[{name:'kitchen',data:[[date,temp],[date,temp],[date,temp]]},{name:'lounge',data:[[date,temp],[date,temp],[date,temp]]}]
Not sure how to change my query so that it pulls the data out for ALL locations and then encodes it correctly.
UPDATE-1
Latest code,
$return_arr = array();
$fetch = mysql_query("SELECT timeof, locationmap AS location, message AS temp FROM temperatures WHERE DATE(timeof) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE() ORDER BY location, timeof ASC");
$i=0;
while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
$rows[$i]=array(strtotime($row[timeof])*1000 ,(float)$row[temp],$row[location]);
$i++;
}
echo json_encode($rows);
However it doesn't return the correct format as mentioned above.
It comes out like this. (Obviously there is 1000's of entires.)
[1375272426000,22.63,"cupboard"],[1375272496000,22.69,"cupboard"],[1375272566000,22.75,"cupboard"],[1375272637000,22.75,"cupboard"],[1375272707000,22.69,"cupboard"],[1375272777000,22.63,"cupboard"],[1375106429000,17.69,"kitchen"],[1375106500000,17.69,"kitchen"],[1375106570000,17.63,"kitchen"],[1375106640000,17.63,"kitchen"],[1375106711000,17.63,"kitchen"],[1375106781000,17.63,"kitchen"],[1375106851000,17.63,"kitchen"],[1375106921000,17.56,"kitchen"],[1375106992000,17.56,"kitchen"],[1375107062000,17.56,"kitchen"],[1375107132000,17.56,"kitchen"],[1375107203000,17.56,"kitchen"],[1375107273000,17.5,"kitchen"],[1375107343000,17.5,"kitchen"],[1375107413000,17.5,"kitchen"]
I think my query is correct now!?? just need some help with the json output!
UPDATE-2 Thx to the highcharts forums, this is the format I need the JSON in, just need some help with the php/mysql side...
[{
name: 'kitchen',
data: [
[time, value],
[time, value]
]
}, {
name: 'attic',
data: [
[time, value],
[time, value]
]
}]
UPDATE-3 Got it working with multiple series data, didn't relise i had to call my json php script multiple times, have put the two files on GIST for anyone having similar problems.
https://gist.github.com/matbor/8854385 https://gist.github.com/matbor/8853902
I think the best way is to follow the "compare demo" and make your php code generate only one array, depending on an url parameter which specifies the room :
json.php?room=attic
json.php?room=cupboard
An you only output the data of the room
Then, it's highstocks that calls the separate php files with :
names = ['attic', 'cupboard', '...'],
$.each(names, function(i, name) {
$.getJSON('jsonp.php?room='+ name.toLowerCase() +'-c.json&callback=?', function(data) {
...
But to be honest I'm still looking for a way to avoid multiple http calls/sql queries :-) especially for data like [date1,value1,value1,value3],[date2,value1,value1,value3]...
这篇关于难以在highstock上绘制多个系列的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
