Showing MySQL data on an LCD display using ESP8266

DIY Internet of Things for everyone

Showing MySQL data on an LCD display using ESP8266


In my last guide i made an sensor node that records Temperature, Humidity and Pressure directly to an MySQL database. i did this using an ESP8266 (NodeMCU) with an BME280 sensor attached to it. This sensor node stores his data nicely in to an database. But now the data has no use yet.

In this guide i will explain how we can retrieve this data from the MySQL database and make an LCD display show these values. We are going to do this using an ESP8266 programmed using the Arduino IDE and an I2C based LCD display.

Parts required

-NodeMCU (Other ESP8266 based controllers will also work)

-16×2 LCD Display

-I2C LCD interface (LCM1602 IIC)


-Jumper wires


NodeMCU/ESP8266I2C LCD Interface


Just like in the last guide we are going to 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:

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

-LiquidCrystal I2C (version 1.1.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!

 * 2022
 * ESP8266 to MySQL tutorial
 * u are free to edit this code
#include <LiquidCrystal_I2C.h>
#include <MySQL_Generic.h>
#include <Wire.h>

// Lcd adress
LiquidCrystal_I2C lcd = LiquidCrystal_I2C(0x3F, 16, 2);

// Database Adress
IPAddress server_addr(86, 85, 233, 156);
uint16_t server_port = PORT;

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

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

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

// Query
String query = String("SELECT timestamp,temperature,humidity,pressure FROM ") + database + "." + table + (" ORDER BY id DESC LIMIT 1;");

// Default values
String datetime = "";
double temperature = 0;
double humidity = 0;
double pressure = 0;

// Create an instance of the cursor passing in the connection
MySQL_Connection conn((Client *)&client);
MySQL_Query sql_query = MySQL_Query(&conn);

void setup()

  // Begin WiFi section
  Serial.println(String("Connecting to ") + ssid);
  WiFi.begin(ssid, pass);

  while (WiFi.status() != WL_CONNECTED)

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

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

void loop()
  // Sending query
  if (conn.connectNonBlocking(server_addr, server_port, user, password) != RESULT_FAIL)
    row_values *row = NULL;
    String Pdatetime = "";
    float Ptemperature = 0;
    float Phumidity = 0;
    float Ppressure = 0;

    // Initiate the query class instance
    MySQL_Query query_mem = MySQL_Query(&conn);

    // Print the query

    // Execute the query
    // KH, check if valid before fetching
    if ( !query_mem.execute(query.c_str()) )
      Serial.println("Querying error");

    // Fetch the columns (required) but we don't use them.
    column_names *columns = query_mem.get_columns();

    // Read the row (we are only expecting the one)
      row = query_mem.get_next_row();

      if (row != NULL)
        Pdatetime = row->values[0];
        Ptemperature = atof(row->values[1]);
        Phumidity = atof(row->values[2]);
        Ppressure = atof(row->values[3]);
    } while (row != NULL);

    // Export
    datetime = Pdatetime;
    temperature = Ptemperature;
    humidity = Phumidity;
    pressure = Ppressure;

    // Now we close the connection to free any memory
    Serial.println("\nConnect failed. Trying again on next iteration.");

  // Showing results in serial
  Serial.println("Datetime: " + datetime);
  Serial.println("temperature: " + String(temperature));
  Serial.println("humidity: " + String(humidity));
  Serial.println("pressure: " + String(pressure));

  // Showing results on lcd
  lcd.setCursor(0, 0);
  lcd.setCursor(0, 1);
  lcd.print("Temp: " + String(temperature) + " " + (char)223 + "C     ");
  lcd.setCursor(0, 1);
  lcd.print("Humi: " + String(humidity) + " %     ");
  lcd.setCursor(0, 1);
  lcd.print("Pres: " + String(pressure) + " hPa     ");


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.

// 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";

Wrapping Up

Now power on the board, Everything should now work!

You now made an ESP8266 project that retrieves and data from an MySQL Database and shows it on to an LCD display! Feel free to leave a comment below when u need extra information or troubleshooting help.

Here is an demonstration picture and video of the project:

The Hardware
Demonstration Video

Tags: , , , , , ,

Leave a Reply

Your email address will not be published.

WordPress Appliance - Powered by TurnKey Linux