Log Data into MySQL Database using NodeMCU Development Board

NodeMCU is a low-cost open-source IoT platform that initially included firmware which runs on the ESP8266 Wi-Fi SoC from Espressif Systems, and hardware which was based on the ESP-12 module.

If you are getting started with IoT or microcontrollers in general, this development board is a fierce competition to everything that you’ll come across, at least initially.

In this project, we will emphasize on looking at how to interface the NodeMCU development board with a local copy of MySQL DB using PHP.

Primarily,

1. We’ll be building a NodeMCU Access Point that will make a POST request with the sensor data to a PHP Script which will then insert the data to a Local Copy Of MySQL Database.
2. We’ll be putting together a Web Page which will retrieve the latest reading from the MySQL Database and display it.

Note: The web page in this context will not be fetching the data from the DB Asynchronously to avoid obvious complexity, We’ll have to reload page to see the latest data, we can automate the page reload process by putting in a HTML5 Meta Tag:

<meta http-equiv="refresh" content="1">

Where the content attribute represents the interval in seconds of auto page refresh.

Secondarily, to spice things up on the hardware side,

1. We’ll interface a DHT11-Temperature and Humidity Sensor which we’ll be then stored in MySQL Database.
2. We’ll interface a 0.96 inch OLED Display( Commonly known as SSD1306 OLED ) and display the readings.

Components Required

1. NodeMCU
2. DHT11 Sensor
3. 0.96 inch I2C/IIC 128x64 OLED Display Module 4 Pin
4. 10K 0.5W Resistor
5. Breadboard
6. Male to Male Jumper Wires x10

Circuit Diagram

As you can notice in the circuit illustration above, wiring up things is fairly simple.

1. Wiring up DHT11 to NodeMCU

1. Start by placing the NodeMCU on to your breadboard, ensuring each side of the board is on a separate side of the breadboard.
2. Now, place the sensor on to your breadboard besides NodeMCU.
3. Connect VCC pin on the sensor to the 3.3V pin on the NodeMCU and ground to ground.
4. Also, connect Data pin on the sensor to D3 pin of the NodeMCU.
5. Finally, we need to place a pull-up resistor of 10KΩ between VCC and data line to keep it HIGH for proper communication between sensor and NodeMCU.

2. Wiring up the OLED to NoceMCU

1. Connections are fairly simple. Start by connecting VCC pin to the 3.3V output on the NodeMCU and connect GND to ground.
2. Next, connect the SCL pin to the I2C clock D1 pin on your NodeMCU and connect the SDA pin to the I2C data D2 pin on your NodeMCU.

With that said our hardware is now in place, note that we won’t go deep into the coding aspects of the hardware but the code will be commented enough to be self- explanatory as our primary agenda is to understand MySQL and it’s interface with NodeMCU.

Setting Up the Local Development Environment

Note: The only reason we need to manually setup a development environment is that we'll be hosting and developing the project locally, no internet is needed once setup is complete.

1. Download XAMPP -> It is being advertised as being the most popular PHP development environment at the time of writing this article.

It is available for MacOS, Windows and Linux and the installation process will be similar across every OS.

Download Link: https://www.apachefriends.org/index.html

2. Click on the setup file to start the click next until you are prompted to install the application.

Note: Make sure that all the components are installed, a few of which are not necessary for our project but could be internally necessary for things to work smoothly.

3. After successful installation, run the XAMPP Control Panel from the desktop shortcut.

You'll be able to see something like below in the application window.

4. Run instances of Apache Server and MySQL by clicking on the start button in XAMPP Control Panel.

Access the phpMyAdmin Panel by clicking the Admin Button located in the MySQL row of XAMPP Control Panel. Which will then open it in a browser tab.

5. Now, we'll have access to the phpMyAdmin which can be used to create a new MySQL DB for our project.

A brief on phpMyAdmin:

It is one of the most popular applications for MySQL database management. It is a free tool written in PHP. Through this software, you can create, alter, drop, delete, import and export MySQL database tables. You can run MySQL queries, optimize, repair and check tables, change collation and execute other database management commands.

Setting Up the MySQL Database using phpMyAdmin

1. Click on "New" in the sidebar located to the left of phpMyAdmin to create a new database.

You'll be prompted to fill in two fields:

             1. Database name

             2. Collation

Note: You can lookup for what's collation on the internet or just move on, the default collation is utfmb4_general_ci which will work just fine for the project as it supports everything we'll need.

We have named our database "nodemcu_datalog" here, which can be renamed to any valid name.

2. The next step will be to create a table in our database.      

            We'll name our table as "dht11_sensorlog".

            We want our table to have four columns namely id, temperature, humidity and reading_time.

            Click on the "SQL" tab from the top bar to run a SQL query which will create a table for us, paste the query as below and click on the "GO" button at the bottom right.

CREATE TABLE dht11_sensorlog (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    temperature FLOAT,
    humidity FLOAT,
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

This will create a table with specified fields:

Here, the ID is attributed with "AUTO_INCREMENT" Which means that every time new data arrives the ID is incremented by one as compared to the data in previous row.

Also the "PRIMARY_KEY" attribute means that every value in this column is unique.

You can now see the empty table with name of columns which will be filled with sensor data.

Clicking on the Structure tab in the top bar will show you all the columns and their types as can be seen below.

Also, every time we insert some data to the table, “reading_time” field will be automatically filled with the timestamp of when data was inserted.

3. To finish this off, we will create a database user and password.

1. Click on the "User accounts" tab from the top bar.

2. Click "Add user account" and fill login information as below.

3. Click on "Go" at the bottom left which will successfully create a user.

With that said, we have completed setting up our database.

PHP Script to Insert Data into Database

We shall now start writing our php script to insert data into the MySQL DB.

To keep it simple, we’ll be creating a new text file as “phpwrite.txt”. And later, when we are done writing the code, we can change the extension of the file to “dbwrite.php”

It's time to get our hands dirty writing some PHP Code...

Hold on tight if you've never been to PHP before, you can read through the comments one by one to get the idea.

Here's what the code looks like:

<?php
// host = localhost because database hosted on the same server where PHP files are hosted
//everything prefixed with $ is a PHP variable
    $host = "localhost"; 
    $dbname = "nodemcu_datalog";    // Database name
    $username = "admin";                    // Database username
    $password = "root";                        // Database password
    // Establish connection to MySQL database, using the inbuilt MySQLi library.
    $conn = new mysqli($host, $username, $password, $dbname);
    // Check if connection established successfully
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } else {
        echo "Connected to mysql database. ";
    }
 //$_POST is a PHP Superglobal that assists us to collect/access the data, that arrives in the form of a post request made to this script.
  // If values sent by NodeMCU are not empty then insert into MySQL database table
  if(!empty($_POST['sendval']) && !empty($_POST['sendval2']) ){
        // "sendval" and "sendval2" are query parameters accessed from the HTTP POST Request made by the NodeMCU.
            $temperature = $_POST['sendval'];
            $humidity = $_POST['sendval2'];
        // Update your tablename here
        // A SQL query to insert data into table -> INSERT INTO table_name (col1, col2, ..., colN) VALUES (' " .$col1. " ', '".col2."', ..., ' ".colN." ')
                $sql = "INSERT INTO dht11_sensorlog (temperature, humidity) VALUES ('".$temperature."','".$humidity."')";
                        if ($conn->query($sql) === TRUE) {
                            // If the query returns true, it means it ran successfully
                            echo "Values inserted in MySQL database table.";
                        } else {
                            echo "Error: " . $sql . "<br>" . $conn->error;
                        }
            }
    // Close MySQL connection
    $conn->close();
?>

PHP Script to Display Latest Reading

Now, that we are done with writing to the database, we will move on to reading from our MySQL DB and showing the latest reading on the screen.

But this time, our PHP script will run on the server and return HTML which will enables us to visit the webpage and see the latest reading.

Note: As discussed earlier, our web page will not be fetching the data from the DB Asynchronously to avoid obvious complexity, We’ll have to reload page to see the latest data, we can automate the page reload process by putting in a HTML5 Meta Tag:

<meta http-equiv="refresh" content="1">

Where the content attribute represents interval in seconds of auto page refresh.

Code:

