In this tutorial you will:

- Create a MySQL database for logging telemetry data on a Raspberry Pi

- Log telemetry data from wireless sensors to the database

- Create a web page to view the data

Step 1 - Create a MySQL database for logging telemetry data

Open up a terminal session on the Raspberry Pi and type (or paste) the following commands one at a time:

sudo apt-get update

sudo apt-get install apache2

sudo apt-get install php

sudo apt-get install php-mysql

sudo apt-get install mariadb-server

sudo apt-get install python3-mysqldb

sudo python -m pip install mqsql-connector-python

sudo apt-get install python-numpy

sudo service apache2 restart

Now you’re ready to create the telemetry log table. At the Raspberry Pi command prompt, type the following:

$ sudo mysql

This will take you into the command-line interpreter of MySQL. Here you can issue SQL commands to MySQL. Standard Linux commands will no longer work when you’re in the MySQL interpreter. Type exit or quit to return to the Linux command prompt.

Next, create a new database by typing the following:

mysql> CREATE database sensor_logs;

Next, create a user and password, which you use again later, and assign all privileges to that user.

Create the user and password as follows:

mysql> CREATE USER 'dblogger'@'localhost' IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON sensor_logs.* TO 'dblogger'@'localhost';

mysql> FLUSH PRIVILEGES;

mysql> quit

Now log back in using the user you just created, and password 'password' and create the new table:

mysql -u dblogger -p (push Enter and you will be prompted for the password you used above)

mysql> USE sensor_logs;

mysql>CREATE TABLE telemetry_log (rec_id INT NOT NULL AUTO_INCREMENT, device_id CHAR(50), type int,value CHAR(50), date DATETIME, unit_of_measure CHAR, PRIMARY KEY (rec_id));

Check that the table was created successfully by typing the following:

mysql>SHOW TABLES;

As shown in below, you should see the new table listed in the database.

MariaDB [sensor_logs]> SHOW TABLES;

+-----------------------+
| Tables_in_sensor_logs |
+-----------------------+
| telemetry_log |
+-----------------------+
1 row in set (0.00 sec)

Last , create an index on theDatecolumn so that you can search and sort easily by date:

mysql>CREATE INDEX idx_date ON telemetry_log(date);

Now make sure that you’re happy with the new table you’ve created (see below):

mysql>SHOW COLUMNS IN telemetry_log;

Log out of MySQL by typing quit.

If you made some mistakes and want to delete the table and start again, use theDROPstatement:

mysql>DROP TABLE telemetry_logs;

Developing a sensor logger

The next step is to log data into the database from the wireless sensors. You accomplish this by using Structured Query Language (SQL) syntax. SQL is made up of commands that closely resemble spoken language. Here are some examples of SQL statements. The following SQL statement will return the first name, surname (last name), and grade of all students with a surname of Evans:

SELECT firstname, surname, grade FROM student WHERE surname = “Evans”

The following SQL statement creates a new row of data in the student table:

INSERT INTO student (firstname, surname, grade) VALUES (“Jonathan”,“Evans”, “A”);

The following SQL statement changes the grade of all students that have a surname of Evans to B:

UPDATE student SET grade = “B” WHERE surname=”Evans”

Notice the use of surname in the precedingWHEREclauses. Earlier in the chapter, we describe how to create an index to sort data and how to make searching data quicker. Surname would be an ideal candidate for an index. If surname was not indexed, the database engine would scan through the entire table looking for students with the surname of Evans. However, by creating an index on thesurnamecolumn, the database engine will be able to sort through and pull out the relevant records much more quickly. However, don’t create an index on many columns because every index adds overhead to the performance of the table. This is why planning your database tables, fields, and indexes is important.

Your database logger application will use two SQL statements: anINSERTstatement to log the data and aSELECT statement to retrieve the data that will be displayed on a web page.

You use a Python program called rflog_db.pyto log the telemetry readings to the database. You can download the software using the following commands:

$ cd ~

$ git clone https://github.com/JemRF/Data-logging.git

$ cd Data-logging

Run the program:

$ pythonrflog_db.py

The program prints readings to the screen and also writes those values into the database as shown below.

pi@raspberrypi:~/Data-logging $ python rflog_db.py
Mon May 28 00:28:55 2018 a86BUTTONOFF
Processing data : DevId=86,Type=1,Value1=NOFF,Value2=0
INSERT INTO telemetry_log (device_id, type, value, date, unit_of_measure) VALUES ('86', 1, 'Open', NOW(), '')
Telemetry 86,1,Open,C,logged
Mon May 28 00:28:55 2018 a86BUTTONOFF
Mon May 28 00:28:55 2018 a86SLEEPING-
Mon May 28 00:28:58 2018 a86SLEEPING-
Mon May 28 00:29:00 2018 a45SLEEPING-
Mon May 28 00:29:14 2018 a90AWAKE----
Mon May 28 00:29:28 2018 a61TMPA18.60
Processing data : DevId=61,Type=3,Value1=18.60,Value2=0
INSERT INTO telemetry_log (device_id, type, value, date, unit_of_measure) VALUES ('61', 3, '18.60', NOW(), 'C')
Telemetry 61,3,18.60,C,logged
Mon May 28 00:29:28 2018 a61SLEEPING-

$ mysql -u dblogger -p (then type the password)

$ mysql> USE sensor_logs;

$ mysql> select * from telemetry_log;

The rows that were inserted into the database will be listed in a table, as shown below.

MariaDB [sensor_logs]> select * from telemetry_log;

+--------+-----------+------+-------+---------------------+-----------------+
| rec_id | device_id | type | value | date | unit_of_measure |
+--------+-----------+------+-------+---------------------+-----------------+
| 6 | 86 | 1 | Open | 2018-05-28 00:28:55 | |
| 7 | 61 | 3 | 18.60 | 2018-05-28 00:29:28 | C |
+--------+-----------+------+-------+---------------------+-----------------+
2 rows in set (0.00 sec)

Creating a dynamic web page

In this section, you create a web page that displays the telemetry log you created in the previous section on a webpage. Earlier, you install PHP and Apache. You use both in this section to create a web page to display your telemetry data. Check that the Apache installation worked. Open a browser either by typing startx and opening a browser on your Raspberry Pi, or by using another PC connected to your network, and type in the IP address of your Raspberry Pi as the URL.

You should see a web page that saysIt works!, as shown below.

You can find the IP address of your Raspberry Pi by typing the following:
/sbin/ifconfig
The output will look something like this:
wlan0 Link encap:Ethernet HWaddr d8:eb:97:18:16:ef
inet addr: 192.168.0.2 Bcast:192.168.0.255 Mask:255.255.255.0
Your IP address is the number provided afterinet addr:, which in this case is 192.168.0.2.

Now that you have your web server up and running, you work with the following two PHP files to retrieve and display the data on a web page:

• dblogger.php: DisplayTelemetryLog displays the last x readings stored in the log.
• index.php:Calls DisplayTelemetryLog.

PHP files need to be located in the web server's directory(/var/www),so you need to copy both the files into that directory as follows:

$ cd ~/Data-logging

$ sudo cp dblogger.php /var/www/html

$ sudo cp index.php /var/www/html

$ sudo rm /var/www/html/index.html

Now browse to your Raspberry Pi IP address again and you should see the latest telemetry displayed on the web page as shown below.