PHP | 3l3c7r1c pl4y6r0und

Irrigation – Part 6: Fuzzy Control

The easiest way to control automatic irrigation would be using the soil moisture as threshold value and start a pump with that. But I don’t think that this is a suitable approach to control watering of plants. The measuring value coming from the soil probe is rather more a reference point than a value to be used for a controller. I also want to implement a real controller keeping my soil moisture rather constant because I think that will lead to a better yield. The standard controller everybody knows is the PI-Controller. It is an ideal solution for many situations but in my case the disadvantage is that it can only process one measuring value. For sure there are some ways to deal with additional values such as feedforward or cascading but I won’t do that. I will use a Fuzzy Controller instead.

The Fuzzy Controller is a multi-input-multi-output (MIMO) control algorithm working with indistinct (fuzzy) definitions. That sounds very creative but the history goes back to 1965. In the 1990s there was a real hype about Fuzzy Control but then this concept sank into oblivion. In my opinion this control concept is still quite underestimated and unknown today and that’s very unfortunate because it deals well with the most common problems in controller design: no exact model and inly empirical knowledge about the needed algorithm.

I want to use our existing webserver so I will implement the controller in PHP. I have found a very nice class from Wojtek Jarzecji. The idea is that later on an Arduino will call the PHP script. The script will return a change request for the water setpoint (-10%…+10%). The Arduino will add this to the actual pump setpoint (0….100%) and transfer it to a pwm signal.

For the parameters I do some measurements in wet (~400) and in dry soil (~800). Google tells me that in my hometown average humidity is between 50% and 90% and temperature in summer times is between 8°C and 36°C. I take this values to define my input membership functions as follows:

That’s my script:

<!doctype html>
<!--?php
    require_once ('./fuzzy-logic-class.php');
    
    /*----------------Aktuelle Messwerte aus Datenbank anfragen----*/
    // <span class="hiddenSpellError" pre="" data-mce-bogus="1"-->MariaDB Server
    $servername = "IP-SERVER";
    $username = "USER";
    $password = "PASSWORD";
    $dbname = "templogg";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
    	die("Connection failed: " . $conn->connect_error);
    }

    // Actual Values
    $sql = "SELECT `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()) {
		$acttemp = number_format($row["temp"],0);
		$acthum = number_format($row["humidity"],0);
		$actsoil = number_format($row["soil"],0);
	}
    } else {
	echo "0 results";
    }

    /*----------------Fuzzy Parameters----*/
    $x = new Fuzzy_Logic();
    $x->clearMembers();
    /* ---------- set input members ---------*/
    $x->setInputNames(array('TEMP','HUMIDITY', 'SOIL'));
    $x->addMember($x->getInputName(0),'COLD',  0,  8, 25   ,LINFINITY);
    $x->addMember($x->getInputName(0),'WARM', 20, 35, 100  ,RINFINITY);
    $x->addMember($x->getInputName(1),'LOW', 0  ,40 ,60    ,LINFINITY);
    $x->addMember($x->getInputName(1),'HIGH',50 ,70 ,100   ,RINFINITY);
    $x->addMember($x->getInputName(2),'WET', 0,   400 ,650  ,LINFINITY);
    $x->addMember($x->getInputName(2),'DRY',550, 800 ,1025 ,RINFINITY);

    /* ---------- set output members ---------*/
    $x->setOutputNames(array('OUT'));
    $x->addMember($x->getOutputName(0),'LOWER',-10, -5 ,0 ,LINFINITY);
    $x->addMember($x->getOutputName(0),'RAISE',0, 5 ,10 ,RINFINITY);

    /* ---------- set rule table ------------ */
    $x->clearRules();
    $x->addRule('IF TEMP.WARM OR SOIL.DRY OR HUMIDITY.LOW THEN OUT.RAISE');
    $x->addRule('IF TEMP.COLD OR SOIL.WET OR HUMIDITY.HIGH THEN OUT.LOWER');


    /*---------- Get values from database and calculate output ----*/
    $x->SetRealInput('TEMP',	$acttemp);
    $x->SetRealInput('HUMIDITY' ,$acthum);
    $x->SetRealInput('SOIL' ,	$actsoil);
    $fuzzy_arr = $x->calcFuzzy();
    $Fuzzy = $fuzzy_arr['OUT'];
    echo $Fuzzy;
