Embedded System Project 10: Mini Weather Station + Database
Hello! I’m back! This time, we will continue what we did before in project 9. We will try to make a database to receive sensor readings from BMP280. In other words, we will try to build an ESP32 client that makes an HTTP POST request to a PHP script to insert data (in this case sensor readings) into a MySQL database. For sure, we will also try to make a web page that displays the sensor readings, timestamps, and other information from the database. In other words, you may visualize your own data from anywhere in the world as long you can access your own server.
For this project, I will use this randomnerdtutorials as references! The main differences are I used BMP280 instead of BME280 and I modified some things on the table that is used to display database content.
Create MySQL Table
First of all, we need to create a MySQL database. There are many options to create a MySQL database. In my case, I will use 000webhost. If you guys also interested in using 000webhost (don’t worry, it’s free!), here is a short explanation of how to do it:
- Create your free website from 000webhost
- Create MySQL Database, here is a good link to explain it
- Then, click Manage > PhpMyAdmin
- Then, click SQL
- After that, you may copy the following code
CREATE TABLE SensorData (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sensor VARCHAR(30) NOT NULL,
location VARCHAR(30) NOT NULL,
value1 VARCHAR(10),
value2 VARCHAR(10),
value3 VARCHAR(10),
reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)- Next, don’t forget to click Go
Create PHP Script HTTP POST
Next, we will try to create a PHP script that is responsible for receiving incoming requests from the ESP32 and inserting the data into a MySQL database.
- Now, at the sidebar, open File Manager instead of Database Manager, then click Upload Files
- Then, open public_html folder
- Next, you may upload your PHP file that has the following snippet inside.
<?php/*
Rui Santos
Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files.
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
*/$servername = "localhost";// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";// Keep this API Key value to be compatible with the ESP32 code provided in the project page.
// If you change this value, the ESP32 sketch needs to match
$api_key_value = "tPmAT5Ab3j7F9";$api_key= $sensor = $location = $value1 = $value2 = $value3 = "";if ($_SERVER["REQUEST_METHOD"] == "POST") {
$api_key = test_input($_POST["api_key"]);
if($api_key == $api_key_value) {
$sensor = test_input($_POST["sensor"]);
$location = test_input($_POST["location"]);
$value1 = test_input($_POST["value1"]);
$value2 = test_input($_POST["value2"]);
$value3 = test_input($_POST["value3"]);
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3)
VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
}
else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}
else {
echo "Wrong API Key provided.";
}}
else {
echo "No data posted with HTTP POST.";
}function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
N.B. As usual, don’t forget to change the database name, username, and password before uploading. Example: If your database name is “MyDatabase”, you may make it
$dbname = "MyDatabase"; - In my case, I made the PHP file with the name “POST_DATA”, and the website with the name “choleraic-authoriza” (generated automatically). So, if I open this link will result:
Create PHP Script to Display Database Content
We are still not done with PHP Script! Next, we need to create another PHP Script in the /public_html directory to display all the database content on a web page. Personally, I named the file with the name “DATA”.
For the snippet, you may copy the following code.
<!DOCTYPE html>
<html><body>
<?php
/*
Rui Santos
Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files.
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
*/$servername = "localhost";// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}$sql = "SELECT id, sensor, location, value1, value2, value3, reading_time FROM SensorData ORDER BY id DESC";echo '<table cellspacing="15" cellpadding="5">
<tr>
<th>ID</th>
<th>Sensor</th>
<th>Location</th>
<th>Temperature (°C)</th>
<th>Pressure (Pa)</th>
<th>Approx Altitude (m)</th>
<th>Timestamp</th>
</tr>';
if ($result = $conn->query($sql)) {
while ($row = $result->fetch_assoc()) {
$row_id = $row["id"];
$row_sensor = $row["sensor"];
$row_location = $row["location"];
$row_value1 = $row["value1"];
$row_value2 = $row["value2"];
$row_value3 = $row["value3"];
$row_reading_time = $row["reading_time"];
// Uncomment to set timezone to - 1 hour (you can change 1 to any number)
//$row_reading_time = date("Y-m-d H:i:s", strtotime("$row_reading_time - 1 hours"));
// Uncomment to set timezone to + 4 hours (you can change 4 to any number)
//$row_reading_time = date("Y-m-d H:i:s", strtotime("$row_reading_time + 4 hours"));
echo '<tr>
<td>' . $row_id . '</td>
<td>' . $row_sensor . '</td>
<td>' . $row_location . '</td>
<td>' . $row_value1 . '</td>
<td>' . $row_value2 . '</td>
<td>' . $row_value3 . '</td>
<td>' . $row_reading_time . '</td>
</tr>';
}
$result->free();
}$conn->close();
?>
</table>
</body>
</html>
N.B. As usual, don’t forget to change the database name, username, and password before uploading. Example: If your database name is “MyDatabase”, you may make it (same as before)
After this, I can open this link with the result:
Yup! If you can see an empty table printed in your browser, it means that everything is ready. In the next section, we will try to learn how to insert BMP280 sensor readings data from ESP32 into the database.
Preparing ESP32
Now, we will assemble a simple circuit and upload the sketch provided to insert temperature, pressure, and approximate altitude into the database every 30 seconds.
Components and Parts Required
- ESP32 Development Board
- BMP280 sensor
- Jumper wires (I’m using male to male)
- Breadboard
Schematics Diagram
For this project, we will still use the same schematics diagram from the previous project.
As I explained before in the previous project, although in the schematics diagram I used BMP180, BMP280 can also work as long you pay attention to the pins! (I can’t found BMP280 sensor in fritzing, that’s why I used BMP180 instead of BMP280)
Some important notes:
- ESP32 3v3 connected to BMP280 VCC
- ESP32 GND connected to BMP280 GND
- GPIO 21 connected to BMP280 SCL
- GPIO22 connected to BMP280 SDA
ESP32 Code
Next, you may copy the following code
/*
This code references to: Rui Santos
Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files.
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.*/#include <WiFi.h>
#include <HTTPClient.h>#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BMP280.h>// Replace with your network credentials
const char* ssid = "REPLACE_WITH_YOUR_SSID";
const char* password = "REPLACE_WITH_YOUR_PASSWORD";// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "DOMAIN_NAME";// Keep this API Key value to be compatible with the PHP code provided in the project page.
// If you change the apiKeyValue value, the PHP file /post-esp-data.php also needs to have the same key
String apiKeyValue = "tPmAT5Ab3j7F9";String sensorName = "BMP280";
String sensorLocation = "Office";#define SEALEVELPRESSURE_HPA (1013.25)Adafruit_BMP280 bmp; // I2C
//Adafruit_BMP280 bmp(BMP_CS); // hardware SPI
//Adafruit_BMP280 bmp(BMP_CS, BMP_MOSI, BMP_MISO, BMP_SCK);void setup() {
Serial.begin(115200);
WiFi.begin(ssid, password);
Serial.println("Connecting");
while(WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.print("Connected to WiFi network with IP Address: ");
Serial.println(WiFi.localIP());// (you can also pass in a Wire library object like &Wire2)
bool status = bmp.begin(0x76);
if (!status) {
Serial.println("Could not find a valid BMP280 sensor, check wiring or change I2C address!");
while (1);
}
}void loop() {
//Check WiFi connection status
if(WiFi.status()== WL_CONNECTED){
HTTPClient http;
// Your Domain name with URL path or IP address with path
http.begin(serverName);
// Specify content-type header
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
// Prepare your HTTP POST request data
String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&value1=" + String(bmp.readTemperature())
+ "&value2=" + String(bmp.readPressure()) + "&value3=" + String((bmp.readAltitude(SEALEVELPRESSURE_HPA))) + "";
Serial.print("httpRequestData: ");
Serial.println(httpRequestData);
// You can comment the httpRequestData variable above
// then, use the httpRequestData variable below (for testing purposes without the BMP280 sensor)
//String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BMP280&location=Office&value1=24.75&value2=49.54&value3=1005.14";// Send HTTP POST request
int httpResponseCode = http.POST(httpRequestData);
// If you need an HTTP request with a content type: text/plain
//http.addHeader("Content-Type", "text/plain");
//int httpResponseCode = http.POST("Hello, World!");
// If you need an HTTP request with a content type: application/json, use the following:
//http.addHeader("Content-Type", "application/json");
//int httpResponseCode = http.POST("{\"value1\":\"19\",\"value2\":\"67\",\"value3\":\"78\"}");
if (httpResponseCode>0) {
Serial.print("HTTP Response code: ");
Serial.println(httpResponseCode);
}
else {
Serial.print("Error code: ");
Serial.println(httpResponseCode);
}
// Free resources
http.end();
}
else {
Serial.println("WiFi Disconnected");
}
//Send an HTTP POST request every 30 seconds (30.000 mili seconds)
delay(30000);
}
As usual, make sure to change the SSID, password, and domain name before uploading!
// Replace with your network credentials
const char* ssid = "REPLACE_WITH_YOUR_SSID";
const char* password = "REPLACE_WITH_YOUR_PASSWORD";// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "DOMAIN_NAME";
Result?
So…. I tried it, and here are the results!
I found this in the serial monitor in Arduino IDE. Fortunately, HTTP Response code: 200!
So, I tried to refresh the web page after 2–3 mins. VOILA!
Oh, wait! Probably some of you guys wondered about the ID. Where is the first ID? Why the ID starts from 2, not 1?!
No worries! I have a good explanation for it. Actually, I didn’t succeed on the first try. To be honest, I tried many times, but still failed! In the serial monitor in Arduino IDE, I got HTTP code 200, but on the web page, the data wasn’t updated. So, I tried to insert data into the database manually, and it succeeds. The data that I inserted manually has an ID with the value “1”. Later, I deleted the data, but that’s why when I succeed to send data with HTTP POST, the ID starts with 2, not 1.
For your information, my code didn’t work because I put the wrong server name in Arduino IDE, so make sure to pay attention! If apparently your web page also doesn’t work, I recommend debugging by inserting data manually to the database with SQL and do Serial.println the data to the serial monitor in Arduino IDE.
Additional information, in the Database Manager > Manage > PhpMyAdmin, you may find something like this:
You may manage the data stored in your SensorData table. You can edit it, copy it, delete it, etc.
BONUS
Code Explanation
I would like to summarize how the code works, which are:
- Import all libraries needed
- Set variables that you might want to change, which are: apiKeyValue, sensorName, sensorLocation
- The apiKeyValue is a random string, so feel free to modify it! It’s used for security reasons, so only anyone that knows your API key can publish data to your database
- Initialize the serial communication to debug
- Establish a Wi-Fi connection between ESP32 and the router
- Initialize the BMP280 sensor to get sensor readings (temperature, pressure, and approximate altitude)
Next, in the loop(), you make the HTTP POST request every 30 seconds (because we put delay(30000) in the end) with the latest BMP280 readings:
// Your Domain name with URL path or IP address with path
http.begin(serverName);
// Specify content-type header
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
// Prepare your HTTP POST request data
String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&value1=" + String(bmp.readTemperature())
+ "&value2=" + String(bmp.readPressure()) + "&value3=" + String((bmp.readAltitude(SEALEVELPRESSURE_HPA))) + "";// Send HTTP POST request
int httpResponseCode = http.POST(httpRequestData);
If you want, you may comment the httpRequestData variable above that concatenates all the BMP280 readings and use the httpRequestData variable below to test your code.
// You can comment the httpRequestData variable above
// then, use the httpRequestData variable below (for testing purposes without the BMP280 sensor)
//String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BMP280&location=Office&value1=24.75&value2=49.54&value3=1005.14";Conclusion
It seems that our journey is done. To conclude, we can combine the mini weather station we made in the previous project with the database. We can publish sensor data into a database in our own server domain that we can access from anywhere in the world. In my case, I used 000webhost.
I hope that this project can inspire you guys to keep trying and learning new things, especially about Embedded System! Don’t forget to have fun and see you guys in the next project!
