mysql - trouble plotting multiple series of data on highstock -
have personal temperature monitor project logs temperatures in separate rooms @ home mysql database , came across highstock/charts other day , have been playing it, cant seem work multiple series of data.
how data logged table;
datetime <-- (eg; 2013-07-18 15:52:26) time different each sensor each location
location <-- 6 of (lounge,kitchen,dinning,outside,master,spare)
temperature <-- (eg. 12.34)
what record looks in mysql; 2013-07-18 15:52:26 / master / 12.34
i have managed working set of data, i'm not sure need , how best way format json , mysql query highcharts can read json/mysq data , plot 6 locations temperature data on 1 graph.
complete code; main highstock chart file. http://pastebin.com/xwkthfc8 , file generates json mysql database. http://pastebin.com/rxbfr24p
this looks 1 set of data using 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 questions are;
what best way change plots data, @ moment doesn't seem work;
mysql_select_db("mqtt", $con); $return_arr = array(); $fetch = mysql_query("select timeof, message temperatures date(timeof) between date_sub(curdate(), interval 7 day) , curdate() , 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 multiple sets of data needs in format.
[{name:'kitchen',data:[[date,temp],[date,temp],[date,temp]]},{name:'lounge',data:[[date,temp],[date,temp],[date,temp]]}]
not sure how change query pulls data out locations , encodes correctly.
update-1
latest code,
$return_arr = array(); $fetch = mysql_query("select timeof, locationmap location, message temp temperatures date(timeof) between date_sub(curdate(), interval 1 day) , curdate() order 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 doesn't return correct format mentioned above.
it comes out this. (obviously there 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 query correct now!?? need json output!
update-2 thx highcharts forums, format need json in, need php/mysql side...
[{ name: 'kitchen', data: [ [time, value], [time, value] ] }, { name: 'attic', data: [ [time, value], [time, value] ] }] update-3 got working multiple series data, didn't relise had call json php script multiple times, have put 2 files on gist having similar problems.
https://gist.github.com/matbor/8854385 https://gist.github.com/matbor/8853902
i think best way follow "compare demo" , make php code generate 1 array, depending on url parameter specifies room :
json.php?room=attic json.php?room=cupboard an output data of room
then, it's highstocks calls separate php files :
names = ['attic', 'cupboard', '...'], $.each(names, function(i, name) { $.getjson('jsonp.php?room='+ name.tolowercase() +'-c.json&callback=?', function(data) { ... but honest i'm still looking way avoid multiple http calls/sql queries :-) data [date1,value1,value1,value3],[date2,value1,value1,value3]...
Comments
Post a Comment