Embedded System Project #9 — Database
8 min readMay 7, 2023
Hello!
In this article, I’m gonna build a web server using MySQL database with data provided from ESP32. I’m using HTTP POST request to a PHP script to insert data to the database. This project is highly guided by randomnerdtutorials.com. So, without further ado, let’s get going!
STEP 1 — Hosting PHP Application and MySQL Database
I’m using free hosting service by https://www.000webhost.com/ because, well, it’s free. Sign yourself up to make an account.
STEP 2 — Preparing MySQL Database
After making an account, I’m immediately directed to make a website project.
To prepare the database, go to Tools > Database Manager > New Database. It required database name, username, and password. Database is already created, click manage and choose PhpMyAdmin.
To create SQL table, go to SQL tab in the dashboard. Fill that out with this 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
)
After that, click ‘Go’ at the bottom right corner.
STEP 3 — PHP Script HTTP POST, Inserting Data to MySQL Database
First, make a file named “post-esp-data.php” and use this code. Remember to replace $dbname, $username, $password with your own data.
<?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;
}Then, go to Tools > File Manager. Click the Upload Files and you will see this interface. Open public_html the upload the “post-esp-data.php” file.
Check https://yourdomain.000webhostapp.com/post-esp-data.php to check if the php file uploaded is working successfully. Here’s what it should look like.
STEP 4 — PHP Script, Displaying Database Content
Create another PHP file in /public-html to display database content to the web. Name it “esp-data.php”. Use 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="5" cellpadding="5">
<tr>
<td>ID</td>
<td>Sensor</td>
<td>Location</td>
<td>Value 1</td>
<td>Value 2</td>
<td>Value 3</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>Check https://yourdomain.000webhostapp.com/esp-data.php to check if the php file uploaded is working successfully. Here’s what it should look like.
STEP 5 — Setting up the ESP32
Parts and Components
- ESP32
- BMP280
- Jumper wires
- Breadboard
Schematics Diagram
ESP32 pins:
- GPIO22: SCL
- GPIO21: SDA
ESP32 Code
/*
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 = "WRITE_YOUR_SSID_HERE";
const char* password = "WRITE_YOUR_PASSWORD_HERE";
// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "https://yourdomain.000webhostapp.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 = "Home";
/*#include <SPI.h>
#define BMP_SCK 18
#define BMP_MISO 19
#define BMP_MOSI 23
#define BMP_CS 5*/
#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); // software SPI
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()) +
+ "&pressure=" + String(bmp.readPressure()/100.0F) + "&altitude=" + 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 = 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("{\"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
https.end();
}
else {
Serial.println("WiFi Disconnected");
}
//Send an HTTP POST request every 30 seconds
delay(30000);
}Two things to take notes,
- Modify the following lines with SSID and password of your WiFi credentials.
// Replace with your network credentials
const char* ssid = "REPLACE_WITH_YOUR_SSID";
const char* password = "REPLACE_WITH_YOUR_PASSWORD";- Modify the following line with your domain name, so the ESP will upload the measurement to the server.
const char* serverName = "https://yourdomain.000webhost.com/post-esp-data.php";STEP 6 — Demonstration
Open your serial monitor. If it’s shown HTTP Response code: 200, the data is successfully uploaded the web server. If it’s shown Error code: -1, it isn’t successful.
Go to https://yourdomain.000webhostapp.com/esp-data.php to see the data measured by BMP280.
YAY VOILA IT’S DONE. Arigatou Gozaimasu.
Problem that (is frustating) I faced
At the very last step, my data isn’t showing up at the website, it’s only giving me empty screen. After some LOTS of time, I realized that I mistakenly filled the $dbname, $username data in the php files without the codes before “esp_data” and “hilmi”. When I reuploaded the right code, It’s working properly.
