Embedded Systems Project: ESP32 Database
The ninth embedded systems project will show you how we can make an HTTP POST request to a PHP script to insert data into a MySQL database. The data we are inserting can be anything, but in this project the data is sensor readings from BMP280 connected to ESP32.
This project can be broken down into several steps:
1. Hosting PHP application and MySQL database
2. Preparing MySQL database
3. Creating PHP script to insert data in MySQL database
4. Creating PHP script to display database content
5. Setting up the ESP32
There are several components and technologies we need for this project.
-> ESP32
-> BMP280
-> Jumper wires
-> Arduino IDE
-> Hosting server and domain name
-> PHP script to insert data into MySQL and display it on a web page
-> MySQL database to store readings
Now let’s take a closer look into how each steps is done!
1. Hosting PHP application and MySQL database
Below is a high-level overview of how data is sent and stored in database. credit: randomnerdtutorials
We need a hosting service that can handle the project requirements. Any hosting service that offers PHP and MySQL should work with this project. The recommended ones are Bluehost and Digital Ocean, which are paid services and good options for people who need to use a hosting service regularly.
Once again, you are allowed to use other services that meet the project requirements. For this project, I will be using 000webhost, a free hosting service.
Here’s how I set up my hosting account
- Go to 00webhost.com
- Sign up (or log in) with your email address
- Set up your website name and password
- Done!
Next, let’s set up the MySQL database. Go to your website dashboard, scroll down to find “Databases” and click on “MySQL Database”.
2. Preparing MySQL database
By default, your account should already have one database. We will not be using that one, instead create a new database by clicking “+ New Database” button.
Enter your desired database name, database username, and password.
Note that the database name cannot be the same as the database username.
After the database is successfully created, click manage, and click “PhpMyAdmin”. This will take you to phpMyAdmin, a software tool written in PHP to handle the administration of your MySQL database over the Web.
In phpMyAdmin, if you expand the left side bar, you should see the database you just created. Click on the database, and then click SQL.
In the SQL query field, copy the SQL query below to create a new empty table to store data. The table will be named “SensorData”, you can change the table name to your own preference. Paste the query, and then click Go.
CREATE TABLE SensorData (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sensor VARCHAR(30) NOT NULL,
location VARCHAR(30) NOT NULL,
temperature VARCHAR(10),
altitude VARCHAR(10),
pressure VARCHAR(10),
reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
If your query is successfully executed, on the right side bar you should see the table name under the database.
If you want to create more tables, just run the query again with a different table name and attributes, or click “New” above the table you just created.
3. Creating PHP script to insert data in MySQL database
The PHP script we will create is used for receiving incoming requests from the ESP32 and inserting data into a MySQL database.
In your website dashboard, scroll down to find “File Manager” under “Files”
Click Upload Files, then double click the public_html folder. Click the New File icon on the top bar to create a new file.
Create a new file inside public_html folder named post-esp-data.php
Locate the file and right click it to edit the content. Copy-paste the following snippet as the file content.
(credit: randomnerdtutorials)
<?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 = $temperature = $altitude = $pressure = "";
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"]);
$temperature = test_input($_POST["temperature"]);
$altitude = test_input($_POST["altitude"]);
$pressure = test_input($_POST["pressure"]);
// 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, temperature, altitude, pressure)
VALUES ('" . $sensor . "', '" . $location . "', '" . $temperature . "', '" . $altitude . "', '" . $pressure . "')";
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;
}Before saving the file, you MUST modify the $dbname, $username, and $password variables with your own database details.
After you’re done, you can click Save & Close.
4. Creating PHP script to to display database content
Another PHP script is used to display all the database content on a web page.
Open 000webhost file manager web (files.000webhost.com) that you already opened before in step 3.
Create a new file named esp-data.php inside the public_html folder just like before.
Locate the file and right click it to edit the content. Copy-paste the following snippet as the file content.
(credit: randomnerdtutorials)
<!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, temperature, altitude, pressure, 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>Altitude</td>
<td>Pressure</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_temperature = $row["temperature"];
$row_altitude = $row["altitude"];
$row_pressure = $row["pressure"];
$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_temperature . '</td>
<td>' . $row_altitude . '</td>
<td>' . $row_pressure . '</td>
<td>' . $row_reading_time . '</td>
</tr>';
}
$result->free();
}
$conn->close();
?>
</table>
</body>
</html>Once again, you must modify the $dbname, $username, and $password variables with your own database details. Then click Save & Close button.
5. Setting up the ESP32
We finally reach the final step of this project! Set up your ESP32 board and BMP280 sensor using jumper wires. Follow the wiring steps below.
Connect the ESP32 board to your computer, then upload the sketch below in Arduino IDE. (credit: randomnerdtutorials)
/*
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 <WiFiClientSecure.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 = "https://example.com/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 = "BMP280";
String sensorLocation = "Office";
#define SEALEVELPRESSURE_HPA (1013.25)
Adafruit_BMP280 bmp; // I2C
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){
WiFiClientSecure *client = new WiFiClientSecure;
client->setInsecure(); //don't use SSL certificate
HTTPClient https;
// Your Domain name with URL path or IP address with path
https.begin(*client, serverName);
// Specify content-type header
https.addHeader("Content-Type", "application/x-www-form-urlencoded");
// Prepare your HTTP POST request data
String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&temperature=" + String(bmp.readTemperature())
+ "&altitude=" + String(bmp.readAltitude(SEALEVELPRESSURE_HPA)) + "&pressure=" + String(bmp.readPressure()/100.0F) + "";
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&temperature=24.75&altitude=49.54&pressure=1005.14";
// Send HTTP POST request
int httpResponseCode = https.POST(httpRequestData);
// If you need an HTTP request with a content type: text/plain
//https.addHeader("Content-Type", "text/plain");
//int httpResponseCode = https.POST("Hello, World!");
// If you need an HTTP request with a content type: application/json, use the following:
//https.addHeader("Content-Type", "application/json");
//int httpResponseCode = https.POST("{\"temperature\":\"19\",\"altitude\":\"67\",\"pressure\":\"78\"}");
if (httpResponseCode>0) {
Serial.print("HTTP Response code: ");
Serial.println(httpResponseCode);
}
else {
Serial.print("Error code: ");
Serial.println(httpResponseCode);
}
// Free resources
https.end();
}
else {
Serial.println("WiFi Disconnected");
}
//Send an HTTP POST request every 30 seconds
delay(30000);
}There are several variables you need to modify.
- ssid : modify with your WiFi or network SSID
- password : modify with your WiFi or network password
- serverName : modify with your website or domain name (note: keep the “/post-esp-data.php”)
- sensorLocation : modify with where the BMP280 sensor is located (optional)
You also need to install the libraries required to run the code inside the Arduino IDE. The libraries are the first 6 uncommented lines in the sketch.
After the code is successfully uploaded, you are done with the final step!
Testing
First, lets test if we can access the domain. Open your website or domain name. For example, my domain name is embprojtest.000webhostapp.com/esp-data.php
When I open my website, I got the message above. Then I realized I forgot to change the dbname, username, and password variables in the php files.
After changing the variables in the php file, it seems that I needed to delete my browser cache. Instead of deleting cache, just open the domain in incognito mode.
As you can see from the picture above, my domain is now working properly.
Moving on to Arduino IDE, upload the code to your ESP32. Press “EN” button on the ESP32 board. Open the Serial Monitor at a baud rate of 115200, and what is shown should be the similar to this picture below.
In the website, you should be able to see all the readings stored in your database. Looks like the timestamp in the database is set on a different time zone, but you can see from the minutes that the data displayed on the Serial Monitor and the one displayed on the website is the same! To see the newest readings, refresh the web page.
You can go back to phpMyAdmin, see and manage the data stored in database, in the SensorData table.
That’s all for ESP32 Database project, thanks for reading and I’ll see you on the next project~＼ʕ•ᴥ•ʔ／
