Embedded System Project 11: ESP32 + Visualizing Sensor Readings from Anywhere in the World

Hi guys! In this project, you will learn to create a web page that displays sensor readings in a plot that you can access from anywhere in the world. In this case, you will learn to build an ESP32 client that makes a request to a PHP script to publish sensor readings (In my case is BMP280 sensor readings) in a MySQL database. Although I will use ESP32 and BMP280, you may use a different sensor or multiple boards.

For this embedded system project, I will use this randomnerdtutorials as references. Notable differences are I will use BMP280 instead of BME280 and get approximate altitude instead of humidity!

Technologies needed

  • ESP32 programmed with Arduino IDE software
  • 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

Ah, right! Before we continue, you may want to check my previous project which is about the mini weather station with a database! Because it could be said that this final project is the continuation of the previous project!

Let’s do this!

First, you need to host your PHP application and MySQL database. Here’s a pretty good high-level overview for this project! I used this link as a reference with some modifications!

Overview (references to randomnerdtutorials)

If you still don’t have your own domain name and hosting account that allows you to store sensor readings from the ESP32, I recommend checking my previous project! In my case, I used 000webhost.

Next, you need to prepare your MySQL database and make PHP script HTTP POST to insert data in the MySQL database. But, because this is already explained in the previous project, to make this project as brief as possible, I will skip these parts.

After that, you need to create a PHP script to visualize database content in the chart. To do this, create another PHP file in the /public_html directory that will plot the database content in a chart on a web page. Personally, I named it “ESP_CHART”

You may copy the following snippet:

<!--
This code references to: Rui Santos
Complete project details at https://RandomNerdTutorials.com

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.

-->
<?php

$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, value1, value2, value3, reading_time FROM SensorData order by reading_time desc limit 40";$result = $conn->query($sql) or die($conn->error);while ($data = $result->fetch_assoc()){
$sensor_data[] = $data;
}
$readings_time = array_column($sensor_data, 'reading_time');$i = 0;
foreach ($readings_time as $reading){
$readings_time[$i] = date("Y-m-d H:i:s", strtotime("$reading + 7 hours")); //convert timezone to +7
$i += 1;
}
$value1 = json_encode(array_reverse(array_column($sensor_data, 'value1')), JSON_NUMERIC_CHECK);
$value2 = json_encode(array_reverse(array_column($sensor_data, 'value2')), JSON_NUMERIC_CHECK);
$value3 = json_encode(array_reverse(array_column($sensor_data, 'value3')), JSON_NUMERIC_CHECK);
$reading_time = json_encode(array_reverse($readings_time), JSON_NUMERIC_CHECK);
/*echo $value1;
echo $value2;
echo $value3;
echo $reading_time;*/
$result->free();
$conn->close();
?>
<!DOCTYPE html>
<html>
<meta name="viewport" content="width=device-width, initial-scale=1">
<script src="https://code.highcharts.com/highcharts.js"></script>
<style>
body {
min-width: 310px;
max-width: 1280px;
height: 500px;
margin: 0 auto;
}
h2 {
font-family: Arial;
font-size: 2.5rem;
text-align: center;
}
</style>
<body>
<h2>ESP Weather Station</h2>
<div id="chart-temperature" class="container"></div>
<div id="chart-approximate-altitude" class="container"></div>
<div id="chart-pressure" class="container"></div>
<script>
var value1 = <?php echo $value1; ?>;
var value2 = <?php echo $value2; ?>;
var value3 = <?php echo $value3; ?>;
var reading_time = <?php echo $reading_time; ?>;
var chartT = new Highcharts.Chart({
chart:{ renderTo : 'chart-temperature' },
title: { text: 'BMP280 Temperature' },
series: [{
showInLegend: false,
data: value1
}],
plotOptions: {
line: { animation: false,
dataLabels: { enabled: true }
},
series: { color: '#059e8a' }
},
xAxis: {
type: 'datetime',
categories: reading_time
},
yAxis: {
title: { text: 'Temperature (Celsius)' }
//title: { text: 'Temperature (Fahrenheit)' }
},
credits: { enabled: false }
});
var chartH = new Highcharts.Chart({
chart:{ renderTo:'chart-pressure' },
title: { text: 'BMP280 Pressure' },
series: [{
showInLegend: false,
data: value2
}],
plotOptions: {
line: { animation: false,
dataLabels: { enabled: true }
}
},
xAxis: {
type: 'datetime',
//dateTimeLabelFormats: { second: '%H:%M:%S' },
categories: reading_time
},
yAxis: {
title: { text: 'Pressure (Pa)' }
},
credits: { enabled: false }
});
var chartP = new Highcharts.Chart({
chart:{ renderTo:'chart-approximate-altitude' },
title: { text: 'BMP280 Approximate Altitude' },
series: [{
showInLegend: false,
data: value3
}],
plotOptions: {
line: { animation: false,
dataLabels: { enabled: true }
},
series: { color: '#18009c' }
},
xAxis: {
type: 'datetime',
categories: reading_time
},
yAxis: {
title: { text: 'Approximate Altitude (m)' }
},
credits: { enabled: false }
});
</script>
</body>
</html>