?>

<!doctype html>

<!--?php

require_once ('./fuzzy-logic-class.php');

/*----------------Aktuelle Messwerte aus Datenbank anfragen----*/

// <span class="hiddenSpellError" pre="" data-mce-bogus="1"-->MariaDB Server

$servername = "IP-SERVER";

$username = "USER";

$password = "PASSWORD";

$dbname = "templogg";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// Actual Values

$sql = "SELECT `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()) {

$acttemp = number_format($row["temp"],0);

$acthum = number_format($row["humidity"],0);

$actsoil = number_format($row["soil"],0);

}

} else {

echo "0 results";

}

/*----------------Fuzzy Parameters----*/

$x = new Fuzzy_Logic();

$x->clearMembers();

/* ---------- set input members ---------*/

$x->setInputNames(array('TEMP','HUMIDITY', 'SOIL'));

$x->addMember($x->getInputName(0),'COLD', 0, 8, 25 ,LINFINITY);

$x->addMember($x->getInputName(0),'WARM', 20, 35, 100 ,RINFINITY);

$x->addMember($x->getInputName(1),'LOW', 0 ,40 ,60 ,LINFINITY);

$x->addMember($x->getInputName(1),'HIGH',50 ,70 ,100 ,RINFINITY);

$x->addMember($x->getInputName(2),'WET', 0, 400 ,650 ,LINFINITY);

$x->addMember($x->getInputName(2),'DRY',550, 800 ,1025 ,RINFINITY);

/* ---------- set output members ---------*/

$x->setOutputNames(array('OUT'));

$x->addMember($x->getOutputName(0),'LOWER',-10, -5 ,0 ,LINFINITY);

$x->addMember($x->getOutputName(0),'RAISE',0, 5 ,10 ,RINFINITY);

/* ---------- set rule table ------------ */

$x->clearRules();

$x->addRule('IF TEMP.WARM OR SOIL.DRY OR HUMIDITY.LOW THEN OUT.RAISE');

$x->addRule('IF TEMP.COLD OR SOIL.WET OR HUMIDITY.HIGH THEN OUT.LOWER');

/*---------- Get values from database and calculate output ----*/

$x->SetRealInput('TEMP', $acttemp);

$x->SetRealInput('HUMIDITY' ,$acthum);

$x->SetRealInput('SOIL' , $actsoil);

$fuzzy_arr = $x->calcFuzzy();

$Fuzzy = $fuzzy_arr['OUT'];

echo $Fuzzy;

Irrigation– Part 3: Visualization

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.

Irrigation- Part 2: Data Recording

I use a Raspberry PI 2 as server for my data recording. Additionally you need a SD card, a power supply (I use just a USB cable to connect it to my access point) and if needed a WLAN stick. I strongly recommend to use a case e.g. like this one

First of all the Raspberry needs an operating system (raspbian) installed. There are plenty of manuals for that in the internet. As you have seen in the Arduino project I send my data using a web server. Writing a small socket server would have been the more lightweight solution but it is really a tough job to program a service that runs 24/7 stable and robust. Therefore I like to use some kind of a well-known and tested middleware. For data recording the combination of Apache webserver, PHP and MySQL (LAMP) is an ideal solution for me. There are many tutorials for setting up such a system in the internet (I used this one). Instead of using the “real” MySQL I use the open source successor MariaDB. I just replace

apt-get install apache2 apache2-utils php5 libapache2-mod-php5 php5-mysql mysql-server mysql-client phpmyadmin -y

apt-get install apache2 apache2-utils php5 libapache2-mod-php5 php5-mysql mariadb-server mysql-client phpmyadmin -y

MariaDB and MySQL are compatible on a wide scale. So you can use almost all the tools you use for MySQL also for MariaDB.

