ESP8266IoT Projects

Insert Data into MySQL Database with ESP8266 Development Board

In this tutorial, I am going to show how we can insert sensor data into the MySQL database using ESP8266. we will also design a webpage that displays the sensor readings, with timestamps and other information from the database. You can visualize sensor data from anywhere by accessing the server.

For demonstration purposes, we’ll be using an Ultrasonic HC-SR04 sensor connected to an ESP8266 board. You can modify the program code to send readings from a different sensor like DHT11/DHT22, BMP180/BMP280, etc. or use multiple boards.

Components Required

In order to Insert Data into MySQL Database with ESP8266 Development Board, you’ll use these components:

Hardware components:

  • NodeMCU ESP8266 Development Board
  • Ultrasonic HC-SR04 Sensor
  • Few jumper wires

Software Components:

  • Hosting server and domain name/ Xampp Server
  • PHP script to insert data into MySQL database and display it on a web page
  • MySQL database to store sensor readings

PHP Script to Insert Data into MySQL Database

Here, we’re going to create a PHP script that is responsible for receiving incoming data from NodeMCU ESP8266 and insert data into a MySQL database.

Insert Data into MySQL Database with ESP8266 Development Board

Now start the Apache and MySQL server from the xampp control panel. Then go to the xampp htdocs folder and create a new folder called “Sensor-data-test“.

For Demonstration, we are using the xampp server. If you are using any Hosting service then you need to go to Cpanel and under public_html folder create a new file called “sensor-data.php“.

First, we will create a file called sensor-data.php. The main purpose of this file is to establish a connection between the website script and the MySQL database. Below this is the code for “sensor-data.php” where the host configuration, dbname, user, etc. can be changed according to your respective database configuration.

<!DOCTYPE html>
<html>
<body>
<?php
/*
  Alsan Parajuli
  Complete project details at https://iotprojectsideas.com/
  
  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files.
  
  The above copyright notice and this permission notice shall be included in all
  copies or substantial portions of the Software.
*/
$servername = "localhost";
// REPLACE with your Database name
$dbname = "sensor database";
// REPLACE with Database user
$username = "iot projects";
// REPLACE with Database user password
$password = "admin@TheIoTProjects.com";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
strtotime("$row_reading_time + 4 hours"));
$conn->close();
?> 
</table>
</body>
</html>

The above code has two major functions. First, $conn will start the database connection, and conn->close; closes the database connection.

Second, this sensor-data.php file will initiate GET requests to display the data that has been recorded in the database in tabular form.

$sql = "SELECT id, sensor, location, distance, reading_time FROM SensorData ORDER BY id DESC";

echo '<table cellspacing="5" cellpadding="5">
      <tr> 
        <td>ID</td> 
        <td>Sensor</td> 
        <td>Location</td> 
        <td>Distance (cm)</td> 
        <td>Timestamp</td> 
      </tr>';
 
if ($result = $conn->query($sql)) {
    while ($row = $result->fetch_assoc()) {
        $row_id = $row["id"];
        $row_sensor = $row["sensor"];
        $row_location = $row["location"];
        $row_distance = $row["distance"]; 
        $row_reading_time = $row["reading_time"];
 echo '<tr> 
                <td>' . $row_id . '</td> 
                <td>' . $row_sensor . '</td> 
                <td>' . $row_location . '</td> 
                <td>' . $row_distance . '</td> 
                <td>' . $row_reading_time . '</td> 
              </tr>';
    }
    $result->free();
}

In the sensor-data.php file, the $conn function will be called and execute a SELECT query to get data from the database and form a table containing the data before closing the database connection.

Third, we will create a file called post-sensor-data.php which will handle POST requests where this script will be the gateway for recording data to the MySQL database. Below is the contents of this file.

<?php

/*
  Alsan Parajuli
  Complete project details at https://iotprojectsideas.com/
  
  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files.
  
  The above copyright notice and this permission notice shall be included in all
  copies or substantial portions of the Software.
*/

$servername = "localhost";

// REPLACE with your Database name
$dbname = "sensor database";
// REPLACE with Database user
$username = "iot projects";
// REPLACE with Database user password
$password = "admin@TheIoTProjects.com";

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

$api_key= $sensor = $location = $value1 = $value2 = $distance = "";
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"]);
        $distance = test_input($_POST["distance"]);
        
        // 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, distance)
        VALUES ('" . $sensor . "', '" . $location . "', '" . $distance . "')";
        
        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        } 
        else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
    
        $conn->close();
    }
    else {
        echo "Wrong API Key provided.";
    }

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

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

In this script, database connection initiation, API Key validation, and request method will be performed. In the API Key, you can put any random numbers. Here, I set $api_key_value = “tPmAT5Ab3j7F9“. After that, the data will be executed in a query and entered into the database. If something went wrong (like wrong API Key, incomplete input data, or wrong method), the query will be canceled.