As usual, don’t forget to change this part just like the previous project!

// Your Database name 
$dbname = "example_esp_data";
// Your Database user
$username = "example_esp_board";
// Your Database user password
$password = "YOUR_USER_PASSWORD";

If you don’t really follow my previous project, you also need this PHP file in the /public_html directory. Personally, I named it “POST_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";// Your Database name
$dbname = "example_esp_data";
// Your Database user
$username = "example_esp_board";
// Your Database user password
$password = "YOUR_USER_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;
}

In the end, this will be how the /public_html directory looked.

/public_html

Don’t need to worry with DATA.php! As we don’t need it for this project (that PHP file is needed for the previous project),

Also, I would like to point out to be careful in naming! For example, in a previous project, I named the table “Sensor_Data”

$sql = "SELECT id, value1, value2, value3, reading_time FROM Sensor_Data order by reading_time desc limit 40";

Meanwhile, randomnerdtutorials used “Sensor” instead of “Sensor_Data”!

$sql = "SELECT id, value1, value2, value3, reading_time FROM Sensor order by reading_time desc limit 40";

In my case, I can visit this link to view the chart! Why? Well, because I already have sensor readings from the previous project! So, this time I can instantly view it! But, don’t worry! I will explain it again below, so feel free to scroll down! (Yours should be http://example.com/ESP-CHART.php)

BMP280 Temperature (Celcius)
BMP280 Approximate Altitude (m)
BMP280 Pressure (Pa)

Components and Parts Required

  • ESP32 board
  • BMP280 sensor
  • Jumper wires (I’m using male to male jumper wires)
  • Breadboard

Schematics Diagram

Pretty much the same as the previous project.

My Schematic Diagram

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

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 the BMP280 sensor in fritzing, that’s why I used BMP180 instead of BMP280)

ESP32 Code

Still pretty much the same as the previous project! You may copy the following code to upload it with Arduino IDE to your ESP32.

/*
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 = "Home";
#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);
}

Don’t forget 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";

If your code succeeds, you should be able to see this in your serial monitor in Arduino IDE.

Serial Monitor in Arduino IDE

Result?

BMP280 Temperature (Celcius)
BMP280 Approximate Altitude (m)
BMP280 Pressure (Pa)

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

Personally, I want to change the measurement of pressure, so that instead of in Pa, I want to measure it in hPa. So, I need to make a change in ESP_chart.php, like this:

var chartH = new Highcharts.Chart({
chart:{ renderTo:'chart-pressure' },
title: { text: 'BMP280 Pressure' },
series: [{
showInLegend: false,
data: value2
}],
plotOptions: {
line: { animation: false,
dataLabels: { enabled: true }
}
},
xAxis: {
type: 'datetime',
//dateTimeLabelFormats: { second: '%H:%M:%S' },
categories: reading_time
},
yAxis: {
title: { text: 'Pressure (hPa)' }
},
credits: { enabled: false }
});

Also, I need to make a change in the ESP32 code, like this:

// Prepare your HTTP POST request data
String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&value1=" + String(bmp.readTemperature())
+ "&value2=" + String(bmp.readPressure()/100.0F) + "&value3=" + String((bmp.readAltitude(SEALEVELPRESSURE_HPA))) + "";

Next, I need to delete all previous data in the SensorData table.

Result?

Fortunately, it seems that I did it! The result that can be seen in this link is:

BMP280 Temperature (Celcius)
BMP280 Approximate Altitude (m)
BMP280 Pressure (hPa)

Code Explanation

Pretty much the same as the previous project, but instead of displaying it in the table, this time we display it in the graph (ESP_CHART.php instead of DATA.php). For further understanding, you may want to check my previous project.

I would like to point out again that in the loop(), you make the HTTP POST request every 30 seconds (because we put a 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()/100.0F) + "&value3=" + String((bmp.readAltitude(SEALEVELPRESSURE_HPA))) + "";
// Send HTTP POST request
int httpResponseCode = http.POST(httpRequestData);

If you want, you may comment on 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

Finally, we are done. In this project, you have learned how to publish sensor data into a database in your own server domain that you can access from anywhere in the world as the graph. Personally, I used 000webhost because it’s free. If you are interested in this project, after understanding this project, you should be able to change the web page appearance, publish different sensor readings, publish from multiple ESP boards, etc.

To be honest, this is my last embedded system project that will be published in Medium. Actually, I still have 1 more project which will be my final project, but I don’t think I will publish it in Medium because I will do it in a team of 3 persons and we will make it in a document, not an article. I hope that all of my embedded system projects can bring you guys benefits! Keep learning and don’t forget to have fun!

See you later!