I'm new here and not really experienced with highcharts / javascripts and variable data from SQL.
The Situation is that I've got three Pi3 with DHT22 Sensor, the Scripts on the Pis working well, the data is stored to my NAS into a Maria5 DB.
I've managed to display the data (date/time, temperature, humidity) in a table for each sensor: Data shown in a table
I've also managed to display the date/time and temperature (OR humidity) of all three Pi3Sensors (data) in separate highcharts on one page: three charts with temperature
What I want to do now is to show the temperature AND humidity in each highchart of the single Pi's charts. When I enter the 2nd yAxis Data (Series 2) for the humidity manually, then it's working: 2nd data series entered manually
There must be something wrong with the variables of temperature and humidity data. I searched a lot online but wasn't able to find something that helps, or I'm just not able to understand it the right way...
In the DB are the fields 'dateandtime' (2018-04-18 15:05:00), 'unix_timestamp' (1524056702), 'sensor' (value "1", "2" or "3" for each sensor), 'temperature' and 'humidity.
The html page should be alright (index.html);
<html>
<head>
<title>Temperatur Uebersicht</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js" type="text/javascript"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
<script type="text/javascript" src="data.js" ></script>
</head>
<body>
<div id="chart1" style="height: 400px; margin: 0 auto"></div>
</body>
</html>
The php file (values1.php) with the SQL connection / statement;
<?php
$con = mysql_connect("localhost","user","password");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("piSensors", $con);
$result = mysql_query("SELECT *
FROM `temperaturedata`
WHERE sensor=1
ORDER BY unix_timestamp DESC
LIMIT 288 ")
or die ("Connection error");
while($row = mysql_fetch_array($result)) {
echo $row['unix_timestamp'] . "/" . $row['temperature'] . "/" . $row['humidity'] . "/" ;
}
mysql_close($con);
?>
...and the javascript (data.js) with the two series. The data of the 2nd serie is entered manually. Here I don't know how to do it with the variables, that the actual data (of humidity) from sql is also shown in the same chart like the temperature:
$(function() {
var x_values1 = [];
var y_values1 = [];
var switch1 = true;
$.get('values1.php', function(data1) {
data1 = data1.split('/');
for (var i in data1)
{
if (switch1 == true)
{
var ts = timeConverter(data1[i]);
x_values1.push(ts);
switch1 = false;
}
else
{
y_values1.push(parseFloat(data1[i]));
switch1 = true;
}
}
x_values1.pop();
$('#chart1').highcharts({
chart : {
type : 'spline'
},
title : {
text : 'Pi1'
},
subtitle : {
text : 'PiSensor # 1'
},
xAxis : {
title : {
text : 'Datum & Zeit'
},
categories : x_values1,
reversed : true
},
yAxis : [{
title : {
text : 'Temperatur'
},
labels : {
formatter : function() {
return this.value + ' C'
}
}
}, {
lineWidth: 1,
opposite: true,
title: {
text: 'Luftfeuchtigkeit'
},
labels : {
formatter : function() {
return this.value + ' %'
}
}
}],
tooltip : {
crosshairs : true,
shared : true,
valueSuffix : ''
},
plotOptions : {
spline : {
marker : {
radius : 4,
lineColor : '#666666',
lineWidth : 1
}
}
},
series : [{
name : 'Temperatur',
data : y_values1,
tooltip : {
valueSuffix: ' C'
}
}, {
name : 'Luftfeuchtigkeit',
data : [20,30,40,20],
yAxis : 1,
tooltip : {
valueSuffix : ' %'
}
}]
});
});
});
function timeConverter(UNIX_timestamp){
var a = new Date(UNIX_timestamp * 1000);
var months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
var year = a.getFullYear();
var month = months[a.getMonth()];
var date = a.getDate();
var hour = a.getHours();
var min = a.getMinutes() < 10 ? '0' + a.getMinutes() : a.getMinutes();
var time = date + ' ' + month + ' ' + year + ' ' + hour + ':' + min ;
return time;
}
If anyone could help out or route me on the right way, I would be really thankfull!
Edit:
I tried to manage the highchart with the two yAxis data series with this variant. Output of "values1.php" (unix_timestamp / temperature / humidity):
1524060302/27.3/36.4/1524060002/27.3/36.4/1524059702/27.3/36.4/
Output of "values1.php" with dateandtime (dateandtime / temperature / humidity):
2018-04-18 16:05:00/27.3/36.4/2018-04-18 16:00:00/27.3/36.4/2018-04-18 15:55:00/27.3/36.4/
Picture of the DB structure: DB structure
Picture of the DB values: DB values
Edit (19.04.2018 / 12:25)
I changed the php code like you said:
<?php
$con = mysql_connect("localhost","user","password");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("piSensors", $con);
$result = mysql_query("SELECT *
FROM `temperaturedata`
WHERE sensor=1
ORDER BY unix_timestamp DESC
LIMIT 288 ")
or die ("Connection error");
while($row = mysql_fetch_array($result)) {
echo $row['unix_timestamp'] . "/" . $row['temperature'] . "/" . $row['humidity'] . ";" ;
}
mysql_close($con);
?>
The output of the php file is now like that:
1524134103/26.1/38.1;1524133802/26.1/38.2;1524133502/26.1/37.8;1524133202/26.2/37.9;
Also I made the changes in the javascript:
$(function() {
var x_values1 = [];
var y_values1 = [];
var y_values2 = [];
var switch1 = true;
$.get('values1.php', function(data1) {
data1 = data1.split(';').reverse();
for (var i in data1) {
let tmpData = data1[i].split('/');
y_values1.push({x: parseInt(tmpData[0])*1000, y: parseFloat(tmpData[1])});
y_values2.push({x: parseInt(tmpData[0])*1000, y: parseFloat(tmpData[2])});
}
//x_values1.pop();
console.log(y_values1);
$('#chart1').highcharts({
chart : {
type : 'spline'
},
title : {
text : 'Buro'
},
subtitle : {
text : 'PiSensor # 1'
},
xAxis : {
title : {
text : 'Datum & Zeit'
},
categories : x_values1,
reversed : false
},
yAxis : [{
title : {
text : 'Temperatur'
},
labels : {
formatter : function() {
return this.value + ' C'
}
}
}, {
lineWidth: 1,
opposite: true,
title: {
text: 'Luftfeuchtigkeit'
},
labels : {
formatter : function() {
return this.value + ' %'
}
}
}],
tooltip : {
crosshairs : true,
shared : true,
valueSuffix : ''
},
plotOptions : {
spline : {
marker : {
radius : 4,
lineWidth : 1
}
}
},
series : [{
name : 'Temperatur',
data : y_values1,
color : '#FF0033',
tooltip : {
valueSuffix: ' C'
}
}, {
name : 'Luftfeuchtigkeit',
data : y_values2,
dashStyle: 'shortdot',
color : '#58ACFA',
zones : [{
value: 45,
color: '#00cc00',
}, {
value: 60,
color: '#ff9900',
}, {
value: 100,
color: '#ff0000',
}],
yAxis : 1,
tooltip : {
valueSuffix : ' %'
}
}]
});
});
});
function timeConverter(UNIX_timestamp){
var a = new Date(UNIX_timestamp * 1000);
var months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
var year = a.getFullYear();
var month = months[a.getMonth()];
var date = a.getDate();
var hour = a.getHours();
var min = a.getMinutes() < 10 ? '0' + a.getMinutes() : a.getMinutes();
var time = date + ' ' + month + ' ' + year + ' ' + hour + ':' + min ;
return time;
}
I added some zone-colors for the humidity (Luftfeuchtigkeit) and another dashStyle. This changes are working well.
But there's a problem with the xAxis Date & Time (or unix_timestamp right now). xAxis DateTime - Unix_Timestamp Problem in the HighCharts
The SQL statement is still the same (unix_timestamp) and the time converter function at the end is also not changed.
