The plan
The plan is an old fashioned web application: Get data with whatever language (in my case with Python and the Adafruit_DHT library), save it in a database (MySQL) and retrieve & display it via PHP / HTML. In the end I want to display the sensor data in a small self-made web application.
MySQL connector
If you followed the other two guides dutifull, there is not much left do do: Just enhance the python3 library with the mysql-connector package and you're ready to query the database via python.
sudo pip3 install mysql-connector-python
Script automation
I am going to use two file parameters to get this script a little bit dynamic. Pin number and the sensor name are going to be prompted via command line because of obvious reasons: Data pin can change due to setup changes (more sensors, different setup) and data is going to be aggregated on an attribute - the sensor name. I am also going to implement an automated delete script - the delete query behind "rem" will get rid of all the old unwanted data (data older than 7 days).
#!/usr/bin/python3
import sys
from datetime import datetime
import mysql.connector
import board
import Adafruit_DHT
# write file parameters into script variables
pin = sys.argv[1]
name = sys.argv[2]
# date assignment
date = datetime.now()
# get sensor data with read_retry() Adafruit_DHT lib function
sensor = Adafruit_DHT.AM2302
humidity, temperature = Adafruit_DHT.read_retry(sensor, pin)
# mysql login information
mydb = mysql.connector.connect(
host="YOUR_SERVER",
user="YOUR_USER",
password="YOUR_PASSWORD",
database="YOUR_DATABASE"
)
mycursor = mydb.cursor()
# data insert query
sql = "INSERT INTO sensor_data (pin, name, date, temperature, humidity) VALUES (%s, %s, %s, %s, %s)"
val = (pin, name, date, temperature, humidity)
# delete query for data older than 7 days
rem = "DELETE FROM sensor_data WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)"
# query execution
mycursor.execute(sql, val)
mycursor.execute(rem)
mydb.commit()
# closing mysql connection
mycursor.close()
Final steps
After the insert query is working, I want to schedule it with crontab. Every 5 minutes should be good enough:
sudo crontab -e
*/5 * * * * sudo python3 /home/pi/YOUR_SCRIPT_NAME.py YOUR_PIN_NUMBER YOUR_SENSOR_NAME
Finally, query the MySQL database via PHP PDO: The code below can be used (edit the database settings and the prepare function to meet your system requirements) as a starting help.
<?php
/* SERVER CONNECTION */
const SERVER_NAME = 'YOUR_HOST';
const SERVER_DATABASE = 'YOUR_DATABASE';
const SERVER_USERNAME = 'YOUR_USERNAME';
const SERVER_PASSWORD = 'YOUR_PASSWORD';
class database{
public $connection;
function __construct(){
$this->connection = new PDO('mysql:host='.SERVER_NAME.';dbname='.SERVER_DATABASE.'', SERVER_USERNAME, SERVER_PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}
// GET FUNCTIONS
public function getData(){
$stmt = $this->connection->prepare("SELECT * FROM view_data ORDER BY name ASC;");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
}
$db = new database();
$data = $db->gatData();
// DEIFNE VARIABLES HERE
?>
If everything is working like intended, you should now have information in the $data array.
With a little effort made, the page could look like this:

Have fun playing with the snippets!