<html>
<head>
    <title>NodeMCU MySQL Datalog</title>
    <meta http-equiv="refresh" content="1"> <!-- Refreshes the browser every 1 second -->
    <!-- All the CSS styling for our Web Page, is inside the style tag below -->
    <style type="text/css">
       * {
            margin: 0;
            padding: 0;
        }
        body {
            background: url('/bg.jpeg') no-repeat center center;
            background-attachment: fixed;
            background-size: cover;
            display: grid;
            align-items: center;
            justify-content: center;
            height: 100vh;
            font-family:Haettenschweiler, 'Arial Narrow Bold', sans-serif;
        }
        .container {
            box-shadow: 0 0 1rem 0 rgba(0, 0, 0, .2);
            border-radius: 1rem;
            position: relative;
            z-index: 1;
            background: inherit;
            overflow: hidden;
                text-align:center;
                padding: 5rem;
            font-size:40px;
                color: white;
        }
        .container:before {
            content: "";
            position: absolute;
            background: inherit;
            z-index: -1;
            top: 0;
            left: 0;
            right: 0;
            bottom: 0;
            box-shadow: inset 0 0 2000px rgba(255, 255, 255, .5);
            filter: blur(10px);
            margin: -20px;
        }         
            h1{
               font-size: 40px;
            }
        p{
            margin: 60px 0 0 0;
        }
  </style>
</head>
<body>
    <?php
        $host = "localhost";  // host = localhost because database hosted on the same server where PHP files are hosted
        $dbname = "nodemcu_datalog";  // Database name
        $username = "admin";  // Database username
        $password = "root"; // Database password
        // Establish connection to MySQL database
        $conn = new mysqli($host, $username, $password, $dbname);
        // Check if connection established successfully
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
        // Query the single latest entry from the database. -> SELECT * FROM table_name ORDER BY col_name DESC LIMIT 1
        $sql = "SELECT * FROM dht11_sensorlog ORDER BY id DESC LIMIT 1";
        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
            //Will come handy if we later want to fetch multiple rows from the table
            while($row = $result->fetch_assoc()) { //fetch_assoc fetches a row from the result of our query iteratively for every row in our result.
                //Returning HTML from the server to show on the webpage.
                echo '<div class="container">';
                echo '<h1>NodeMCU Data Logging</h1>';
                echo '<p>';
                echo '   <span class="dht-labels">Temperature = </span>';
                echo '   <span id="temperature">'.$row["temperature"].' &#8451</span>';
                echo ' </p>';
                echo '<p>';
                echo '   <span class="hum dht-labels">Humidity = </span>';
                echo '   <span id="humidity">'.$row["humidity"].' &#37</span>';
                echo ' </p>';
                echo '</div>';
            }
        } else {
            echo "0 results";
        }
    ?>
</body>
</html>

Breaking down the SQL Query that fetches latest data

“SELECT * FROM dht11_sensorlog ORDER BY id DESC LIMIT 1”

SELECT * -> Used to select all the columns/fields from the database

FROM table_name -> The table from which data is being fetched

ORDER BY field_name DESC -> Retrieves data in a descending alphabetical or numerical order.

LIMIT some_number -> The number defines how many rows we want to retrieve from the result.

Once you have both the php scrips ready it’s time to change the extension of both the files to.php if it is not already.

Then you’ll have to move them into htdocs folder inside your main xampp installation folder, along with all assets like images, etc.

NodeMCU Code

1. The sketch starts by including libraries:

#include "DHT.h"
#include <Wire.h> 
#include <Adafruit_GFX.h>
#include <Adafruit_SSD1306.h>
#include <Adafruit_Sensor.h>
#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>

You must install a few of these libraries from the library manager present in Arduino IDE.

2. Then all the global variable, pin definitions and necessary classes are instantiated:

//Initializations for DHT11 Sensor
#define DHTPIN 0
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE);
float Temperature;
float Humidity;
//End Of Initializations for DHT11 Sensor
//Initializations for SSD1306 Sensor
#define SCREEN_WIDTH 128 // OLED display width, in pixels
#define SCREEN_HEIGHT 64 // OLED display height, in pixels
// Declaration for the SSD1306 display connected via I2C -> SDA + SCL Pins
Adafruit_SSD1306 display(SCREEN_WIDTH, SCREEN_HEIGHT, &Wire, -1);
//"&Wire" Declares the I2C Communication Protocol
//"-1" tells that the OLED display has no RESET Pin which is the case with our OLED Display.
//End Of Initializations for SSD1306 Sensor
// Declare global variables which will be uploaded to server
String sendval, sendval2, postData;            

3. The setup() function:

