ESP32/ESP8266: Seamless Data Integration with MySQL and PHP

In this project, we will develop an ESP32 or ESP8266 device that can communicate with a remote server. The device will act as an HTTP client by making POST requests to a PHP script. The purpose of these requests will be to send sensor data (such as temperature or humidity readings) to the PHP script. The script will then insert this sensor data into a MySQL database, allowing the readings to be stored in a table for later retrieval or analysis. Overall, this creates a simple IoT system where a WiFi-enabled microcontroller can collect sensor readings and upload them to a database hosted on a remote web server.

Transform Your IoT Project into a Data-Driven Powerhouse

Gain real-time insights from your IoT devices by seamlessly integrating sensor readings into a MySQL database using PHP and an ESP32 or ESP8266 microcontroller. This comprehensive guide will equip you with the knowledge and tools to build a robust data visualization system, enabling you to monitor and analyze sensor data from anywhere in the world.

Harness the Synergy of Hardware and Software

This project utilizes the ESP32 or ESP8266 microcontroller as the data acquisition interface, collecting sensor readings and transmitting them to a web server. The PHP script then takes over, processing the data and inserting it into a MySQL database for secure storage.

Unleash the Power of Data Visualization

A beautifully crafted web page serves as the data visualization hub, displaying sensor readings, timestamps, and other relevant information in a clear and concise manner. Access this web page from any device, anywhere in the world, to stay informed about your IoT project’s performance.

Embrace Versatility and Adaptability

The provided code can be easily modified to accommodate different sensors or multiple ESP32/ESP8266 boards, making this project highly versatile and adaptable to your specific needs.

Embark on Your IoT Journey

To embark on this exciting journey into the world of IoT data integration, you’ll need the following tools and technologies:

  • ESP32 or ESP8266 microcontroller and Arduino IDE
  • Hosting server and domain name
  • PHP script for data insertion and web page development
  • MySQL database for data storage

Get ready to transform your IoT project into a data-driven powerhouse with this comprehensive guide!

2. Preparing Your MySQL Database

Once you have registered for a web hosting account and purchased a domain name, you will need to access your hosting control panel (like cPanel) to begin setting up the database infrastructure. Log into your hosting dashboard and then take the following steps:

Create the MySQL database that will store the sensor data.

Establish a database username and password that the PHP script can use to connect to the database.

Within the new database, design the structure of the SQL table where the sensor readings will be inserted as records. This would define the table fields like sensor type, timestamp, value, etc.

By completing these preliminary database configuration tasks through the hosting dashboard, you will put in place the backend components needed to receive the data uploads from the IoT device and store them in an organized manner for retrieval and analysis.

Creating a database and user

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:

3. Specify the username that will be used to access the database. Also, define a secure password for that username account. Be sure to make note of (or save securely) both the username and password that you establish here, as you will need to provide them to the PHP script later on so it can authenticate and connect to the database. The script will need the login credentials in order to reliably insert the sensor readings received from the IoT device. So keeping track of the username and password from this database setup step is important for completing the project.

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.

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 – Insert Data in MySQL Database

In this part of the project, we will develop a PHP script that will serve two key functions:

It will listen for and receive HTTP POST requests containing sensor data sent from the ESP32/ESP8266 device.

It will insert the sensor readings from these requests into the MySQL database that was previously configured.

To set up the PHP file, log into your hosting control panel (such as cPanel) and find the file manager tool. This will allow you to upload the PHP script file to your web server. Once in place, the script can then perform the tasks of receiving data uploads from the IoT device and saving them to the database.

By creating this receiving/inserting PHP script, we link together the IoT device portion of the project with the database backend, completing the client-server architecture needed to monitor sensor values remotely through HTTP requests and store the results in the cloud.

Then, select the public_html option and press the “+ File” button to create a new .php file.

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

Edit the newly created file (post-esp-data.php) and copy the following snippet:

<?php

/*
  Rui Santos
  Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/
  
  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files.
  
  The above copyright notice and this permission notice shall be included in all
  copies or substantial portions of the Software.
*/

$servername = "localhost";

// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";

// Keep this API Key value to be compatible with the ESP32 code provided in the project page. 
// If you change this value, the ESP32 sketch needs to match
$api_key_value = "tPmAT5Ab3j7F9";

$api_key= $sensor = $location = $value1 = $value2 = $value3 = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = test_input($_POST["api_key"]);
    if($api_key == $api_key_value) {
        $sensor = test_input($_POST["sensor"]);
        $location = test_input($_POST["location"]);
        $value1 = test_input($_POST["value1"]);
        $value2 = test_input($_POST["value2"]);
        $value3 = test_input($_POST["value3"]);
        
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 
        
        $sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3)
        VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";
        
        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        } 
        else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
    
        $conn->close();
    }
    else {
        echo "Wrong API Key provided.";
    }

}
else {
    echo "No data posted with HTTP POST.";
}

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

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-domain.com/post-esp-data.php

Source: ESP32/ESP8266: Seamless Data Integration with MySQL and PHP


About The Author

Ibrar Ayyub

I am an experienced technical writer with a Master's degree in computer science from BZU Multan University. I have written for various industries, mainly home automation, and engineering. I have a clear and simple writing style and am skilled in using infographics and diagrams. I am a great researcher and is able to present information in a well-organized and logical manner.

Follow Us:
LinkedinTwitter
Scroll to Top