ESP8266: Logging data in a MySQL database

The following post shows how it is possible to store data that comes from the esp8266 into a MySQL database.
I assume that for this to be done, we have the following architecture:
– A LAMP server (Linux, Apache, MySQL and PHP) or a WAMP server (on Windows).
– ESP8266 running the NodeMCU firmware with LUA language that periodically calls the LAMP server and it passes the data on the HTTP request query string.

There are other ways of passing data, for example, using MQTT and with Node-Red storing data into MySQL, for example, but that is another story.

Creating the database:
Let’s create the database. Access the LAMP server, and with an user with enough privileges, execute the mysql client command line utility. Normally the user for this is the user root.

1. Create an user for accessing the database server:

mysql> create user 'esp8266'@'localhost' identified by 'secretpwd';
Query OK, 0 rows affected (0.06 sec)

2. Create the database:

mysql> create database esp8266;
Query OK, 1 row affected (1.40 sec)

3. Grant the necessary privileges to the user for accessing the database engine:

mysql> grant usage on *.* to 'esp8266'@'localhost' with max_queries_per_hour 10000;
Query OK, 0 rows affected (0.07 sec)

On the above statement, the ‘with max_queries_per_hour’ is optional, and it can be left out if there is no reason to limit resource access:

mysql> grant usage on *.* to 'esp8266'@'localhost';
Query OK, 0 rows affected (0.02 sec)

And let’s make sure that the esp8266 mysql user can do whatever it needs on the esp8266 database:

mysql> grant all on esp8266.* to 'esp8266'@'localhost';
Query OK, 0 rows affected (0.02 sec)

So now we can quit, and access the newly created database to create, in the case of this post, a single table for storing data.

4. Let’s access the new database:

mysql -u esp8266 -psecretpwd esp8266

And create the table:

mysql> create table DataTable ( logdate DATETIME , field VARCHAR(64), value BIGINT);
Query OK, 0 rows affected (0.70 sec)

I’ve added a column named field so, that, if we want in the future, to query data based on field data.

Storing data:

Storing data is done through the following php page called store.php:

