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