Visualize Your Sensor Readings from Anywhere in the World (ESP32/ESP8266 + MySQL + PHP)

In this project, you’ll create a web page that displays sensor readings in a plot that you can access from anywhere in the world. In summary, you’ll build an ESP32 or ESP8266 client that makes a request to a PHP script to publish sensor readings in a MySQL database.
As an example, we’ll be using a BME280 sensor connected to an ESP board. You can modify the code provided to send readings from a different sensor or use multiple boards.
To create this project, you’ll use these technologies:
ESP32 or ESP8266 programmed with Arduino IDE
Hosting server and domain name
PHP script to insert data into MySQL database and display it on a web page
MySQL database to store readings
PHP script to plot data from database in charts
You might also find helpful reading these projects:
ESP32/ESP8266 Insert Data into MySQL Database using PHP and Arduino IDE
ESP32/ESP8266 Plot Sensor Readings in Real Time Charts – Web Server
Table of Contents
The project is divided into the following main sections:
Hosting Your PHP Application and MySQL Database
Preparing Your MySQL Database
PHP Script HTTP POST – Insert Data in MySQL Database
PHP Script – Visualize Database Content in a Chart
Setting up the ESP32 or ESP8266
1. Hosting Your PHP Application and MySQL Database
The goal of this project is to have your own domain name and hosting account that allows you to store sensor readings from the ESP32 or ESP8266. You can visualize the readings from anywhere in the world by accessing your own server domain. Here’s a high level overview of the project:
I recommend using one of the following hosting services that can handle all the project requirements:
: free domain name when you sign up for the 3-year plan. I recommend choosing the unlimited websites option;
Digital Ocean
: Linux server that you manage through a command line. I only recommended this option for advanced users.
Those two services are the ones that I use and personally recommend, but you can use any other hosting service. Any hosting service that offers PHP and MySQL will work with this tutorial. If you don’t have a hosting account, I recommend
When buying a hosting account, you’ll also have to purchase a domain name. This is what makes this project interesting: you’ll be able to go your domain name (http://example.com) and see your ESP readings.
Note:
you can also run a
LAMP (Linux, Apache, MySQL, PHP) server
on a Raspberry Pi to
access data in your local network
. However, the purpose of this tutorial is to publish readings in your own domain name that you can access from anywhere in the world. This allows you to easily access your ESP readings without relying on a third-party IoT platform.
2. Preparing Your MySQL Database
After signing up for a
hosting account and setting up a domain name
, you can login to your cPanel or similar dashboard. After that, follow the next steps to create your database, username, password and SQL table.
Creating a database and user
Open the “
Advanced
” tab:
1.
Type “database” in the search bar and select “MySQL Database Wizard”.
2.
Enter your desired Database name. In my case, the database name is
esp_data
. Then, press the “Next Step” button:
Note:
later you’ll have to use the database name with the prefix that your host gives you (my database prefix in the screenshot above is blurred). I’ll refer to it as
example_esp_data
from now on.
3.
Type your Database username and set a password. You must save all those details, because you’ll need them later to establish a database connection with your PHP code.
That’s it! Your new database and user were created successfully. Now, save all your details because you’ll need them later:
Database name
: example_esp_data
Username
: example_esp_board
Password
: your password
Creating a SQL table
After creating your database and user, go back to cPanel dashboard and search for “phpMyAdmin”.
In the left sidebar, select your database name
example_esp_data
and open the “SQL” tab.
Important:
make sure you’ve opened the
example_esp_data
database. Then, click the SQL tab. If you don’t follow these exact steps and run the SQL query, you might create a table in the wrong database.
Copy the SQL query in the following snippet:
CREATE TABLE Sensor (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
value1 VARCHAR(10),
value2 VARCHAR(10),
value3 VARCHAR(10),
reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
Paste it in the SQL query field (highlighted with a red rectangle) and press the “Go” button to create your table:
After that, you should see your newly created table called
Sensor
in the
example_esp_data
database as shown in the figure below:
3. PHP Script HTTP POST – Insert Data in MySQL Database
In this section, we’re going to create a PHP script that receives incoming requests from the ESP32 or ESP8266 and inserts the data into a MySQL database.
If you’re using a hosting provider with cPanel, you can search for “File Manager”:
Then, select the
public_html
option and press the “+ File” button to create a new
.php
file.
Note:
if you’re following this tutorial and you’re not familiar with PHP or MySQL, I recommend creating these exact files. Otherwise, you’ll need to modify the ESP sketch provided with different URL paths.
Create a new file in
/public_html
with this exact name and extension:
post-data.php
Edit the newly created file (
post-data.php
) and copy the following snippet:
<?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 = $value1 = $value2 = $value3 = \"\";

if ($_SERVER[\"REQUEST_METHOD\"] == \"POST\") {
$api_key = test_input($_POST[\"api_key\"]);
if($api_key == $api_key_value) {
$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 Sensor (value1, value2, value3)
VALUES ('\" . $value1 . \"', '\" . $value2 . \"', '\" . $value3 . \"')\";

if ($conn->query($sql) === TRUE) {
echo \"New record created successfully\";
}
else {
echo \"Error: \" . $sql . \"\" . $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 need to modify the
$dbname
$username
and
$password
variables with your uniq