Testing the PHP MySQL Database and a Web Page

Now Go To PhpMyAdmin and create a New Database “sensor database“. Then, go to privilege and create a new user, for example username = “iot projects“, host=”localhost“, password = “admin@TheIoTProjects.com“. Finally, Go to SQL Section and Paste the following script and click on Go.

CREATE TABLE SensorData (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor VARCHAR(30) NOT NULL,
    location VARCHAR(30) NOT NULL,
    distance VARCHAR(10),
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

To test the PHP server, You can use the POSTMAN application with data bodies that are needed according to your projects. For this project, we need API Key, Sensor, Location, and Distance.

Test MySQL Database and PHP Script using POSTMAN application

When I checked the data provided is successfully recorded in the database

Setting Up NodeMCU ESp8266 to Insert Data into MySQL Database

The HC-SR04 Ultrasonic sensor module provides the measurement of the time that it takes for sound to bounce off an object and return back to the sensor. Want to know more visit the Link- Working Principle of HC-SR04 Ultrasonic Sensor. Following are the wiring details for connecting the ESP8266 Development Board to the HC-SR04 sensor:

Circuit Diagram to Insert Data into MySQL Database with ESP8266
Circuit Diagram

Ultrasonic HC-SR04 wiring to ESP8266

Ultrasonic HC-SR04ESP8266
Vcc PinVin Pin
Trig PinD1 (GPIO 5)
Echo PinD2 (GPIO 4)
GND PinGND
Assemble your circuit as shown in the schematic diagram.
Assembling circuit diagram

Program Code Explanation

We’ll program the ESP8266 board using Arduino IDE, so you must have the following library file installed in your Arduino IDE. You can Download them from library manager on Arduino IDE.

#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>
#include <WiFiClient.h>
#include <Wire.h>
#include <Ultrasonic.h>
// Instantiate trig and echo pin for ultrasonic sensor
Ultrasonic ultrasonic(5, 4);
const char* ssid     = "Alsan Air WiFi 4";
const char* password = "11122235122@kap";
const char* serverName = "http://192.168.1.4/Sensor-data-test/post-sensor-data.php";
// Example: http://xxx/post-sensor-data.php";
String apiKeyValue = "theiotprojects";
// Example: tPmAT5Ab3j7F9
String sensorName = "HC-SR04";
String sensorLocation = "Home";

The above program indicates that we are importing the library modules needed for this program. Then we also need to instantiate sensors on the trig and echo pins, and we need to define variables that will be used in this program such as SSID and network passwords, server addresses (API endpoints), API Key, sensor names, and location for reading data.

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());
}

In the setup procedure above, the WiFi network will be initialized and display the usable IP address.

void loop() {
  if (WiFi.status() == WL_CONNECTED) {
    HTTPClient http;
    http.begin(serverName);

    http.addHeader("Content-Type", "application/x-www-form-urlencoded");

    // Prepare your HTTP POST request data
    String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName + "&location=" + sensorLocation + "&distance=" + String(ultrasonic.read()) + "";
    //String httpRequestData = "api_key=theiotprojects&sensor=HC-SR04&location=Home&distance=24.75";
    Serial.print("httpRequestData: ");
    Serial.println(httpRequestData);
    // Send HTTP POST request
    int httpResponseCode = http.POST(httpRequestData);

    if (httpResponseCode > 0) {
      Serial.print("HTTP Response code: ");
      Serial.println(httpResponseCode);
    }
    else {
      Serial.print("Error code: ");
      Serial.println(httpResponseCode);
    }
    // Free resources
    http.end();
  }
  else {
    Serial.println("WiFi Disconnected");
  }
  //Send an HTTP POST request every 20 seconds
  delay(10000);
}

In the above loop procedure, a request will be made using the POST method to the PHP server that we have prepared previously.

String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName + "&location=" + sensorLocation + "&distance=" + String(ultrasonic.read()) + "";

The above code is a Query String that will be passed to the server to be recorded in the database.

int httpResponseCode = http.POST(httpRequestData);

Then, the code above is a method for making a request to the server that has been started ( http.begin (serverName) ). If this is successful, the data will be recorded in the MySQL database.

Program Code for Inserting Data to MySQL Database with ESP8266

This is a program source code for Inserting Data into MySQL Database with ESP8266 Development Board. Simply, copy the program code and compile it using the Arduino IDE to program the ESP8266 Development Board and HC-SR04 sensor.

#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>
#include <WiFiClient.h>
#include <Wire.h>
#include <Ultrasonic.h>
// Instantiate trig and echo pin for ultrasonic sensor
Ultrasonic ultrasonic(5, 4);
const char* ssid     = "Alsan Air WiFi 4";
const char* password = "11122235122@kap";
const char* serverName = "http://192.168.1.4/Sensor-data-test/post-sensor-data.php";
// Example: http://xxx.com/esp_hcsr04_php_post.php
String apiKeyValue = "theiotprojects";
// Example: tPmAT5Ab3j7F9
String sensorName = "HC-SR04";
String sensorLocation = "Home";
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());
}
void loop() {
  if (WiFi.status() == WL_CONNECTED) {
    HTTPClient http;
    http.begin(serverName);

    http.addHeader("Content-Type", "application/x-www-form-urlencoded");

    // Prepare your HTTP POST request data
    String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName + "&location=" + sensorLocation + "&distance=" + String(ultrasonic.read()) + "";
    //String httpRequestData = "api_key=theiotprojects&sensor=HC-SR04&location=Home&distance=24.75";
    Serial.print("httpRequestData: ");
    Serial.println(httpRequestData);
    // Send HTTP POST request
    int httpResponseCode = http.POST(httpRequestData);

    if (httpResponseCode > 0) {
      Serial.print("HTTP Response code: ");
      Serial.println(httpResponseCode);
    }
    else {
      Serial.print("Error code: ");
      Serial.println(httpResponseCode);
    }
    // Free resources
    http.end();
  }
  else {
    Serial.println("WiFi Disconnected");
  }
  //Send an HTTP POST request every 20 seconds
  delay(10000);
}

Upload the Code

Then the last one is that we have to upload the program code to the ESP8266 board by clicking on the arrow in the top left corner. After uploading, open the serial monitor to see the status of the project.

Serial Monitor of Sending data to mysql database

Now, when we see our PHP page at “http://192.168.1.4/Sensor-data-test/post-sensor-data.php“, the results of the listing will be displayed.

Insert Data into MySQL Database with ESP8266 Development Board

Some Frequently Asked Questions:

What to do when Serial Monitor shows ERROR CODE -1?

Sometimes error code-1 is printed in the Arduino IDE Serial Monitor, but the data is still inserted into the server.
If your server is not receiving the data, make sure you have the right files saved in your server (/sensor-data.php and post-sensor-data.php)

Where should I save those PHP File?

If you are using Domain and Hosting. You need to save all the PHP files in the root directory of public_html.
If you are using Xampp server. You need to save all those PHP files in C:xampphtdocsYour-Folder-Name.

My httpRequestData response is -1?

Try to check your server name, it should be starting with “http://…” not “https://..” There is separate method for HTTPS Request. Check twice when you just copy & paste the host address.

How secure is this type of HTTP posting??

You can define your server with HTTPS to have all data posted to your server encrypted. Of course, with this example, you are relying on third-party websites, but I’ve been using it for years and haven’t had any problem.
The URL /sensor-data.php is currently open to the public, but you can set up a login system with a PHP code that would keep that data private to you.

How to change the SQL query time_reading to show my local time?

ou’ve to add some extra lines of code to adjust the time to your timezone in the sensor-data.php file:
You can simply add one of the following lines to adjust the time displayed in your webpage:
(you can change 1 to any number)
$row_reading_time = date(“Y-m-d H:i:s”, strtotime(“$row_reading_time – 1 hours”));
(you can change 4 to any number)
$row_reading_time = date(“Y-m-d H:i:s”, strtotime(“$row_reading_time + 4 hours”));
If the actual time (minute and date) is incorrect when inserting data into the database, I recommend contacting your host directly to see what is going on in your server.
I hope this solves your problem.

How is the Api_key_value generated? Do we just pick any random character/numbers for this Api_key?

Yes. That was randomly generated. You can set any API Key that you want.
That way, only who knows the API key can post data to your database.

 Data into MySQL Database with ESP8266

Conclusion

In this tutorial, we have shown you how to Insert Data into MySQL Database with the ESP8266 Development Board. Now you can modify this project and add any other sensor to read data in charts. I hope you enjoyed reading this article. If you need any type of help related to this project then do let me know in the comment section below.

You might also like reading:

Related Articles

8 Comments

  1. Was not able to get data in mysql db. Even though it is a good effort and everything is written so systematic but I think something is going wrong. Already tried many times but error code -1 keeps coming.

  2. Hi Alsan, thanks for your prompt reply. I have taken up a domain where I am trying to implement it in Public HTML. Now I am getting HTTP Response code: 404 on serial monitor. It looks like domain is not visible, I am trying to rectify this.
    Secondly please advise what will be the right POST request for POSTMAN testing.
    I am struggling with this for weeks now, kindly help!

  3. Hi, I keep getting this error ” mysqli::__construct(): The server requested authentication method unknown to the client [sha256_password]” eventhough I already followed your instructions and also my sensor’s data could not be read. Can you please help me?

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button