ESP32/ESP8266: Seamless Data Integration with MySQL and PHP

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.

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