void setup() {
  dht.begin();    
  Serial.begin(115200);
  Serial.println("Communication Started \n\n"); 
  delay(1000);
  pinMode(LED_BUILTIN, OUTPUT);  // initialize built in led on the board
  if(!display.begin(SSD1306_SWITCHCAPVCC, 0x3C)) {
    Serial.println(F("SSD1306 allocation failed"));
    for(;;);
  }
  delay(2000);
  display.clearDisplay();
  display.setTextColor(WHITE);
  WiFi.mode(WIFI_AP);
//name for the access point and 8 character password
  WiFi.softAP("NodeMCU", "nodemcur");
  delay(200);
    while (WiFi.softAPgetStationNum() !=1){ //loop here while no AP is connected to this station
      Serial.print(".");
      delay(100);                           
      }
  delay(500);​

The most important snippet of code here is to configure the NodeMCU as a WiFI AccessPoint:

//configure the mode
WiFi.mode(WIFI_AP);
//name for the access point and 8 character password
WiFi.softAP("NodeMCU", "nodemcur");

Now, this WiFi AP can be accessed using the password defined. Once the client is connected to the AP, we need the IPv4 address of nodemcu at the client side to allow make POST requests.

Note: Once the AP is ready, connect the client device and extract the IPv4 address which is the actual IP address at which a POST request can be made.

The IPv4 address here is: 192.168.4.2.

4. The loop() function

void loop(){
  //read temperature and humidity
  float temperature = dht.readTemperature();
  float humidity = dht.readHumidity();
  if(isnan(temperature) || isnan(humidity)){
    Serial.println("Failed to read DHT11");
  } else {
    Serial.print("Humidity: ");
    Serial.print(humidity);
    Serial.print(" %\t");
    Serial.print("Temperature: ");
    Serial.print(temperature);
    Serial.println(" *C");
    delay(450);
  }
  // clear display
  display.clearDisplay();
  // display temperature
  display.setTextSize(1);
  display.setCursor(0,0);
  display.print("Temperature: ");
  display.setTextSize(2);
  display.setCursor(0,10);
  display.print(temperature);
  display.print(" ");
  display.setTextSize(1);
  display.cp437(true);
  display.write(167);
  display.setTextSize(2);
  display.print("C");
  // display humidity
  display.setTextSize(1);
  display.setCursor(0, 35);
  display.print("Humidity: ");
  display.setTextSize(2);
  display.setCursor(0, 45);
  display.print(humidity);
  display.print(" %");
  display.display();
  HTTPClient http;    // http object of clas HTTPClient
  // Convert to float
  sendval = float(temperature);
  sendval2 = float(humidity); 
  postData = "sendval=" + sendval + "&sendval2=" + sendval2;
  // We can post values to PHP files as  example.com/dbwrite.php?name1=val1&name2=val2
  // Hence created variable postData and stored our variables in it in desired format
  // Update Host URL here:-
  http.begin("http://192.168.4.2/dbwrite.php");  // Connect to host where MySQL database is hosted
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");  //Specify content-type header
  int httpCode = http.POST(postData);   // Send POST request to php file and store server response code in variable named httpCode
  Serial.println("Values are, sendval = " + sendval + " and sendval2 = "+sendval2 );
  // if connection eatablished then do this
  if (httpCode == 200) { Serial.println("Values uploaded successfully."); Serial.println(httpCode);
    String webpage = http.getString();  // Get html webpage output and store it in a string
    Serial.println(webpage + "\n");
  } else {
    // if failed to connect then return and restart
    Serial.println(httpCode);
    Serial.println("Failed to upload values. \n");
    http.end();
    return;
  }
  delay(200);
  digitalWrite(LED_BUILTIN, LOW);
  delay(200);
  digitalWrite(LED_BUILTIN, HIGH);
}

Code to make a POST Request to our Local Server:

sendval = float(temperature); 
sendval2 = float(humidity);  
//Construct the data string
postData = "sendval=" + sendval + "&sendval2=" + sendval2;
// Connect to host where MySQL database is hosted, with IPv4 address of our NodeMCU
http.begin("http://192.168.4.2/dbwrite.php");
//Specify content-type header
http.addHeader("Content-Type", "application/x-www-form-urlencoded");       
// Send POST request to php file and store server response code in variable named httpCode
int httpCode = http.POST(postData);  

With that said you can now visit the web page:

http://192.168.4.2/dbread.php

Note: dbread.php was the name of our webpage as we saved it in the “htdocs” folder. The filename can be updated to any valid name, and thus the URL should vary.

Hardware Images: