Sending data from the ESP8266 to MySQL directly

Introduction

The ESP8266 is a popular programmable microcontroller with a lot of features. One of its main features in the ability to directly connect the microcontroller to WiFi. This gives u the ability to control the microcontroller over the internet using http, MQTT and many other ways. But it also gives u the ability to send data to the internet. In this guide i am going to explain to you how you can connect a ESP8266 based controller with an BME280 directly to an MySQL database without any other connections or servers in-between.

Parts required

-NodeMCU (Other ESP8266 based controllers will also work)

-BME280 sensor (Other sensors will also work but u have to modify the code)

-Breadboard

-Jumper wires

Schematic

NodeMCU/ESP8266 BMP280
3,3v/5v VIN
GND GND
D1 (SCL) SCL
D2 (SDA) SDA

Code

We’re gonna program the ESP8266 using the Arduino IDE. You need to install the ESP8266 board in to the IDE and know how to program to it. For this you can follow the following tutorial:

https://randomnerdtutorials.com/installing-esp8266-nodemcu-arduino-ide-2-0/

To be able to connect to MySQL and to use the BME280 you need to install some libraries. Follow the next steps to install these libraries:

-Adafruit BME280 Library (version 2.2.2)

-MySQL MariaDB_Generic (version 1.7.2)

Open the Arduino IDE and go to Sketch -> Include Library -> Manage Libraries. Here search for the above Libraries and install them one by one.

After installing the required libraries and making sure your Arduino IDE is set-up copy the following code to your Arduino IDE. Don’t upload it yet. You first need to make some changes to make it work!

/*
 * ioTechProjects.com 2022
 * 
 * ESP8266 to MySQL tutorial
 * 
 * u are free to edit this code
 */
 
#include <Wire.h>
#include <SPI.h>
#include <MySQL_Generic.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>

// Defining BME280 sensor
#define SEALEVELPRESSURE_HPA (1013.25)
Adafruit_BME280 bme; // I2C

// MySQL Debug Level from 0 to 4
#define _MYSQL_LOGLEVEL_      1
#define MYSQL_DEBUG_PORT      Serial

// Network Info
char ssid[] = "WIFI_SSID";
char pass[] = "WIFI_PASSWORD";

// Database Adress
char server[] = "HOSTNAME OR IP";
uint16_t server_port = PORT;

// Database Account
char user[]         = "DATABASE_NAME";
char password[]     = "DATABASE_PASSWORD";

//Database Name
char database[] = "DATABASE_NAME";
char table[]    = "DATABASE_TABLE_NAME";

// Default insert data values
double temperature = 0;
double humidity = 0;
double pressure = 0;

// MySQL something
MySQL_Connection conn((Client *)&client);
MySQL_Query *query_mem;

void setup()
{
  // Initialize status LED
  pinMode(LED_BUILTIN, OUTPUT);
  
  // Setup Serial
  Serial.begin(115200);

  // Begin WiFi section
  Serial.println("================================================");
  Serial.println(String("Connecting to ") + ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print(".");
  }

  // Print out info about the WiFi connection
  Serial.print("Connected to network. My IP address is: ");
  Serial.println(WiFi.localIP());

  // Test MySQL connection
  Serial.print("Connecting to SQL Server @ ");
  Serial.print(server);
  Serial.println(String(", Port = ") + server_port);
  Serial.println(String("User = ") + user + String(", PW = ") + password + String(", DB = ") + database);

  // Testing BME280 Sensor
  unsigned status;
  status = bme.begin(0x76);
  if (!status) {
    Serial.println("Could not find a valid BME280 sensor, check wiring, address, sensor ID!");
    Serial.print("SensorID was: 0x"); Serial.println(bme.sensorID(), 16);
    Serial.print("        ID of 0xFF probably means a bad address, a BMP 180 or BMP 085\n");
    Serial.print("   ID of 0x56-0x58 represents a BMP 280,\n");
    Serial.print("        ID of 0x60 represents a BME 280.\n");
    Serial.print("        ID of 0x61 represents a BME 680.\n");
    while (1) delay(10);
  }
}


void loop()
{
  // Turn on status LED
  digitalWrite(LED_BUILTIN, LOW);

  // Measuring data
  Serial.println("================================================");
  Serial.println("Measuring data...");
  delay(500);
  Serial.println();
  
  temperature = bme.readTemperature();
  pressure = bme.readPressure() / 100.0F;
  humidity = bme.readHumidity();
  
  Serial.print("Temperature = ");
  Serial.print(temperature);
  Serial.println(" °C");

  Serial.print("Pressure = ");
  Serial.print(pressure / 100.0F);
  Serial.println(" hPa");

  Serial.print("Humidity = ");
  Serial.print(humidity);
  Serial.println(" %");

  Serial.println("================================================");

  // change Insert Query with new data
  String INSERT_SQL = String("INSERT INTO ") + database + "." + table + " (temperature, humidity, pressure) VALUES (" + temperature + "," + humidity + "," + pressure + ")";

  // Insert data in to database
  Serial.println("Connecting to MySQL...");
  Serial.println();
  if (conn.connectNonBlocking(server, server_port, user, password) != RESULT_FAIL)
  {
    delay(500);
    // Initiate the query class instance
    MySQL_Query query_mem = MySQL_Query(&conn);

    if (conn.connected())
    {
      Serial.println(INSERT_SQL);

      // Execute the query
      // KH, check if valid before fetching
      if ( !query_mem.execute(INSERT_SQL.c_str()) )
        Serial.println("Insert error");
      else
        Serial.println("Data Inserted.");
    }
    else
    {
      Serial.println("Disconnected from Server. Can't insert.");
    }
    conn.close();                     // close the connection
  }
  else
  {
    Serial.println("\nConnect failed. Trying again on next iteration.");
  }

  // Turn off status LED
  digitalWrite(LED_BUILTIN, HIGH);

  // Sleep till next data measure and insert
  Serial.println("================================================");
  Serial.println("\nSleeping...");
  delay(5000);
}

Code changes

Now you need to change the following values in the code to your values.

After u done this you can upload the code. It should now connect to your WiFi network and connect to your database. Now there is only one thing for us to do and set up the right database structure so that our ESP8266 can write data to MySQL.

// Network Info
char ssid[] = "WIFI_SSID";
char pass[] = "WIFI_PASSWORD";

// Database Adress
char server[] = "HOSTNAME OR IP";
uint16_t server_port = PORT;

// Database Account
char user[]         = "DATABASE_NAME";
char password[]     = "DATABASE_PASSWORD";

//Database Name
char database[] = "DATABASE_NAME";
char table[]    = "DATABASE_TABLE_NAME";

Database requirements

Our ESP8266 needs a MySQL database to store it’s data. We’re now going to set up the structure of the database. Assuming you already have an MySQL phpmyadmin host or set up your own server.

If u have not, make sure you have an MySQL phpmyadmin server before you move on. Also make sure Legacy Authentication Is enabled! Otherwise the code is not gonna work.

I suggest u use the following tutorial if u gonna host your own: https://www.osradar.com/how-to-install-phpmyadmin-on-windows-10/

 

Database structure

id -> type: INT -> select A_I (auto increment)

timestamp -> type: DATETIME -> select standard value: CURRENT_TIMESTAMP

temperature -> type: DOUBLE

humidity -> type: DOUBLE

pressure -> type: DOUBLE

Wrapping Up

Now power on the board, Everything should now work!

You now made an ESP8266 based sensor node who directly talks to an MySQL Database! Feel free to leave a comment below when u need extra information or troubleshooting help.

Here are some demonstration pictures of the project:

The Hardware
Data debug in Arduino IDE
MySQL database