Javascript | 3l3c7r1c pl4y6r0und

For sure I want to see the measuring values from by automatic plant irrigation. But it is a bit annoying to use my database tools all the time. That’s the reason for me to make a small visualisation. I want to see the actual values as text and two trends. One showing the last 24h and the other showing the last week.

I have already my webserver so the drug of choice for me is making a website. But that’s not the only reason. It is becoming state of the art to make front-ends with web technologies. The advantages are obvious, simple deployment, platform independent (especially mobile devices). Furthermore web technologies have gained power over the last years (e.g. web socket, HTML5, SVG). Also rendering performance improved enough to render trends fluidly.

I always develop and test queries separately in Workbench IDE. For a better readability in my trends I compress the data a bit. The 24h data shows the average per hour. The data for the last week shows the average per quarter day. The value for soil moisture is scaled 1024 = 100%.

# Actual Values
SELECT DATE_FORMAT(`dt`, '%H:%i') as `dtshort`,`temp`,`humidity`,`soil`
	FROM `tbllogging`
	ORDER BY dt desc LIMIT 1;

# last 24h (average per hour)
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;
    
# last week (average per quarter day)
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;s

# Actual Values

SELECT DATE_FORMAT(`dt`, '%H:%i') as `dtshort`,`temp`,`humidity`,`soil`

FROM `tbllogging`

ORDER BY dt desc LIMIT 1;

# last 24h (average per hour)

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;

# last week (average per quarter day)

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;s

Usually I would put the queries in stored procedures and use them as they offer some advantages but unfortunately the mysqli does not support stored procedures returning multiple row sets. Therefore you find the queries hard-coded in the PHP script.

For displaying the trend data I use a 3rd party charting library. I use Chart.js in this case. It is for sure not the most powerful library but it is slim, fast, free and open source (MIT license).

That’s the code for the website

<!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. ' &degC</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>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

<!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>

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>

</head>

<body>

<?php

echo '<tr><th width=100><h2>' .$actdt.' </h2></th><th><h1>' . $acttemp. ' &degC</h1></th><th width=120><h2>Luft: ' . $acthum. "%<br>Soil: ".$actsoil."</h2></th></tr>";

</table>

<hr>

<hr>

<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>

// 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>

The result is a nice website that also works great on mobile devices.

Tag: Javascript

Irrigation– Part 3: Visualization

Share this: