<!doctype html>
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
// MariaDB Server
$servername = "serverip";
$username = "dbuser";
$password = "dbpassword";
$dbname = "templogg";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// actual
$sql = "SELECT DATE_FORMAT(`dt`, '%H:%i') as `dtshort`,`temp`,`humidity`,`soil` FROM `tbllogging` ORDER BY dt desc LIMIT 1;";
$actual = $conn->query($sql);
if ($actual->num_rows > 0) {
// output data of each row
while($row = $actual->fetch_assoc()) {
$actdt = $row["dtshort"];
$acttemp = number_format($row["temp"],0);
$acthum = number_format($row["humidity"],0);
$actsoil = number_format($row["soil"],0);
}
} else {
echo "0 results";
}
// last 24h
$sqlday = "SELECT DATE_FORMAT(`dt`, '%H:%i') `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil` FROM `tbllogging` WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 24 GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d %H') ORDER BY dt asc;";
$daydt="";
$daytemp="";
$dayhum="";
$daysoil="";
$dayresult = $conn->query($sqlday);
if($dayresult->num_rows > 0)
{
while($row = mysqli_fetch_array($dayresult, MYSQLI_ASSOC))
{
$daydt.='"'.$row["dtshort"].'",';
$daytemp.=$row["temp"].",";
$dayhum.=$row["humidity"].",";
$daysoil.=$row["soil"].",";
}
} else {
echo "0 results";
}
// last week
$sqlweek = "SELECT DATE_FORMAT(MIN(`dt`), '%a %H:00') as `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil` FROM `tbllogging` WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 168 GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d'),FLOOR(EXTRACT(HOUR FROM `dt`)/6) ORDER BY dt asc;";
$weekdt="";
$weektemp="";
$weekhum="";
$weeksoil="";
$weekresult = $conn->query($sqlweek);
if($weekresult->num_rows > 0)
{
while($row = mysqli_fetch_array($weekresult, MYSQLI_ASSOC))
{
$weekdt.='"'.$row["dtshort"].'",';
$weektemp.=$row["temp"].",";
$weekhum.=$row["humidity"].",";
$weeksoil.=$row["soil"].",";
}
} else {
echo "0 results";
}
// Verbidnung schließen
$conn->close();
?>
<html>
<head>
<title>Temp+Humidity Logger v1</title>
<meta charset="utf-8" />
<meta name = "viewport" content ="width = 600">
<style type="text/css">
h1 {color:black;font-size: 350%;margin:0;}
h2 {color:black;font-size: 150%;maring:0;}
h3 {color:black;font-size: 80%;margin:0;}
</style>
<script type="text/javascript" src="Chart.min.js"></script>
</head>
<body>
<center>
<table border=0 width=600>
<?php
echo '<tr><th width=100><h2>' .$actdt.' </h2></th><th><h1>' . $acttemp. ' °C</h1></th><th width=120><h2>Luft: ' . $acthum. "%<br>Soil: ".$actsoil."</h2></th></tr>";
?>
</table>
<hr>
<span class="h3">last 24h</span><br>
<canvas id="DayChart" width="580" height="300"></canvas><br>
<hr>
<span class="h3">last week</span><br>
<canvas id="WeekChart" width="580" height="300"></canvas><br>
<table border=0 width=600>
<tr><th style="background-color:rgba(150,150,255,0.6)">Luftfeuchte</th></tr>
<tr><th style="background-color:rgba(0,0,130,0.6)">Temperatur</th></tr>
<tr><th style="background-color:rgba(172,194,132,0.6)">Boden</th></tr>
</table>
<script type="text/javascript">
// Global Chart definition
Chart.defaults.global.scaleBeginAtZero=true;
Chart.defaults.global.showTooltips=false;
Chart.defaults.global.scaleOverride= true;
Chart.defaults.global.scaleSteps=10;
Chart.defaults.global.scaleStepWidth=5;
Chart.defaults.global.scaleStartValue=10;
// Day Chart
var DayChartData = {
labels : [<?php
echo $daydt;
?>],
datasets : [
{
label: "Humidity",
fillColor : "rgba(150,150,255,0.4)",
strokeColor : "#9999FF",
pointDot : false,
bezierCurve : false,
data : [<?php
echo $dayhum;
?>]
},
{
label: "Soil",
fillColor : "rgba(172,194,132,0.4)",
strokeColor : "#ACC26D",
pointDot : false,
bezierCurve : false,
data : [<?php
echo $daysoil;
?>]
},
{
label: "Temp",
fillColor : "rgba(0,0,130,0.4)",
strokeColor : "#0000AA",
pointDot : false,
bezierCurve : false,
data : [<?php
echo $daytemp;
?>]
}
]
}
var day = document.getElementById("DayChart").getContext("2d");
new Chart(day).Line(DayChartData);
// Week Chart
var DayChartData = {
labels : [<?php
echo $weekdt;
?>],
datasets : [
{
label: "Humidity",
fillColor : "rgba(150,150,255,0.4)",
strokeColor : "#9999FF",
pointDot : false,
bezierCurve : false,
data : [<?php
echo $weekhum;
?>]
},
{
label: "Soil",
fillColor : "rgba(172,194,132,0.4)",
strokeColor : "#ACC26D",
pointDot : false,
bezierCurve : false,
data : [<?php
echo $weeksoil;
?>]
},
{
label: "Temp",
fillColor : "rgba(0,0,130,0.4)",
strokeColor : "#0000AA",
pointDot : false,
bezierCurve : false,
data : [<?php
echo $weektemp;
?>]
}
]
}
var week = document.getElementById("WeekChart").getContext("2d");
var weekchart = new Chart(week).Line(DayChartData);
</script>
</center>
</body>
</html>
