Do It Yourself Cloud Weather Station with ESP32/ESP8266 (MySQL Database as well as PHP).

Build a cloud weather terminal control panel to picture your ESP32 or ESP8266 sensing unit analyses from anywhere in the globe. You’ll envision your sensor information displayed on assesses and on a table. The ESP32 or ESP8266 will make an HTTP POST demand to a PHP manuscript to put your data into a MySQL database.

DIY Cloud Weather Station with ESP32/ESP8266 MySQL Database and PHP

Updated on 27 March 2023

Previously, we’ve stored sensor readings in a database as well as presented or using your own server. Now, I’ve determined to take a couple of steps better as well as include some more information to the web page.

I’ve added 2 assesses to display the most recent temperature as well as moisture analyses along with some data about the minimum, optimum and average readings from a number of analyses that you can define. You can likewise picture all the current readings on a table and you can pick just how lots of readings you wish to reveal.

To construct this job, you’ll utilize 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 split into the following major areas:

Watch the Video Demonstration

To see how the job functions, you can watch the adhering to video demonstration:

0. Download Source Code

For this project, you’ll need these data:

  • SQL query to create your table:
  • Insert and access database readings:
  • Handle HTTP Post requests:
  • CSS file to style your web page:
  • Display your sensor readings:
  • Arduino Sketch for ESP32:
  • Arduino Sketch for ESP8266 :
  • If your server doesn’t support HTTPS, use this Arduino Sketch (compatible with the ESP32 and ESP8266:

1. Hosting Your PHP Application and MySQL Database

The goal of this task is to have your very own domain and holding account that permits you to store sensing unit readings from the ESP32 or ESP8266. You can imagine the analyses from throughout the world by accessing your own server domain.

Here’s a high-level review of just how the project functions:

  1. You have an ESP32 or ESP8266 that sends sensor readings to your own server. For this, you have your board connected to your router;
  2. In your server, there’s a PHP script that allows you to store your readings in a MySQL database;
  3. Then, another PHP script will display the web page with the gauges, table, and all the other information;
  4. Finally, you can visualize the readings from anywhere in the world by accessing your own domain name.

Hosting Services

I advise using among the complying with holding solutions that can take care of all the job demands:

  • : free domain name when you sign up for the 3-year plan. I recommend choosing the unlimited websites option;
  • : Linux server that you manage through a command line. I only recommended this option for advanced users.

Those 2 solutions are the ones that I use and personally suggest, however you can use any type of various other hosting solution. Any type of hosting service that uses PHP and MySQL will certainly work with this tutorial. I advise if you don’t have a holding account.

When getting a holding account, you’ll also have to acquire a domain. This is what makes this task fascinating: you’ll be able to go to your domain (https://example.com) and see your ESP readings.

If you like our tasks, you could consider enrolling in among the recommended organizing services, since you’ll be supporting our work.

Note: you can additionally run a on a Raspberry Pi to. However, the purpose of this tutorial is to release readings in your own domain that you can access from anywhere in the globe. This enables you to easily access your ESP readings without counting on a third-party IoT platform.

2. Preparing Your MySQL Database

After signing up for a, you can login to your cPanel or similar control panel. Afterwards, comply with the following actions to produce your data source, username, password, as well as SQL table.

Creating a database and user

Open the “Advanced” tab:

Bluehost Advanced tab

1. Type “data source” in the search bar and choose “MySQL Database Wizard”.

CPanel select MySQL database wizard to create db

2. Enter your preferred Database name. In my case, the data source name is esp_data. Then, press the “Next Step” button:

ESP32 ESP8266 CPanel Create MySQL Database

Note: later on you’ll have to utilize the database name with the prefix that your host gives you (my database prefix in the screenshot above is blurred). I’ll describe it as example_esp_data from now on.

3. Type your Database username and also set a password. You should save all those details because you’ll need them later on to develop a data source connection with your PHP code.

ESP32 ESP8266 CPanel Create MySQL Database User and Password

That’s it! Your brand-new data source as well as customer were created effectively. Now, conserve all your details due to the fact that you’ll need them later on:

  • Database name: example_esp_data
  • Username: example_esp_board
  • Password: your password

Creating a SQL table

After developing your data source and also individual, return to cPanel dashboard and look for “phpMyAdmin”.

ESP32 ESP8266 CPanel Open PHPMyAdmin

In the left sidebar, choose your data source name example_esp_data and also open the “SQL” tab.

ESP32 ESP8266 PHPMyAdmin Open Database

Important: make certain you’ve opened up the example_esp_data database. Then, click the SQL tab. You might develop a table in the incorrect data source if you do not follow these precise actions and run the SQL inquiry.

Copy the SQL inquiry in the adhering to bit:

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 question field (highlighted with a red rectangle) and also push the “Go” button to develop your table:

ESP32 ESP8266 PHPMyAdmin Create SQL Table

After that, you need to see your newly developed table called SensorData in the example_esp_data data source as displayed in the figure below:

3. PHP Script HTTP POST – Receive and Insert Data in MySQL Database

In this section, we’re mosting likely to produce a PHP script that is in charge of receiving inbound demands from the ESP32 or ESP8266 and also placing the information into a MySQL data source.

If you’re utilizing a hosting service provider with cPanel, you can browse for “File Manager”:

ESP32 ESP8266 CPanel Open Edit PHP Files with File Manager

Then, pick the public_html choice and press the “+ File” switch to develop a new.php file.

ESP32 ESP8266 CPanel Create New PHP File

Note: if you’re following this tutorial as well as you’re not knowledgeable about PHP or MySQL, I suggest developing these specific files. Or else, you’ll need to change the ESP illustration supplied with different URL courses.

Create a new file in/ public_html with this exact name and extension: esp-post-data. php

Create esp post data PHP file

Edit the recently produced file (esp-post-data. php) and also copy the complying with bit:

<!--
  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 brand-new documents in/ public_html that is accountable for inserting and accessing data in your database. Call your documents: esp-database. php

Create esp database PHP file

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 require to modify the $dbname, $username as well as $password variables with your one-of-a-kind details:

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

After including the database name, username as well as password, continue as well as conserve the data with this tutorial. If you try to access your domain in the next URL path, you’ll see the following:

https://example.com/esp-post-data.php
esp post data via HTTP example PHP file

5. PHP Script – Display Database Readings on Gauges and Table

You’ll likewise require to include a CSS documents to style your control panel, name it: esp-style. css:

Create esp style CSS file

Copy that CSS to your data and wait:

/**
  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, produce one more PHP file in the/ public_html directory that will present all the database material on a web page. Name your new documents: esp-weather-station. php

Create esp weather station PHP file

Edit the recently created documents (esp-weather-station. php) and also duplicate the adhering to 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']; ?> &deg;C</td>
                    <td><?php echo $max_temp['max_amount']; ?> &deg;C</td>
                    <td><?php echo round($avg_temp['avg_amount'], 2); ?> &deg;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 in the following URL course, you’ll see the following:

https://example.com/esp-weather-station.php