This code only stores if the query parameter on the request has a parameter named heap.

  $servername = "localhost";
  $username = "esp8266";
  $password = "secretpwd";
  $dbname = "esp8266";

  $now = new DateTime();
  parse_str( html_entity_decode( $_SERVER['QUERY_STRING']) , $out);

  if ( array_key_exists( 'heap' , $out ) ) {
  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);
  // Check connection
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

  $datenow = $now->format('Y-m-d H:i:s');
  $hvalue  = $out['heap'];

  $sql = "INSERT INTO DataTable ( logdate , field  , value) VALUES ( '$datenow' , 'heap', $hvalue )";

  if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
  } else {
    echo "Error: " . $sql . "<br>" . $conn->error;


The esp8266 code:

The esp8266 code is based on code of some of my previous posts, but basically if we have WIFI connection the following code segment (stored on a file called ping.lua) is called periodically:

conn=net.createConnection(net.TCP, false)
conn:on("receive", function(conn, payload) print("Get done.", payload) end )
conn:send("GET /store.php?heap=" .. node.heap() .." HTTP/1.1\r\nHost: xx.xx.xx.xx\r\n" .. "Connection: keep-alive\r\nAccept: */*\r\n\r\n")

Viewing data:

We can view data stored on the MySQL database with the following code (list.php):

$servername = "localhost";
$username = "esp8266";
$password = "secretpwd";
$dbname = "esp8266";

  parse_str( html_entity_decode( $_SERVER['QUERY_STRING']) , $out);

  $sqlq = "Select * from DataTable ";

  if ( array_key_exists( 'field' , $out ) ) {
    $sqlq = $sqlq . " where field = '" . $out['field'] . "'" ;

  echo $sqlq;
  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);
  // Check connection
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

  $result = mysqli_query( $conn , $sqlq );

  if ( $result->num_rows  > 0 ) {

  echo "<table border='1'>
    <th>Log Date</th>

  while($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['logdate'] . "</td>";
    echo "<td>" . $row['field'] . "</td>";
    echo "<td>" . $row['value'] . "</td>";
    echo "</tr>";
  echo "</table>";


If we call the list.php file without parameters, it will show all the available data. Otherwise it only shows what is specified on the field query parameter, for example:


29 thoughts on “ESP8266: Logging data in a MySQL database

  1. hi! first thanks for this post!
    this is actually what I’m looking for. I want to have an ESP with and temperature sensor sending info to an php/mysql…
    But I’m still not familiarized with the ESP programing… do you have any tutorial on how to program the ESP?

    Thanks so much!

  2. Thank you

    the web have many many tutorials about save data in a LAMP server , but this is the most readable and basic for beginners
    this is just what I was looking for, a simple example of how to start

  3. Hello, thank you very much , works for me perfectly , but I would like to know how to write real-time data from a sensor in the MySQL database , I have dealt with FOR and WHILE, but does not work .
    Thank you.

    1. I’m afraid that I can’t understand your question… What exactly do you want to achieve? Is the sensor connected to an ESP8266? In what language are you programming the ESP882 if used?

      1. I will capture the esp8266 a laser readings: (it is a production accountant), those readings would save them in real time in a MySQL database, I guess it must be programmed in lua and php.

    1. You need to command the ESP8266 from Arduino, either by serial port or using I2C. It really depends on what firmware the ESP8266 is running.

  4. I’m programming in Arduino ide.I have created a webpage at my local and wanted to post the sensor value to the nodeMcu .Can u help me in writing post command

    1. There are a lot of examples available on the web. Just search for “arduino http post”. I’m sure you’ll be able to find an example that fits your requirements.

  5. Hi, great information. But do you have idea how i can send my temperature data to MYSQL? My current set up is NODEMCU DevKit with DHT22, its already working through Arduino IDE i can see the result in Serial monitor. My problem is that how can i send the data wireless to MySQL? Thanks much

  6. hi, i’m trying to send data of my sensors to an application where my senors will be connected to a ESP8266 and i’m not able to send data to the database from esp8266. could you please help me.

  7. Thanks for the awesome post!
    Can you please explain in laymen terms what this following line of code does?
    Googling for individual terms hasn’t helped much. Would be awesome if you can walk me through what this does:
    parse_str(html_entity_decode($_SERVER[‘QUERY_STRING’]) , $out);

    1. The line picks up the query string, decodes the entities and then the parse_str process the line and transforms the variables into an array of variables. Something like a=12&b=14 to [ [a,12] , [b,14] ].
      Check out the php documentation, it will explain better.

  8. Hello,
    nice work :)))
    i want to ask about a topic which is similar to your work, i want to know how to store data ( it is not from a sensor or something like that, it is just a simple data) from Wemos D1 R2 ( with ESP8266) in local database using Wamp server and Arduino IDE
    please Help me and thanks in advance.

    1. Can you explain more what you require? The above PHP code works for anything that can call the web site…

  9. Hi PrimalCortex

    I read your tutorial and would like to know if the following is possible : I have two ESP8266 connected to two Arduino Pro MInis. The idea is to have the ESP8266 do the wireless lifting. Now your post explains almost what I need to do : Is it possible to :

    1.) Have the ESP8266’s read directly from a MySQL database online ? I have a PI unit doing this. It directly connects with mysqlconnector or similar. It doesn’t use POST/GET.

    2.) Have the ESP8266 then forward “instructions” to the Arduino Pro connected to it which will in turn respond which the ESP8266 will take up and then send to the database.

    Any help would be greatly appreciated.

    1. Hi:

      Regarding your questions:

      1) As far I’m aware there is no direct MySQL client for the ESP8266, so you need a “translation” bridge between something that the ESP8266 can do, like GET/Post, MQTT, and so on.

      2) For this checkout jeelabs esplink or for using the esp8266 as a bridge between the arduinos.

      Hope this helps.

  10. Hi, just a simple question if you dont mind, but I couldnt understand your storing data step through store.php, where and how are you exactly calling this file, and what exactly do you mean by “This code only stores if the query parameter on the request has a parameter named heap.”?

    1. The store.php file must be defined on the calling URL: for example executing will call the page for storing data.

      as you can see on the code:

      if ( array_key_exists( ‘heap’ , $out ) )

      the page only stores data if a parameter called heap is passed on the url.

      Modify as you require to store other parameters.

  11. hello ,
    i am trying to upload the data fetched using an EM-18 attached to the esp8266, i tried to go according to the instructions, but wasn’t able to do much, what changes would i need to do specifically to the code to upload it the database?

  12. Hi,
    I want to program ESP8266 with arduino and send DHT22 sensor data to mySQL database. If you can help me figure out what should I do I’ll be thankfull.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.