Get to Know ESP32 #12: Inserting (Weather) Data to MySQL Database with PHP
The last project of ESP32 in this class, building a weather database all around Indonesia!
You know, when playing games, you start from the bottom, struggling to fight low-level enemies, and towards the end, you’ll meet the boss. This project is like the big boss, the final one. Hmm, I smell.. chicken dinner. *lol what*
Let’s see, what’s the deal in this project? Okay, we’re going to measure the weather in our environment using a weather sensor (in my case it’s DHT22, but my friends use different types of sensor — such as BME280). The measurements are going to be periodically sent to a database. This database will consist of different weather records from all my friends who take this class. So, we’re able to see how the weather looks like in any part of the world where our friends are testing ESP32. Sounds pretty cool, huh? Yes, and again, this project is guided by randomnerdtutorials.
Above is an overview of this project. First, the ESP32 accepts weather measurements data from a weather sensor (mine uses DHT22). ESP32 is going to connect to Wi-Fi to access domain. ESP32 is also going to send a HTTP POST request frequently. The php script from our domain will publish sensor readings to a relation in the MySQL database, and then there’ll be another php script which enables data visualization from anywhere.
Preparation
Hardware
These are the tools needed for this project. We’re not going to use external resistor since DHT22 already has a built-in resistor.
- 1 Laptop with Arduino IDE installed and configured
- 1 NodeMCU ESP-32S development board with 30 pins (or any ESP32)
- 2 male-to-male wire jumpers
- 3 female-to-male wire jumpers
- 1 DHT22 sensor
- 1 Type-A USB to Micro-USB cable
- 1 Breadboard
MySQL database
First, let’s give a shoutout to my friend, Hardy Valenthio, for providing a domain name and hosting account for all of his classmates. He had created a database and user, and also prepared the SQL table in phpMyAdmin. So, we no longer have to prepare the database and only need to log in.
Circuit
The circuit is based on the schema below.
Below are some photos of the circuit I managed to build.
Code — Set Up: PHP Sript HTTP POST
Here, we’ll set up a PHP script to receive incoming requests to insert data to database from ESP32.
First, we need to log in with Username and Password.
Then, open File Manager and select public_html. Create new file there by clicking +File button.
According to terms and agreements with Hardy (Hardy is a really professional guy, I salute that) we’re going to create files for ourselves. I created a new file named ‘Betsy-post-esp-data.php’. This is how a file creation dialog message looked like.
To edit the file, click right. It should look like this. Click Edit.
Copy this code.
<?php
$servername = "localhost";
// REPLACE with your Database name
$dbname = "<database_name>";
// REPLACE with Database user
$username = "<database_user>";
// REPLACE with Database user password
$password = "<database_pass>";
// 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"]); NOT USED SINCE DHT22 DOESN'T SUPPORT HUMIDITY
$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, value3)
VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $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;
}As you can see, I didn’t take inputs for value 2 which is pressure, since my sensor is DHT22 and it only provides measurements for temperature and humidity. Before saving changes to the code, make sure you’ve filled the database name, username, password, and API key value according to your unique details.
Next, we’ll try to access our domain name.
This is how the output looked like and it’s correct.
Code — Set Up: PHP Sript to Display Database
Let’s go back to public_html and create a new file to display database content in a web page. I named the file ‘Betsy-esp-data.php’.
Edit the file and copy this code below.
<!DOCTYPE html>
<html><body>
<?php
$servername = "localhost";// REPLACE with your Database name
$dbname = "<db_name>";
// REPLACE with Database user
$username = "<db_user>";
// REPLACE with Database user password
$password = "<db_pw>";// 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, value3, reading_time FROM SensorData ORDER BY id DESC";echo '<table cellspacing="5" cellpadding="5">
<tr>
<td>ID</td>
<td>Sensor</td>
<td>Location</td>
<td>Temperature</td>
<td>Atmospheric Pressure (atm) </td>
<td>Humidity</td>
<td>Timestamp</td>
</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>
As you can see, this PHP script sets the display of webpage, so I changed the column names in this code.
echo '<table cellspacing="5" cellpadding="5">
<tr>
<td>ID</td>
<td>Sensor</td>
<td>Location</td>
<td>Temperature</td>
<td>Atmospheric Pressure (atm) </td>
<td>Humidity</td>
<td>Timestamp</td>
</tr>';Code
Now, let’s go back to Arduino IDE and create a new file. Copy this code below.
#include <WiFi.h>
#include <HTTPClient.h>#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <DHT.h>// Replace with your network credentials
const char* ssid = "SSID";
const char* password = "PW";// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "http://<domain>/post-esp-data.php";// 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 = "DHT22";
String sensorLocation = "Betsy (Jakarta)";#define DHTPIN 4 // Digital pin connected to the DHT sensor
#define DHTTYPE DHT22 // DHT 22DHT dht(DHTPIN, DHTTYPE);DHT dht(DHTPIN, DHTTYPE); // The object representing your DHT22 sensorvoid setup() {
Serial.begin(115200);
delay(2000);
dht.begin();initWifi();
}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(dht.readTemperature())
+ "&value3=" + String(dht.readHumidity()) + "";
Serial.print("httpRequestData: ");
Serial.println(httpRequestData);
// You can comment the httpRequestData variable above
// then, use the httpRequestData variable below (for testing purposes without the BME280 sensor)
//String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BME280&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 minute
delay(60000);
}void initWifi() {
Serial.print("Connecting to: ");
Serial.print(ssid);
WiFi.begin(ssid, password);
while(WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.print("Connected to WiFi network with IP Address: ");
Serial.println(WiFi.localIP());
}
I made the initWifi() function to be a separate function to make debug easier. I also changed the DHT pin according to the GPIO I’m using. Finally, I changed the interval of each HTTP POST request to every minute.
Demonstration
As always, connect the ESP32 to Laptop using Type-A to Micro-USB cable. Upload the code, and after it says ‘Done Uploading’ open Serial Monitor at 115200 baud rate. If it doesn’t show anything, press EN button. Here’s how my Serial Monitor looked like.
Next, we’ll go to the webpage. To access enter the URL.
http://<domain>/esp-data.phpHere’s how the webpage that displays all weather measurements from my classmates (including me) looks like.
Then, we can also go to cPanel home, and then scroll until we see ‘Databases’. Click on the phpMyAdmin.
We can see the data stored in SensorData. This data can be deleted, copied, or edited.
Yay! That’s it. We’ve successfully done the project.
Ladies and gentlemen..
We’ve already reached the end of this Embedded System’s class projects. A lot of the projects have required a lot of time to debug, while some are simple enough to be done in one day. I actually feel really grateful to Sir Soni that has given me a wonderful chance to explore ESP32, this microcontroller that opened my eyes — electronics aren’t that annoying. I’m also really grateful that Sir Soni always measures the external conditions in giving us projects, thus making us able to set rational goals on finishing these projects that won’t hurt our bodies’ health. I really look forward on making more projects with ESP32, with the help of randomnerdtutorials, of course!
As a closing statement, I’d like to quote Sir Soni’s favourite line.
It’s the process that matters the most, not the final result.
Adios, see you! I genuinely mean it!