To improve my tool chain I also install a samba server on the Raspberry. You find a tutorial here. I develop on a Windows platform and the Samba server enables me to mount the wwwroot of the raspberry as network share. So I can simply develop on this share without taking a detour using FTP. For developing on the MariaDB you can use the installed PHPMyAdmin or you install the MySQL Workbench instead. The Workbench throws an error when connecting to a MariaDB but it’s working without any problems. It’s a matter of taste which way you prefer. Ich like the Workbench because I’m used to work in a real IDE for database development so the PHPMyAdmin feels a bit unfamiliar for me.

After setting up the Raspberry and the development tool chain you can create a database. I call my database simply “templogg”

CREATE DATABASE `templogg` /*!40100 DEFAULT CHARACTER SET latin1 */;

1	CREATE DATABASE `templogg` /!40100 DEFAULT CHARACTER SET latin1 /;

Then I create a table called “tbllogging”

CREATE TABLE `tbllogging` (
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `temp` double DEFAULT NULL,
  `humidity` double DEFAULT NULL,
  `soil` double DEFAULT NULL,
  PRIMARY KEY (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbllogging` (

`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

`temp` double DEFAULT NULL,

`humidity` double DEFAULT NULL,

`soil` double DEFAULT NULL,

PRIMARY KEY (`dt`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The column “dt” is a datetime column and acts as my primary key. The columns temp, humidity and soil are data type real and take the measuring values. As you could see in the Arduino code, the PHP script, called “newrecord.php” is missing. I put the script directly in the wwwroot.

<?php
$servername = "raspberryIP";
$username = "root";
$password = "password";
$dbname = "templogg";

echo 'Temp: ' . htmlspecialchars($_GET["temp"]) . "!\r\n";
echo 'Humidity: ' . htmlspecialchars($_GET["humidity"]) . "!\r\n";
echo 'Soil: ' . htmlspecialchars($_GET["soil"]) . "!\r\n";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO tbllogging (temp, humidity, soil)
VALUES (".$_GET["temp"].", ".$_GET["humidity"].",".$_GET["soil"].")";
//echo $sql;
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

<?php

$servername = "raspberryIP";

$username = "root";

$password = "password";

$dbname = "templogg";

echo 'Temp: ' . htmlspecialchars($_GET["temp"]) . "!\r\n";

echo 'Humidity: ' . htmlspecialchars($_GET["humidity"]) . "!\r\n";

echo 'Soil: ' . htmlspecialchars($_GET["soil"]) . "!\r\n";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "INSERT INTO tbllogging (temp, humidity, soil)

VALUES (".$_GET["temp"].", ".$_GET["humidity"].",".$_GET["soil"].")";

//echo $sql;

if ($conn->query($sql) === TRUE) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "

" . $conn->error;

}

$conn->close();

The script requires the MySQL extension in the php.ini.

The mysqli extension is not enabled by default, so the php_mysqli.dll DLL must be enabled inside of php.ini. In order to do this you need to find the php.ini file (typically located in c:\php), and make sure you remove the comment (semi-colon) from the start of the line extension=php_mysqli.dll, in the section marked [PHP_MYSQLI]. – http://php.net/manual/en/mysqli.installation.php

After that I try my script. I call the URL with some test data. You can do that with any browser.

http://raspberryip/newrecord.php?temp=33.00&humidity=44.00&soil=55.00

Then I check if the data has been recorded correctly.

SELECT * FROM tbllogging;

1	SELECT * FROM tbllogging;

When it works I delete the test data.

DELETE FROM tbllogging;

1	DELETE FROM tbllogging;

Now I start my Arduino and check if the recording works fine with that too. If that’s the case the prototype will do measurements on a houseplant for the next weeks.

In the meanwhile I will make a small website to display my measurements without using my database IDE.

Goodbye Magic Cards

Now it’s time to part with my Magic – The Gathering decks. After gathering dust for years I have decided to finally get rid of my former hobby. My wife recommended to just throw it away but after successfully selling my old stereo I prefer to earn some money instead. I mean, it really doesn’t make sense to throw away something if you can get some Euros for. There is only one question: what is a reasonable price?

What I will do now is writing a small script for determining the value of my cards. That’s a good life hack and a programming exercise.

Share this:

Share this:

Share this:

Share this: