Arduino – Raspberry Pi Web Fan Control with MySQL

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

Functional Parts in the Project:

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();

?>

1 Comment

Leave a Reply