Build a cloud weather station dashboard to visualize your ESP32 or ESP8266 sensor readings from anywhere in the world. You’ll visualize your sensor data displayed on gauges and on a table. The ESP32 or ESP8266 will make an HTTP POST request to a PHP script to insert your data into a MySQL database.
Updated on 27 March 2023
Previously, we’ve stored sensor readings in a database and displayed them on a table or charts that you can access from anywhere using your own server. Now, I’ve decided to take a few steps further and add some more information to the web page.
I’ve added two gauges to display the latest temperature and humidity readings as well as some statistics about the minimum, maximum and average readings from a number of readings that you can define. You can also visualize all the latest readings on a table and you can select how many readings you want to show.
To build 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 and display it on a web page
- MySQL database to store readings
Table of Contents
This project is divided into the following main sections:
- Hosting Your PHP Application and MySQL Database
- Preparing Your MySQL Database
- PHP Script HTTP POST – Receive and Insert Data in MySQL Database
- PHP Script for Database Functions
- PHP Script – Display Database Readings on Gauges and Table
- Setting Up the ESP32 or ESP8266
Watch the Video Demonstration
To see how the project works, you can watch the following video demonstration:
0. Download Source Code
For this project, you’ll need these files:
- SQL query to create your table: SensorData_Table.sql
- Insert and access database readings: esp-database.php
- Handle HTTP Post requests: esp-post-data.php
- CSS file to style your web page: esp-style.css
- Display your sensor readings: esp-weather-station.php
- Arduino Sketch for ESP32: HTTPS_ESP32_Cloud_Weather_Station.ino
- Arduino Sketch for ESP8266 : HTTPS_ESP8266_Cloud_Weather_Station.ino
- If your server doesn’t support HTTPS, use this Arduino Sketch (compatible with the ESP32 and ESP8266: ESP_HTTP_POST_MySQL.ino
- Download all projects files
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 how the project works:
- You have an ESP32 or ESP8266 that sends sensor readings to your own server. For this, you have your board connected to your router;
- In your server, there’s a PHP script that allows you to store your readings in a MySQL database;
- Then, another PHP script will display the web page with the gauges, table, and all the other information;
- Finally, you can visualize the readings from anywhere in the world by accessing your own domain name.
Hosting Services
I recommend using one of the following hosting services that can handle all the project requirements:
- Bluehost (user-friendly with cPanel): 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 signing up for Bluehost.
Get Hosting and Domain Name with Bluehost »
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 to your domain name (https://example.com) and see your ESP readings.
If you like our projects, you might consider signing up for one of the recommended hosting services, because you’ll be supporting our work.
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 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
)
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 SensorData in the example_esp_data database as shown in the figure below:
3. PHP Script HTTP POST – Receive and Insert Data in MySQL Database
In this section, we’re going to create a PHP script that is responsible for receiving incoming requests from the ESP32 or ESP8266 and inserting 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: esp-post-data.php
Edit the newly created file (esp-post-data.php) and copy the following snippet:
<!--
Rui Santos
Complete project details at https://RandomNerdTutorials.com/cloud-weather-station-esp32-esp8266/
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
include_once('esp-database.php');
// Keep this API Key value to be compatible with the ESP code provided in the project page. If you change this value, the ESP 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"]);
$result = insertReading($sensor, $location, $value1, $value2, $value3);
echo $result;
}
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;
}
4. PHP Script for Database Functions
Create a new file in /public_html that is responsible for inserting and accessing data in your database. Name your file: esp-database.php
Copy that PHP script:
<!--
Rui Santos
Complete project details at https://RandomNerdTutorials.com/cloud-weather-station-esp32-esp8266/
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";
function insertReading($sensor, $location, $value1, $value2, $value3) {
global $servername, $username, $password, $dbname;
// 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) {
return "New record created successfully";
}
else {
return "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}
function getAllReadings($limit) {
global $servername, $username, $password, $dbname;
// 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 reading_time desc limit " . $limit;
if ($result = $conn->query($sql)) {
return $result;
}
else {
return false;
}
$conn->close();
}
function getLastReadings() {
global $servername, $username, $password, $dbname;
// 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 reading_time desc limit 1" ;
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
function minReading($limit, $value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT MIN(" . $value . ") AS min_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS min";
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
function maxReading($limit, $value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT MAX(" . $value . ") AS max_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS max";
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
function avgReading($limit, $value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS avg";
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
?>
Before saving the file, you need to modify the $dbname, $username and $password variables with your unique details:
// Your Database name
$dbname = "example_esp_data";
// Your Database user
$username = "example_esp_board";
// Your Database user password
$password = "YOUR_USER_PASSWORD";
After adding the database name, username and password, save the file and continue with this tutorial. If you try to access your domain name in the next URL path, you’ll see the following:
https://example.com/esp-post-data.php
5. PHP Script – Display Database Readings on Gauges and Table
You’ll also need to add a CSS file to style your dashboard, name it: esp-style.css:
Copy that CSS to your file and save it:
/**
Rui Santos
Complete project details at https://RandomNerdTutorials.com/cloud-weather-station-esp32-esp8266/
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.
**/
body {
width: 60%;
margin: auto;
text-align: center;
font-family: Arial;
top: 50%;
left: 50%;
}
@media screen and (max-width: 800px) {
body {
width: 100%;
}
}
table {
margin-left: auto;
margin-right: auto;
}
div {
margin-left: auto;
margin-right: auto;
}
h2 { font-size: 2.5rem; }
.header {
padding: 1rem;
margin: 0 0 2rem 0;
background: #f2f2f2;
}
h1 {
font-size: 2rem;
font-family: Arial, sans-serif;
text-align: center;
text-transform: uppercase;
}
.content {
display: flex;
}
@media screen and (max-width: 500px) /* Mobile */ {
.content {
flex-direction: column;
}
}
.mask {
position: relative;
overflow: hidden;
display: block;
width: 12.5rem;
height: 6.25rem;
margin: 1.25rem;
}
.semi-circle {
position: relative;
display: block;
width: 12.5rem;
height: 6.25rem;
background: linear-gradient(to right, #3498db 0%, #05b027 33%, #f1c40f 70%, #c0392b 100%);
border-radius: 50% 50% 50% 50% / 100% 100% 0% 0%;
}
.semi-circle::before {
content: "";
position: absolute;
bottom: 0;
left: 50%;
z-index: 2;
display: block;
width: 8.75rem;
height: 4.375rem;
margin-left: -4.375rem;
background: #fff;
border-radius: 50% 50% 50% 50% / 100% 100% 0% 0%;
}
.semi-circle--mask {
position: absolute;
top: 0;
left: 0;
width: 12.5rem;
height: 12.5rem;
background: transparent;
transform: rotate(120deg) translate3d(0, 0, 0);
transform-origin: center center;
backface-visibility: hidden;
transition: all 0.3s ease-in-out;
}
.semi-circle--mask::before {
content: "";
position: absolute;
top: 0;
left: 0%;
z-index: 2;
display: block;
width: 12.625rem;
height: 6.375rem;
margin: -1px 0 0 -1px;
background: #f2f2f2;
border-radius: 50% 50% 50% 50% / 100% 100% 0% 0%;
}
.gauge--2 .semi-circle { background: #3498db; }
.gauge--2 .semi-circle--mask { transform: rotate(20deg) translate3d(0, 0, 0); }
#tableReadings { border-collapse: collapse; }
#tableReadings td, #tableReadings th {
border: 1px solid #ddd;
padding: 10px;
}
#tableReadings tr:nth-child(even){ background-color: #f2f2f2; }
#tableReadings tr:hover { background-color: #ddd; }
#tableReadings th {
padding: 10px;
background-color: #2f4468;
color: white;
}
Finally, create another PHP file in the /public_html directory that will display all the database content on a web page. Name your new file: esp-weather-station.php
Edit the newly created file (esp-weather-station.php) and copy the following code:
<!--
Rui Santos
Complete project details at https://RandomNerdTutorials.com/cloud-weather-station-esp32-esp8266/
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
include_once('esp-database.php');
if (isset($_GET["readingsCount"])){
$data = $_GET["readingsCount"];
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
$readings_count = $_GET["readingsCount"];
}
// default readings count set to 20
else {
$readings_count = 20;
}
$last_reading = getLastReadings();
$last_reading_temp = $last_reading["value1"];
$last_reading_humi = $last_reading["value2"];
$last_reading_time = $last_reading["reading_time"];
// Uncomment to set timezone to - 1 hour (you can change 1 to any number)
//$last_reading_time = date("Y-m-d H:i:s", strtotime("$last_reading_time - 1 hours"));
// Uncomment to set timezone to + 7 hours (you can change 7 to any number)
//$last_reading_time = date("Y-m-d H:i:s", strtotime("$last_reading_time + 7 hours"));
$min_temp = minReading($readings_count, 'value1');
$max_temp = maxReading($readings_count, 'value1');
$avg_temp = avgReading($readings_count, 'value1');
$min_humi = minReading($readings_count, 'value2');
$max_humi = maxReading($readings_count, 'value2');
$avg_humi = avgReading($readings_count, 'value2');
?>
<!DOCTYPE html>
<html>
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" type="text/css" href="esp-style.css">
<meta name="viewport" content="width=device-width, initial-scale=1">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</head>
<header class="header">
<h1>📊 ESP Weather Station</h1>
<form method="get">
<input type="number" name="readingsCount" min="1" placeholder="Number of readings (<?php echo $readings_count; ?>)">
<input type="submit" value="UPDATE">
</form>
</header>
<body>
<p>Last reading: <?php echo $last_reading_time; ?></p>
<section class="content">
<div class="box gauge--1">
<h3>TEMPERATURE</h3>
<div class="mask">
<div class="semi-circle"></div>
<div class="semi-circle--mask"></div>
</div>
<p style="font-size: 30px;" id="temp">--</p>
<table cellspacing="5" cellpadding="5">
<tr>
<th colspan="3">Temperature <?php echo $readings_count; ?> readings</th>
</tr>
<tr>
<td>Min</td>
<td>Max</td>
<td>Average</td>
</tr>
<tr>
<td><?php echo $min_temp['min_amount']; ?> °C</td>
<td><?php echo $max_temp['max_amount']; ?> °C</td>
<td><?php echo round($avg_temp['avg_amount'], 2); ?> °C</td>
</tr>
</table>
</div>
<div class="box gauge--2">
<h3>HUMIDITY</h3>
<div class="mask">
<div class="semi-circle"></div>
<div class="semi-circle--mask"></div>
</div>
<p style="font-size: 30px;" id="humi">--</p>
<table cellspacing="5" cellpadding="5">
<tr>
<th colspan="3">Humidity <?php echo $readings_count; ?> readings</th>
</tr>
<tr>
<td>Min</td>
<td>Max</td>
<td>Average</td>
</tr>
<tr>
<td><?php echo $min_humi['min_amount']; ?> %</td>
<td><?php echo $max_humi['max_amount']; ?> %</td>
<td><?php echo round($avg_humi['avg_amount'], 2); ?> %</td>
</tr>
</table>
</div>
</section>
<?php
echo '<h2> View Latest ' . $readings_count . ' Readings</h2>
<table cellspacing="5" cellpadding="5" id="tableReadings">
<tr>
<th>ID</th>
<th>Sensor</th>
<th>Location</th>
<th>Value 1</th>
<th>Value 2</th>
<th>Value 3</th>
<th>Timestamp</th>
</tr>';
$result = getAllReadings($readings_count);
if ($result) {
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 + 7 hours (you can change 7 to any number)
//$row_reading_time = date("Y-m-d H:i:s", strtotime("$row_reading_time + 7 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>';
}
echo '</table>';
$result->free();
}
?>
<script>
var value1 = <?php echo $last_reading_temp; ?>;
var value2 = <?php echo $last_reading_humi; ?>;
setTemperature(value1);
setHumidity(value2);
function setTemperature(curVal){
//set range for Temperature in Celsius -5 Celsius to 38 Celsius
var minTemp = -5.0;
var maxTemp = 38.0;
//set range for Temperature in Fahrenheit 23 Fahrenheit to 100 Fahrenheit
//var minTemp = 23;
//var maxTemp = 100;
var newVal = scaleValue(curVal, [minTemp, maxTemp], [0, 180]);
$('.gauge--1 .semi-circle--mask').attr({
style: '-webkit-transform: rotate(' + newVal + 'deg);' +
'-moz-transform: rotate(' + newVal + 'deg);' +
'transform: rotate(' + newVal + 'deg);'
});
$("#temp").text(curVal + ' ºC');
}
function setHumidity(curVal){
//set range for Humidity percentage 0 % to 100 %
var minHumi = 0;
var maxHumi = 100;
var newVal = scaleValue(curVal, [minHumi, maxHumi], [0, 180]);
$('.gauge--2 .semi-circle--mask').attr({
style: '-webkit-transform: rotate(' + newVal + 'deg);' +
'-moz-transform: rotate(' + newVal + 'deg);' +
'transform: rotate(' + newVal + 'deg);'
});
$("#humi").text(curVal + ' %');
}
function scaleValue(value, from, to) {
var scale = (to[1] - to[0]) / (from[1] - from[0]);
var capped = Math.min(from[1], Math.max(from[0], value)) - from[0];
return ~~(capped * scale + to[0]);
}
</script>
</body>
</html>
If you try to access your domain name in the following URL path, you’ll see the following:
https://example.com/esp-weather-station.php
That’s it! If you see that web page with empty values in your browser, it means that everything is ready. In the next section, you’ll learn how to insert data from your ESP32 or ESP8266 into the database.
6. Setting Up the ESP32 or ESP8266
This project is compatible with both the ESP32 and ESP8266 boards. You just need to assemble a simple circuit and upload the sketch provided to insert temperature, humidity, pressure, and more into your database every 10 minutes. The sketch is slightly different for each board.
Parts Required
For this example, we’ll get sensor readings from the BME280 sensor. Here’s a list of parts you need to build the circuit for this project:
- ESP32 board (read Best ESP32 dev boards)
- Alternative – ESP8266 board (read Best ESP8266 dev boards)
- BME280 sensor
- Jumper wires
- Breadboard
You can use the preceding links or go directly to MakerAdvisor.com/tools to find all the parts for your projects at the best price!
Schematics
The BME280 sensor module we’re using communicates via I2C communication protocol, so you need to connect it to the ESP32 or ESP8266 I2C pins.
BME280 wiring to ESP32
The ESP32 I2C pins are:
- GPIO 22: SCL (SCK)
- GPIO 21: SDA (SDI)
So, assemble your circuit as shown in the next schematic diagram (Guide for ESP32 with BME280 and ESP32 BME280 Web Server).
Recommended reading: ESP32 Pinout Reference Guide
BME280 wiring to ESP8266
The ESP8266 I2C pins are:
- GPIO 5 (D1): SCL (SCK)
- GPIO 4 (D2): SDA (SDI)
Assemble your circuit as in the next schematic diagram if you’re using an ESP8266 board (read Guide for ESP8266 with BME280).
Recommended reading: ESP8266 Pinout Reference Guide
Installing Libraries
We’ll program the ESP32/ESP8266 using Arduino IDE, so you must have the ESP add-on installed in your Arduino IDE.
Follow one of the next tutorials depending on the board you’re using:
- Install the ESP32 Board in Arduino IDE – you also need to install the BME280 Library and Adafruit_Sensor library
- Install the ESP8266 Board in Arduino IDE – you also need to install the BME280 Library and Adafruit_Sensor library
ESP32 Code
Follow this section if you’re using an ESP32. For an ESP8266 click here.
After installing the necessary board add-ons, copy the following code to your Arduino IDE, but don’t upload it yet. You need to make some changes to make it work for you.
/*
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_BME280.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/esp-post-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 = "BME280";
String sensorLocation = "Office";
/*#include <SPI.h>
#define BME_SCK 18
#define BME_MISO 19
#define BME_MOSI 23
#define BME_CS 5*/
#define SEALEVELPRESSURE_HPA (1013.25)
Adafruit_BME280 bme; // I2C
//Adafruit_BME280 bme(BME_CS); // hardware SPI
//Adafruit_BME280 bme(BME_CS, BME_MOSI, BME_MISO, BME_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 = bme.begin(0x76);
if (!status) {
Serial.println("Could not find a valid BME280 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 + "&value1=" + String(bme.readTemperature())
+ "&value2=" + String(bme.readHumidity()) + "&value3=" + String(bme.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&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);
}
Setting your network credentials
You need to modify the following lines with your network credentials: SSID and password. The code is well commented on where you should make the changes.
// Replace with your network credentials
const char* ssid = "REPLACE_WITH_YOUR_SSID";
const char* password = "REPLACE_WITH_YOUR_PASSWORD";
Setting your serverName
You also need to type your domain name, so the ESP publishes the readings to your own server.
const char* serverName = "https://example.com/esp-post-data.php";
Now, you can upload the code to your board.
Note: Most servers require you to make HTTPS requests. The code above makes HTTPS requests to be compliant with the requirements of most cloud servers nowadays.
Your server doesn’t support HTTPS? Use this code instead.
How the code works
This project is already quite long, so we won’t cover in detail how the code works, but here’s a quick summary:
- Import all the libraries to make it work;
- Set variables that you might want to change (apiKeyValue, sensorName, sensorLocation);
- The apiKeyValue is just a random string that you can modify. It’s used for security reasons, so only anyone that knows your API key can publish data to your database;
- Initialize the serial communication for debugging purposes;
- Establish a Wi-Fi connection with your router;
- Initialize the BME280 to get readings;
- Initialize a secure WiFi client.
Then, in the loop() is where you actually make the HTTP POST request every 10 minutes with the latest BME280 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(bme.readTemperature()) + "&value2=" + String(bme.readHumidity()) + "&value3=" + String(bme.readPressure()/100.0F) + "";
int httpResponseCode = http.POST(httpRequestData);
You can comment the httpRequestData variable above that concatenates all the BME280 readings and use the httpRequestData variable below for testing purposes:
String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BME280&location=Office&value1=24.75&value2=49.54&value3=1005.14";
Learn more about HTTPS Requests with the ESP32: ESP32 HTTPS Requests (Arduino IDE).
ESP8266 Code
Follow this section if you’re using an ESP8266. For an ESP32 check the section above.
After installing the necessary board add-ons and libraries, copy the following code to your Arduino IDE, but don’t upload it yet. You need to make some changes to make it work for you.
/*
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 <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>
#include <WiFiClientSecureBearSSL.h>
#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.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/esp-post-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 = "BME280";
String sensorLocation = "Office";
/*#include <SPI.h>
#define BME_SCK 18
#define BME_MISO 19
#define BME_MOSI 23
#define BME_CS 5*/
#define SEALEVELPRESSURE_HPA (1013.25)
Adafruit_BME280 bme; // I2C
//Adafruit_BME280 bme(BME_CS); // hardware SPI
//Adafruit_BME280 bme(BME_CS, BME_MOSI, BME_MISO, BME_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 = bme.begin(0x76);
if (!status) {
Serial.println("Could not find a valid BME280 sensor, check wiring or change I2C address!");
while (1);
}
}
void loop() {
//Check WiFi connection status
if(WiFi.status()== WL_CONNECTED){
std::unique_ptr<BearSSL::WiFiClientSecure>client(new BearSSL::WiFiClientSecure);
// Ignore SSL certificate validation
client->setInsecure();
//create an HTTPClient instance
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 + "&value1=" + String(bme.readTemperature())
+ "&value2=" + String(bme.readHumidity()) + "&value3=" + String(bme.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&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
//http.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:
//http.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);
}
Setting your network credentials
You need to modify the following lines with your network credentials: SSID and password. The code is well commented on where you should make the changes.
// Replace with your network credentials
const char* ssid = "REPLACE_WITH_YOUR_SSID";
const char* password = "REPLACE_WITH_YOUR_PASSWORD";
Setting your serverName
You also need to type your domain name, so the ESP publishes the readings to your own server.
const char* serverName = "https://example.com/esp-post-data.php";
Now, you can upload the code to your board.
Note: Most servers require you to make HTTPS requests. The code above makes HTTPS requests to be compliant with the requirements of most cloud servers nowadays.
Your server doesn’t support HTTPS? Use this code instead.
Learn more about HTTPS Requests with the ESP8266: ESP8266 NodeMCU HTTPS Requests (Arduino IDE).
Demonstration
After completing all the steps, let your ESP board collect some readings and publish them to your server.
If everything is correct, this is what you should see in your Arduino IDE Serial Monitor:
If you open your domain name in this URL path:
https://example.com/esp-weather-station.php
You should see the latest 20 readings stored in your database. There are two gauges that show the latest temperature and humidity readings, and a timestamp.
Refresh the web page to see the latest readings:
There’s a field where you can type the number of readings to visualize, as well as the number of readings for these statistics: minimum, maximum, and average. By default, it’s set to 20. For example, if you type 30 and press the update button, you’ll see that your web page updates and recalculates all the values.
The web page is also mobile responsive, so you can use any device to access it:
You can also go to phpMyAdmin to manage the data stored in your SensorData table. You can delete it, edit, etc…
Wrapping Up
In this tutorial you’ve learned how to publish sensor data into a database in your own server domain that you can access from anywhere in the world. This requires that you have your own server and domain name (alternatively, you can use a Raspberry Pi LAMP Server for local access).
I encourage you to change the web page appearance, add more features (like email notifications), publish data from different sensors, use multiple ESP boards, and much more.
You might also like reading:
- ESP32/ESP8266 Send Email Notification using PHP Script
- Visualize Your Sensor Readings from Anywhere in the World (ESP32/ESP8266 + MySQL + PHP) using Charts
- ESP32 Relay Module Web Server
Learn more about the ESP32 and ESP8266 with our resources:
- Learn ESP32 with Arduino IDE
- Home Automation using ESP8266
- Build Web Servers with ESP32 and ESP8266
- Firebase Web App with ESP32 and ESP8266
- SMART HOME with Raspberry Pi, ESP32, and ESP8266
- Free ESP32 Projects and Tutorials…
- Free ESP8266 Projects and Tutorials…
Thank you for reading.
Excellent project. It integrates aspects of sensors, data storage and web display into a comprehensive package with server based ‘management’. I’ll likely use a LAMP server on one of my Raspberry Pis. Thanks Rui.
Thanks for your comment 😀
Regards,
Sara
I did it this way, but the data does not fall into the database. And an error is coming.
Error code: -1
Can you give me a code?
When we enter a code with a sample value, it is loaded into the database.
Thank You….
Hi Sara, As a newbie to microcontrollers, I may have bitten off more than I can chew! I tested your Weather Station project successfully, then modified the ESP8266 code to suit a DS18B20 temperature sensor (as I needed a pool water temperature read out for our local swimming pool). I know the code works OK after viewing the Serial Monitor, but I can’t get any data flowing through to the database. ANy ideas on what I need to look for? Your advice would be appreciated.
Cheers Ken
What about to use AWS Lambda? With this frequency of posting data you can use free tier only 😉
Thanks for this post. I always imagine how cayenne and many more website work. I learn a lot of things with this post. I use 000webhost for SQL PHP and HTML file hosting. How I use an NTC thermistor with this code at a place of adafruit bme280
Keep uploading these Posts
I don’t have those exact instructions, but you can simply replace the section that publishes the readings to send the thermistor values
How difficult is it to modify for BME680?
You just need to import the right library for that sensor and send those readings instead
Amazing project…. A great improvement over the earlier one – visually.
However, can you please check the the semicircle graphs once again both for Temp and humidity. There seems to be an error in how the reading shows up graphically… I tried by changing the temp range to 0-45*C but does not help much.
Thanks for letting me know, my CSS had an error (I wasn’t assigning the gauges to update them properly).
The post was updated and you can use this new HTML and JavaScript code: https://github.com/RuiSantosdotme/Cloud-Weather-Station-ESP32-ESP8266/blob/master/code/esp-weather-station.php
(I’ve updated lines: 62, 167, 181)
Thank you very much. Its done.
May I point out another issue I have noticed…
sometimes when you consider more than 20 readings say 40 – the max temp is often less than min. temp…
19.48 ºC
Temperature 50 readings
Min Max Average
10.01 °C 9.85 °C 14.2 °C
I’m glad that worked for the gauges! How many readings do you have in your database?
12 readings/hour x 24 x5 days or so.. ~ 1000 readings or so
I just encountered a similar situation. The problem with the minimum, maximum and average values comes from the type of variable used for the database. The Temperature (value1), Humidity (value2) and Barometric Pressure (value3) fields are defined as strings (VARCHAR) when the table is created. To get the right values, these three fields must be defined as follows: value1 DECIMAL(5,2), value2 DECIMAL(5,2), value3 DECIMAL(6,2).
Many thanks, for your excellent project. You need a lot of time to build it. It’s really beautiful.
Thank you 😀
Wow! The first project on ESP8266, which I repeated without any problems! Thank you very much!
Thank you sara for this Awesome project.
I am done with all parts with esp32
but not done with esp8266 it shows the error code -1 as an Http response.
Are your values still being published? Is your BME280 getting the readings?
Same for me, no data been published.
Hey, do you find a solution ?
Hi,
Thanks for this project.
I have it running on an ESP8266 and have modified it slightly to read values from a DHT22 and a capacitive soil moisture sensor.
So have three gauges showing temp, humidity and Soil moisture level.
Can you show how to add a slider and a button to the to the web page so that when the soil moisture level drops down to a certain % ( as set by the slider) the ESP will turn on a small water pump. Or if desired the button can be used to turn the pump on manually
Hello John, it’s definitely possible to implement that feature, however at the moment I don’t have any tutorials on that exact subject. In the future, I’ll try to build a project where you set a threshold input like that.
Regards,
Rui
Hello John. My name is Gabriel, I am new to programming and I am studying to do a project very similar to yours, using the DHT22 and soil moisture sensor. Would you agree to share your project with me so I could have an idea on the structure you used to implement different sensors with this code? I would be very kind of yours and I would very much appreciate it!
My email is [email protected], if you agree to help me.
Thanks in advance, and cheers from Brazil!
Thank you Rui and Sarah for this project,
I modified the code to get readings from DHT11 sensor via arduino ethernet shield and added email notifications. Every seem to work just fine. Will be looking forward to more of DIY’s.
That’s great!
We’ll be posting a new tutorial/project every week, so stay tuned!
Regards,
Sara
is it possible to do this without using a domain? So to have the SQL at home in the privat network?
Hi.
You can use it using a Raspberry Pi.
See these tutorials:
– https://randomnerdtutorials.com/raspberry-pi-apache-mysql-php-lamp-server/
– https://randomnerdtutorials.com/esp32-esp8266-raspberry-pi-lamp-server/
I hope this helps.
Regards,
Sara
Thank you. It works :-D. The next step ist to set up a sleep mode and use a Battery to supply it outside 🙂
It’s a great idea….
hello, a query: the update of the page is automatic, after receiving each reading or requires a manual refresh?
Thanks, this was very well done, explained and presented. Save me a lot of time. Thanks, again.
Great! 😀
I have this problem when I load the code and display it on the console
Connected to WiFi network with IP Address: 192.168.0.19
Could not find a valid BME280 sensor, check wiring or change I2C address!
Soft WDT reset
>>>stack>>>
ctx: cont
sp: 3ffffde0 end: 3fffffc0 offset: 01b0
3fffff90: 40208ba8 1300a8c0 feefeffe feefeffe
3fffffa0: feefeffe 00000000 3ffee78c 402062dc
3fffffb0: feefeffe feefeffe 3ffe8500 40100f41
<<<stack<<<
ets Jan 8 2013,rst cause:2, boot mode:(3,7)
load 0x4010f000, len 1392, room 16
tail 0
chksum 0xd0
csum 0xd0
v3d128e5c
~ld
sorry these problems are the connector has no contact with the sensor, sorry problems resolved.
Hi.
The problem can be the wiring or the BME280 I2C address.
Run this sketch to see if the I2C address is the same used in the code:https://raw.githubusercontent.com/RuiSantosdotme/Random-Nerd-Tutorials/master/Projects/LCD_I2C/I2C_Scanner.ino
Regards,
Sara
Awesome tutorial. I have a question though: What would be the best way to manage readings from more than 10 ESP32? Would it be better to create an individual database for each one or add a DEVICEID variable which gets filtered when creating the graphs?
It would be better to have 2 Tables:
#1 Table with all your board and #2 Table with all the sensor readings. Each reading would be related to a board using a foreign key to Table #1.
That’s the correct method of building that type of application.
However, if you’re not familiar with SQL, you have to easier options:
1) To create 1 Table for each board (creating 10 tables).
2) Or use a single Table like in this project that each reading also has the board information… Then, you would use an SQL query with a filter to find all the readings for each board
I hope that helps!
thank-you
i have added the air pressure gauge in as well, the fact that it is building a history db meaning i can run all sorts of queries and graphs against them is really cool. all of this is a big step up from what i knew on this subject yesterday
thank-you
That’s great! 😀
hi the data is going to fill the sql db up pretty quick, if i wanted readings posted to the db every 20 minutes would i just make the below change?
// the following variables are unsigned longs because the time, measured in
// milliseconds, will quickly become a bigger number than can be stored in an int.
unsigned long lastTime = 0;
// Timer set to 20 minutes (1200000)
unsigned long timerDelay = 1200000;
// Set timer to 30 seconds (30000)
//unsigned long timerDelay = 30000;
Hi thank you so much for the wonderful tutorial. Im using esp32, so do I need to use esp8266WiFi.h library or only WiFi.h library is enough ?
Is it possible to send the data to firebase ? Do you have any tutorials for it?
Hi.
If you’re using the ESP32, you just need the WiFi.h library.
We don’t have any tutorials about firebase.
Regards,
Sara
Hi – I’ve built the solar-powered weather station and am sending data to a database which contains temp, humidity, rainfall, battery level, day, hour, minute for each upload. I have some experience in programming but not in PHP and am now trying to understand the PHP code for the web gauges so that I can query my database and post rainfall for the last 24 hours along with the temp and humidity gauges. My problem: I don’t understand how this ‘$last_reading_temp = $last_reading[“temperature”];’ and the following commands work. Is the ‘$last_reading[]’ a function? And, if so, where is it defined? I’ve searched the PHP online manuals and do not see this function defined. Any help would be appreciated.
Hi, can you share what hardware are you using? I’m looking to use solar energy to power the weather station as well.
Excellent project.
I just noticed with my build that it seems to stop posting data from time to time.
The project is solar powered and uses deep sleep as per the other tutorial.
Any advice on how to trouble shoot?
Hi Matt.
Maybe it is not able to connect to the internet when it wakes up?
Does it happen very often?
Regards,
Sara
Hi Sara,
yes it happens quite frequently. I get gaps of up to 8 hours, the device is supposed to wake up every hour.
I implemented a restart routine in case it is unable to connect to wifi after 15 seconds as i noticed that it seems to fail from time to time.
void setup() {
pinMode(SENSOR, OUTPUT);
Serial.begin(115200);
digitalWrite(SENSOR, HIGH);
Serial.println(“Powered GPIO2”);
delay(3000);
WiFi.begin(ssid, password);
Serial.println(“Connecting”);
while(WiFi.status() != WL_CONNECTED) {
delay(1000);
Serial.print(“.”);
if (millis() >= 15000) {
esp_restart();
}
}
Everytime i tried with the usb console connected it solved tha “can’t connect issue”. Now i am at a bit of a loss here on why it won’t work.
I noticed quite a large power drop on the battery right after the problem occured. If my last measurement said 93% battery, 8 hours no readings, the next will say 83% battery.
Quick update:
After making my own pcb for the solar /battery power supply (including battery charge monitoring) in accordance with the tutorial from randomnerds and modifying the weather station pcb from randomnerds (changed the ldr gpio so it wont habe issues with the wifi library and added some more gpio breakouts ) it woors flawlessly. I assume that i had a bad solder joint on one of the cables causing the station to sometimes get a reading and sometimes not
Hi, can you share with us which hardware are you using to power the entire thing with solar energy? I appreciate if you could share any codes / tutorials so we can replicate it. Thanks!
I’m getting error in below step.
while I try to access my domain name in the following URL path.
http://esp8266sensorsdisplay.hstn.me/esp-weather-station.php
Error: Connection failed: No such file or directory
It means your connection to your database file did not work.
Check your config in esp-database.php again and make sure that the
$servername = “localhost”;
// Your Database name
$dbname = “example_esp_data”;
// Your Database user
$username = “example_esp_board”;
// Your Database user password
$password = “YOUR_USER_PASSWORD”;
part is actually correct and that it is possible to connect with them to the db.
I had to use an ip address instead of localhost. So doublecheck those values.
could you explain it better i have the same problem, which ip address did you use?
You need to use the IP address of your database server. Depending on your hosting your database server might not be the same as the one hosting your website.
I can’t give you mine as that won’t work for you 😉
Thank you for the really detailed tutorial. This was the first time I have done anything with the esp32, your tutorial was really easy to follow.
That’s great!
Thanks for following our work.
Regards,
Sara
Hi, i would connect OLED SSD1306 I2C display..
But PIN 21 or 22 is used for BME280 and display.
How i can change pin 21 and 22 for BME280?
Hi.
You can connect both on the same pins.
See this tutorial about I2C communication: https://randomnerdtutorials.com/esp32-i2c-communication-arduino-ide/
Regards,
Sara
How i can make iy with static IP?
You can read these guides to make the ESP32 static IP Address: https://randomnerdtutorials.com/esp32-static-fixed-ip-address-arduino-ide/
Is anyone else having issues with the bme280 only reading 100% humidity after a few months outside in the weatherstation?
Hello Rui, I really liked your work, but I wanted to adapt it to use the dht 22 sensor. How could I help?
Hi.
You can take a look at our getting started tutorial with the DHT22.
https://randomnerdtutorials.com/esp32-mqtt-publish-dht11-dht22-arduino/
Regards,
Sara
Dear Sara and Rui,
I like your project so much.
If possible, could you change your code for DHT11?
I’m newbie and I havn’t got BME sensor
Regards,
Koko
Hi.
Please follow our DHT11 guide and you’ll be able to modify this code yourself: https://randomnerdtutorials.com/esp32-dht11-dht22-temperature-humidity-sensor-arduino-ide/
Regards,
Sara
Thank you very much Sara.
I will try.
Regards,
Koko
how do we auto delete database information that is older than say 3-6 months.
limited storage building quickly with frequency of readings
Thanks
You need to add a field to the SensorData table to hold the timedate stamp of the temp data record when it is inserted in to the table. Something like this:
Column Name = “reading_data”
Data Type = timestamp
Default Value = CURRENT_TIMESTAMP
Depending on how you are maintaining the table, set Not Null to true and set On Update to true. The On Update = true will cause the the database to automatically insert the current date and time into the field. You DO NOT need to change the insertReading code at all. The database will handle the field for you. Every record now will have the timedate stamp of when the reading record was created.
No to purge the SensorData of old records, you have to add an sql to the insertReading code that will delete the old records. In the esp-database.php file, locate this code:
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
and add this code AFTER the above and BEFORE the line:
$sql = “INSERT INTO SensorData (sensor, location, value1, value2, value3) VALUES (‘” . $sensor . “‘, ‘” . $location . “‘, ‘” . ($value1 – 2.80) . “‘, ‘” . $value2 . “‘, ‘” . ($value3 + 21.18) . “‘)”;
$sql = “DELETE from SensorData WHERE reading_time <= ‘” . date(“Y-m-d 00:00:00”,strtotime(“-60 days”));
$conn->query($sql);
Your final version should look like this;
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
$sql = “DELETE from SensorData WHERE reading_time <= ‘” . date(“Y-m-d 00:00:00”,strtotime(“-60 days”));
$conn->query($sql);
$sql = “INSERT INTO SensorData (sensor, location, value1, value2, value3) VALUES (‘” . $sensor . “‘, ‘” . $location . “‘, ‘” . ($value1 – 2.80) . “‘, ‘” . $value2 . “‘, ‘” . ($value3 + 21.18) . “‘)”;
Before the new reading is inserted into the table, the program will delete any records in the past older than 60 days. You can, of course, change the 60 to any number of days that you want.
Enjoy.
Thank you for the effort to respond
A big help.
Old entries can be deleted like this
$sql = “DELETE FROM SensorData WHERE reading_time <= now() – interval 60 day”;
$conn->query($sql);
I’d love to see how this can be used with SQL server AND a local site. Not always necessary to make it for a website 🙂
Hi.
Maybe this will be useful: https://randomnerdtutorials.com/raspberry-pi-apache-mysql-php-lamp-server/
Regards,
Sara
Hi, I was trying to get this version of your code working with XAMPP and its own MySQL on a local machine (changing the IP to my local machine, opening the ports in the firewall etc) . It returns 200 and merrily carries on, alas, nothing is entered into the db.
Hello. Thank you for the project. In this project, I want to read a value from the database with ESP8266. How can I do that.
What does
Error code: -1 mean?
I’m using MAMP as my web server.
http://localhost/esp-weather-station.php loads fine.
But when I upload to my ESP32 it connects to my internet but I the i get
httpRequestData: api_key=tPmAT5Ab3j7F9&sensor=sensorr&location=Office&value1=35&value2=80&value3=0.40
Error code: -1
Hey, i have encountered same problem. Did you find any solution for that issue ? It would help me alot thanks.
I have overcome the Error Code -1 question, the key point is in here:
void loop() {
//Check WiFi connection status
if(WiFi.status()== WL_CONNECTED){
WiFiClient client;
HTTPClient http;
otherwise, correct all https.xxx to http.xxx, like https.begin to http.begin
Good Luck!!
Hello, simply a great project, thanks for sharing…. I’m having a problem with the semicircle graphs ,both the Temp and humidity are not working, everything else is working fine. Would you have any idea why this is happening?
Hi Sara and Rui,
thanks for the good projects you post.
I am a 74-year-old retired grandfather, thanks to your indications I made the weather station, everything worked immediately to my full satisfaction and admiration for your work.
My SQL DataBase runs on a QNAP TS-251 NAS
thanks a lot
Silvano
Awesome Tutorial thanks guys.
It works great but I want it to auto update the data a bit like this tutorial does https://randomnerdtutorials.com/esp32-plot-readings-charts-multiple/.
Currently I have to manually refresh the data and I prefer to be able to get it to auto refresh. How do I do that or what code do I need. I’m a bit of a newbie
Hello Rui and Sara,
I have installed the project “cloud-weather-station-esp32-esp8266” according to the instructions and updated it with my data. When starting “esp-weather-station.php” the following errors occur (Apache server is started):
Warning: Undefined array key “readingsCount” in C:\xampp\htdocs\bme280_esp\esp-weather-station.php on line 13
Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\bme280_esp\esp-weather-station.php on line 26
Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\bme280_esp\esp-weather-station.php on line 27
Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\bme280_esp\esp-weather-station.php on line 28
Thank you for your help
Greetings Erwin
how can I test with out an esp32? I thought I could just type the http path in to a web browser?
http://localhost/esp-post-data.php?api_key=tPmAT5Ab3j7F9&sensor=BME280&location=Office&value1=24.75&value2=49.54&value3=1005.14
doesn’t seem to work?
Server is working great but when some data displayed under the gauges has a 1 (in leading digit) or 100, it is displaying as a Min automatically. How do I correct this?
hello. I checked carefully. The esp-weather-station.php good for your design. But the database in phpmyadmin is not full. My IDE response: HTTP response code 301. How to fix it
I have the same problem,
HTTP Response code: 301
I had the same and just changed the server url in http://www……..
So just enter www. after http://
thanks you! But i changed http://www…. The Response is: Error code: -1
So,
I have been running this station for quite a while now.
From time to time it just dies and won’t do anything until I reset it.
Any idea what might be the cause for that or how to work around such a behaviour?
It is kind of annoying to walk out into the garden every other month to deconstruct the weather station just to reset the esp ^^
I’ve occasionally had similar problems and I’ve tried debugging to figure out what was causing the hang. Alas, I never did figure out the issue and I finally gave in a put in a timer routine that would reset the app (mine is running on an ESP8266) using the ESP.reset(); command.
The idea is to grab a start time (using millis();) when the app starts and periodically check to see how long the app has been running by subtracting the start time from the current time (again, using miilis();) . In my case, when the difference reaches 8 hours, the app resets itself. Obviously, the app has to be running so the time period has to be short enough so that the reset timeout is before the time when the app would hang.
This approach is also quite useful if you have issues with wifi connections being dropped or router hangs. Just use a different test to trigger the reset command.
Hi, thanks for the project I think its great,
but no work!
HTTP Response code: 301
Help please
Just add www. after http:// in :const char* serverName = “http://example.com/esp-post-data.php”; so it becomes: “http://www.example.com/esp-post-data.php.
301 it’s not related to any type of errors, just script don’t know how to communicate with server set to redirect htpp vs https. We never found solution for this and webserver is not sending data to MySQL db. It’s more related that this project can’t work with PHP after version 5. And they never provided url that can help us to add data to database directly from browser using authorized token.
Adding www. after http:// worked fine with me in PHP8 on my webhosting.
I have also this problem, http response 301.
I use php 8.
I solved this by changed this part of code:
// Your Domain name with URL path or IP address with path
http.begin(client, serverName);
To
http.begin(serverName);
http response then change to 200
I dont why in full code, they use http.begin(client, serverName);
but in explanation they use http.begin(serverName);
Hello. Thank you for the project. In this project, I want to read a value from the database with ESP8266. How can I do that.
Finally got it working!
Thanks.
Tips:
1. Follow this esp32 code exactly [when it comes to inputting your domain and others… even using “http” and not “https” can cause the database not to be updated!!!!]
Check the files and make sure your are calling the other files by their correct name. I remember the css file was not styling my work simply because in my main weather station folder, I called it sp.css not esp.css….things like that.
Happy coding 👨🏾💻
This is a marvelous project. Full of interesting ideas.
There is one thing I am wondering about. There is a lot of code involved with updating the web page with push data. For one thing, I don’t understand the idea of “e”. If seems to be a function name but there is no function “e”. I have lots to learn on this, surely!
The other is why you would want to do all of that anyway when one, simple statement will pull the data from the browser:
…
On this page, there is so little heading information that it is trivial to send it again. And that removes all of the overhead of sending it again. I commented out the entire script and the entire updateWeb routines and replaced all of that with that one line. SO much simpler and not a noticeable change with updating the page. I might use this code if there is a huge page with lots of pictures (but, even then, they are cached so no data transmission on subsequent updates). So I don’t understand the need. Yes, I have coded exotic routines after I learned about them, just to see them work but this is way overkill.
Sorry, something got deleted while we were cleaning the room in the middle of my post. The replacement line for all of the event stuff and the updateWeb routine.
5 Minute AQ
…
Maybe this time:
< meta http-equiv=”refresh” content=”300″ >
Hello Sara, Riu and others. I follow your website with interest. First of all, I would like to congratulate you for your sharing.
I have successfully implemented this project with Esp8266. and I send data to database.
But then I added the webserver(littlefs) part to my project. After adding the webserver part, the server started to return me an error -1 code. I also couldn’t get the date-time information from the ntp server.
I tried many times but it didn’t work. Your help in this matter is really important to me. I will be glad if you help me.
Hi.
I’m sorry, but I didn’t understand what is exactly the issue.
Can you be more precise?
Regards,
Sara
Hello Sara.
1.part) The device I built sends the BME680 method to the mysql database on the remote server.(no problem)
2.part) I also want to read sensor data in index.html in ESP8266’s webserver. (I did this)
3) When I combine these two parts, I see the data in the index.html page on the webserver of esp8266. So this part works fine.
But when I add the 2nd part to the 1st part, the process of sending data to mysql in the 1st step does not work. Mysql server returns “Error Code:-1” response.
How do you think I can solve this problem?
Thank you very much.
I followed your instruction according to your interesting work entitled “” DIY Cloud Weather Station with ESP32/ESP8266 (MySQL Database and PHP)””.
I’m getting error Code 302
my host and domain service are premium (with charge).
I will appreciate if you tell me How can I fix this 302 error??
Best regards
My response code is 200 but data not insert to my database
Hello , Did you solve it? I have same situation…!
Unfortunately, no. Not yet! I’ll post a solution as soon as It’s fixed. Good luck Saeid!
It happens to me too. Did you find the solution?
Response code 301 error .
Followed this tutorial exactly as mentioned
Also did what some one suggested to add www but nothing worked.
please help, i m using esp8266 and premium hosting services
Hi,
I try to rebuild this project in micropython. I am using urequest.
It work all fine when I use the Arduino code but with Micropython I am getting a ‘Wrong API Key provided’.
What might I do wrong .. or, do you have a micropython version if this project somewhere?
Hello,
all works fine in Arduino. When I reproduce the project in Micropython (urequest.post) I am getting a ‘Wrong API Key provided’ message.
Any idea what may have gone wrong? Or … do you provide this project also in Micropython?
Dear Rui and Sara,
I want to follow instantly. How can I make the data refresh automatically?
Thank you very much
Hello!
What about to write a guide to ‘read’ the wind speed and wind direction from an anemometer?
(eg the one you can find here: https://projects.raspberrypi.org/en/projects/build-your-own-weather-station/0 ) with an ESP8266 or ESP32 and arduino IDE ?
Thanks!
Hi Stefano.
Thanks for the suggestion.
It’s on my to-do list.
Regards,
Sara
Hi Rui,
Thanks for a great set of tutorials. I’m just having a minor problem with getting the Weather Station one to work. I keep getting the “HTTP Response code: 301”. I read the earlier comments and added in “www” in the URL, but it made no difference.
Does it matter if the website is http or https? Any other suggestions on how to diagnose this would be appreciated. Is there a way to write a url to test this in the browser.
Many Thanks,
John
If you are using Blue host as web provider you will need to change the http to https.
It no longer supports http.
301 is a redirection. you can check the redirection by puttting the php website into your browser and (in this case if it is working you will get a no data posted response) looking at the page title on top you will see the address is https.
There’s an error on the readingsCount part
Warning: Undefined array key “readingsCount” in /srv/disk27/4219040/www/h2ochromium.atwebpages.com/espweatherstation.php on line 13
Hello i have this error Please help
Warning: Undefined array key “readingsCount” in /srv/disk27/4219040/www/h2ochromium.atwebpages.com/espweatherstation.php on line 13
It works perfectly with the BME280 sensor and with:
1 – ESP32 – same as in the instructions
2 – ESP8266 – same as in the instructions
3 – ESP01 / ESP01S (SDA -> GPIO 0 and SCL -> GPIO 1)
To work with ESP01 / ESP01S you must replace:
void setup() {
Serial.begin(115200);
WiFi.begin(ssid, password);
Serial.println(“Connecting”);
while(WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(“.”);
}
with
void setup() {
Serial.begin(115200);
Wire.pins(0, 2);
Wire.begin(0, 2);
WiFi.begin(ssid, password);
Serial.println(“Connecting”);
while(WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(“.”);
}
For all of the above to work correctly at temperatures lower than -9.99°C and to make the correct average of the temeratures, the database must be like this:
CREATE TABLE SensorData (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sensor VARCHAR(30) NOT NULL,
location VARCHAR(30) NOT NULL,
value1 DECIMAL(5,2),
value2 DECIMAL(5,2),
value3 DECIMAL(6,2),
reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
Work perfect with MariaDB
Hi Sara and Rui,
i have a question to this project.
Is it possible to implement a (simple) API-Interface to this project to get the sensor values for external use. This means the following proedure:
If i do a IP-Request such as http://ip-of-esp/values to the ESP there should be a simple respone with the values (text, json e.g.). This response could be used in a third-party-application to participate from the sensor-readings ans values.
Maybe you have a project-sample here that shows this “value-sharing” over a ip-call to the ESP.
Thanks in Advance for a short reply
Manfred
Hi Sara and Rui great project .Good idea to use PHP and MySql to store temperature pressure, humidity data. I would like to know if you have a pcb ready to use for this project such as the one for esp32cam plus BME 280 sensor with telegram bot. Thank you.
Greetings.
Massimo
Hi.
Thanks for your interest in our projects.
We have several different PCB projects that use a BME280 that can be adapted to use with this project.
Here’s a list:
– https://randomnerdtutorials.com/esp32-iot-shield-pcb-dashboard/
– https://randomnerdtutorials.com/esp32-weather-station-pcb/
– https://randomnerdtutorials.com/esp32-status-indicator-sensor-pcb/
I hope this helps.
Regards,
Sara
Hi
Thanks for answer . I didn’t see ” ESP32 IoT Shield” ..great . I think I will use that.
Regards.
Massimo
Hi,
Thanks for a great tutorial, I’m trying to add weather description to the table from openweatherapi. I’ve been able to get my script to call the api and add the weather information I want, I’ve also added this to the following section:-
String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&value1=" + String(bme.readTemperature())
+ "&value2=" + String(bme.readHumidity()) + "&value3=" + String(bme.readPressure()/100.0F) + "&value4=" + weatherType + "";
However it doesn’t seem to write to the database on the HTTP Post, I have updated the php files to add value 4 in too them. Any suggestions would be a great help.
Hi.
Do you get any errors on the serial monitor?
Regards,
Sara
Hello and thank you for the tutorial. Wondering how to put a little check in the script to reboot the esp32 if no wifi or no data posted to mysql.
I see in the esp-post-data.php file there is the else statement
echo “No data posted with HTTP POST.”;
but in testing, I’ve changed the mysql connection to make it fail and still the esp32 is returning a 200 success.
Ultimately, I’d like to put in a restart for when the wifi fails or no data is written though I’m not sure how to proceed with that.
Thank you!
txinga
The include_once(‘esp-database.php’); in esp-weather-station.php doesn’t manage to include the esp-database.php, so that the functions defined in esp-database.php are missing for esp-weather-station.php. Has anyone have a similar problem?
the problem fixed itself, maybe my new database hadn’t been fully installed yet. Suddenly it worked.
Many Thanks for this project Rui Santos!
Highly detailed and most precise documentation I ever seen before.
This kind of knowledge sharing should be taught.
Sara and Rui,
Great project and website. This is my goto for anything I am trying with and ESP8266 and now you have also converted me to MicroPython as well.
I have a PHP and HTML question.
Is there a reason that the quakes and top of the web page are in separate HTML and the second half of the page is in PHP with echo’d HTML statements. I am trying to modify the webpage to create Tabs and I am finding it difficult to create separate div’s for each Tab.
Thanks again
Hello,
What do I need to do to display graphs from two sensors side by side? I added a second sensor to esp-weather-station.php and it is displayed at the bottom.
Hello,
Thank you for this, it’s pretty fun. I tried to change my Celsius to Fahrenheit and it did not work. The page is still displaying in Celsius. I made another small change just to see if that would work and it did, so I don’t think the page is being cached. Is there something else I need to change other than this? Thank you -Jeff
function setTemperature(curVal){
//set range for Temperature in Celsius -5 Celsius to 38 Celsius
//var minTemp = -5.0;
//var maxTemp = 38.0;
//set range for Temperature in Fahrenheit 23 Fahrenheit to 100 Fahrenheit
var minTemp = 23;
var maxTemp = 100;
Hi Sara, Rui
Many thanks for the detailed post, I was able to modify this and have it routed through the blockchain on to my private fleet contract and allowed me to show a very secure esp32 demo.
Hello,
I tried to make this project with an esp32 and the same BME280 that is used in this tutorial.
I bought the BlueHost license, with the link present in this tutorial.
I thought I had succeeded in all the steps in this tutorial, but once the code had been uploaded to my esp32, the web page displayed a blank page.
In the previous steps, I managed to create the files “esp-post-data.php” and “esp-database.php”. I replaced the 3 fields indicated and obtained the following message: “No data posted with HTTP POST.”
But when I want to display the “https://example.com/esp-weather-station.php” page, the page remains blank and doesn’t display the diagrams shown in the tutorial, despite the fact that I’ve created the “esp-style.css” and “esp-weather-station.php” files.
Could you please help me? Thank you and have a nice day.
Sincerely
When I run the mysite.com/esp-weather-station.php I see a white empty page…I removed the php code lines from the esp-weather-station.php file and leave only the html lines…Now I can see the page and the blank values..I checked the database name and password again, all is ok and right written.Do you have any idea what is wrong in the php lines?…Thanks..
Hello,
I had this project up and running for a couple of years but started to run into various problems with hosting companies and decided to take the project local using WAMP and MySQL installed and configured on a desktop computer over my home wifi network. I am using your updated files. I continue to receive http response error code -1 with no data being posted to the table. Can you suggest trouble shooting steps? Thanks!