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.

<?php
  $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;
  }

  $conn->close();
}
?>

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:connect(80,"xx.xx.xx.xx")
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):

<?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'>
    <tr>
    <th>Log Date</th>
    <th>Field</th>
   <th>Value</th>
   </tr>";

  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>";
}
$conn->close();

?>

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:

http://address/list.php?field=sensor1
Advertisements

23 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. Pingback: ESP8266 – Logging data in a backend with end to end encryption | Primal Cortex's Weblog

  4. 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.

    • 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?

      • 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.

    • 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.

  5. 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

    • 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.

  6. 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

  7. 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.

  8. 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);

    • 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.

  9. 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.

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

  10. 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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s