With this project we create an IoT Environmental control device that uses a MariaDB (MySQL) database as the datastore. This allows you to use database functionality to create log files and be able to corrrelate data.
This project creates a web accessible dashboard that shows the current temperature, and gives the ability to turn a fan on or off.
Prerequisites
- Arduino – Send Commands with Serial Communication
- Arduino – Read Serial Communication with Raspberry Pi
- Arduino – Send Serial Commands from Raspbery Pi
- Arduino – Bidirectional Serial Communication with Raspberry Pi
- Arduino – Temperature Web App with Raspberry Pi
- Arduino – Raspberry Pi Web Based Fan Switch
- MySQL Series
Functional Parts in the Project:
- Arduino Uno – https://store.arduino.cc/usa/arduino-uno-rev3
- 560 Piece Jumper Wire Kit – https://amzn.to/2MsCLjL
- Breadboard Kit – https://amzn.to/2Xih5ei
- Analog Temperature Sensor – https://amzn.to/2Rkkl3k
- Raspberry Pi
- USB Cable
- DLI IoT Relay – https://amzn.to/2zPR3ou
- Fan or Other Plugin electric Device
Arduino Sketch
String command;
#define sensorPin A0
#define fan 8
void setup() {
Serial.begin(9600);
pinMode(fan, OUTPUT);
}
void loop() {
int reading = analogRead(sensorPin);
float voltage = reading * 5.0;
voltage /= 1024.0;
float temperatureC = (voltage - 0.5) * 100 ;
float temperatureF = (temperatureC * 9.0 / 5.0) + 32.0;
Serial.println(temperatureF);
if (Serial.available()) {
command = Serial.readStringUntil('\n');
command.trim();
if (command.equals("on")) {
digitalWrite(fan, HIGH);
}
else if (command.equals("off")) {
digitalWrite(fan, LOW);
}
else {
digitalWrite(fan, HIGH);
delay(500);
digitalWrite(fan, LOW);
}
}
delay(1000);
}
Raspberry Pi Setup
- Install Apache 2
- Install PHP
- Install MariaDB
- Install PHP and Python MySL Connectors
- Make Pi user owner of /var/www/html
sudo apt update
sudo apt-get upgrade
sudo apt-get install apache2
sudo chown pi /var/www/html
sudo apt-get install php
sudo apt-get install mariadb-server
sudo apt-get install php-mysql
Install Python Mysql Connector from in Thonny
Tools -> Manage Packages
mysql-connector -> Search on PyPI
Click on Link
Click INSTALL
MariaDB/MySQL Setup
- Note that this is not a secure MySQL setup. This is simply a basic installation to function for this project
sudo mysql
create database tempApp;
use tempApp;
create table temp(
temp_id int auto_increment primary key,
temp float,
timestamp not null default current_timestamp()
);
create table fanStatus(
status_id int auto_increment priamry key,
status text,
timestamp not null default current_timestamp()
);
create user tempUser identified by '123456';
grant all privileges on tempApp.* to tempUser;
tempMysqlPhp.py – Python Script
import mysql.connector
import serial
if __name__ == '__main__':
ser = serial.Serial('/dev/ttyACM1',9600, timeout=1)
ser.flush()
mydb = mysql.connector.connect(
host="localhost",
user="tempUser",
password="123456",
database="tempApp"
)
mycursor = mydb.cursor()
while True:
if ser.in_waiting > 0:
#Take Value from Arduino and Save to Database
line = ser.readline().decode('utf-8').rstrip()
temp = float(line)
sql = "insert into temp(temp) values (%s)" %(temp)
mycursor.execute(sql)
mydb.commit()
#Read Fan Status from Database and Send Command to Arduino
mycursor.execute("select status from fanStatus order by timestamp desc limit 1")
fanStatus = mycursor.fetchall()
fanStatus = fanStatus[0][0]
if fanStatus == "on":
ser.write(b"on\n")
elif fanStatus == "off":
ser.write(b"off\n")
else:
ser.write(b"error\n")
print ("error")
#Troubleshooting Procedure
print(temp, " inserted. Fan Status is",fanStatus)
tempMysqlPhp.php – PHP Script
<meta http-equiv="refresh" content="5">
<h1 style="text-align:center;">Database Temp App</h1>
<?php
$fanStatusUpdate = $_GET["fanStatus"];
$servername = "localhost";
$username = "tempUser";
$password = "123456";
$db = "tempApp";
$conn = new mysqli($servername, $username, $password, $db);
if($conn->connect_error){
die("Connection failed ".$conn->connect_error);
}
$sql = "select * from temp order by timestamp desc limit 1";
$result = $conn->query($sql);
if ($result->num_rows > 0){
while($row = $result->fetch_assoc() ){
print "<p style='text-align:center; font-size:200px; margin-top: 10px; margin-bottom: 10px;color:".$color.";'>".$row["temp"]."</p>";
print "<p style='text-align:center; font-size:50px; margin-top: 10px; margin-bottom: 10px;color:".$color.";'>".$row["timestamp"]."</p>";
}
} else {
echo "0 records";
}
$sqlAvg = "select min(temp) as minTemp, max(temp) as maxTemp, avg(temp) as avgTemp from temp";
$avgTemp = $conn->query($sqlAvg);
$avgResult = $avgTemp->fetch_assoc();
print "<div style='border: 1px solid black; width:300px;margin-left:auto; margin-right:auto;padding:10px;'>";
print "Average Temp: ".$avgResult["avgTemp"];
print "<br>";
print "Minimum Temp: ".$avgResult["minTemp"];
print "<br>";
print "Maximum Temp: ".$avgResult["maxTemp"];
print "</div>";
$sql = "select * from fanStatus order by timestamp desc limit 1";
$result = $conn->query($sql);
print "<div style='border: 1px solid black; width:300px;margin-left:auto; margin-right:auto;padding:10px;'>";
if ($result->num_rows > 0){
while($row = $result->fetch_assoc() ){
$fanStatus = $row["status"];
$fanTimestamp = $row["timestamp"];
if ($fanStatus != $fanStatusUpdate){
$sqlFanStatus = "insert into fanStatus(status) values('$fanStatusUpdate')";
if ($conn->query($sqlFanStatus) === FALSE) {
echo "GET Error";
}
}
}
} else {
echo "Fan Status Log is Empty<br>";
}
if ($fanStatus == "on"){
print "Fan is ON since: ".$fanTimestamp."<br>";
print "<a href='tempMysqlPhp.php?fanStatus=off'>Turn Fan OFF</a>";
} else if ($fanStatus =="off"){
print "Fan is OFF since: ".$fanTimestamp."<br>";
print "<a href='tempMysqlPhp.php?fanStatus=on'>Turn Fan ON</a>";
}else {
print "Fan Controller Error<br>";
print "<a href='tempMysqlPhp.php?fanStatus=off'>Reset Fan Communciation</a>";
}
print "</div>";
$conn->close();
?>
Thank you