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.