The last articles were about the setup of a LoRa-Gateway with TTN and a temperature sensor with LoRa. TTN stores the received data in the application only for a short time and the received pakets cannot be retrieved afterwards. Therefore a separate server or a service is needed to store the received data permanently. TTN itself has many interfaces to other storage services, where the received data can be stored by the LoRaWAN network. This article is about programming your own small HTTP application server with PHP, which stores the data in a MySQL database on your own web space or web server. A small table shows the data from your sensor.

Note: For this project a webspace or webserver is needed.

Requirements

Before you can even receive your data on your own web space, you need a sensor and your own gateway or a public gateway nearby. Additionally the sensor must already communicate with TTN and the payload must be decoded accordingly by TTN. A example tutorial with the LHT65 is available here.

How does the communication work?

The communication between TTN and your own website works very easily via HTTP or HTTPS requests. If a new data packet arrives at TNN via LoRaWAN, TTN starts a HTTP request and sends your data in JSON format with HTTP-POST to your own web server. For this purpose a simple PHP script is called on the web server, which decodes the data from JSON and stores it in a MySQL database.If you access the website with your browser, the LoRa data records are read from the MySQL database and displayed.

Web server & database setup

Before the setup can be started in TTN, the web server and the associated database must be configured.First you have to download the ZIP folder with the PHP-Files. Then you have to unpack the files from the archive. Open the file "config.php" with a text editor. The file looks like in the following screenshot:

The following parameters must be adapted to the database server accordingly:

DATABASE_USERNAME: Username of Database
DATABASE_PASSWORD: Password of Database
DATABASE_NAME: Name of Database
DATABASE_HOST: Adresse of Database Server (often "localhost")
WRITE_LOG:If this parameter is set to the value "true", all traffic from TTN is additionally stored in a text file (log.txt) on the web server

Note: With most web space providers, a MySQL database must first be created in their administration interface. Usually, the necessary access data is also displayed there or sent by e-mail.

Once the "config.php" file has been edited, all three files (config.php, ttn.php & index.php) can be uploaded to the web server. This is usually uploaded via a FTP client like Filezilla, WinSCP or similar. More information and the access data can be obtained from the respective provider.

MySQL Database Setup

After the PHP files have been uploaded to the web server, the database structure must be imported. To do this, log in to PhpMyAdmin from the respective provider. The already existing database is basically empty. In PhpMyAdmin there is the tab "Import". Here the file "sql_template.sql" is selected and uploaded.

After clicking on "OK", the import process should be completed and the table "data" should be available. Afterwards, the domain can already be called from the web space with the browser. Here, as shown in the following screenshot, the following error message should appear:

If this is not the case or if other error messages appear, there is an error on the database server or in the "config.php" file.

TTN Integration Setup

In the existing application, click on the "Integrations" tab. Clicking on "Add Integration" opens a new window where various services are listed

Set up HTTP integration

After clicking on "add Integration" the tool "HTTP Integration" is selected in TTN. After selecting this menu item, a new window opens again with some input fields as shown in the following screenshot:

Parameters for HTTP Forwarder

After HTTP integration is selected, TTN will ask for some parameters:

Process ID: A process ID must be assigned, but can be freely selected.
Access Key: The "default key" can be selected
URL: The server address (starting with http or https) including the PHP script (/ttn.php)
Method: POST
Authorization: Remains blank
Custom Header Name: Remains blank
Custom Header Value: Remains blank

Afterwards the integration is created with "Add Integration". From now on TTN starts a call to the previously defined URL for each received record. The record is passed as POST in JSON format and looks like the following example:

Data processing with PHP

The PHP script "ttn.php" takes the received data from TTN and saves it into the predefined MySQL database. The script is developed in such a way that only the most necessary parameters of TTN are stored into the database. The script and the database serve as a template and can be adapted to the corresponding parameters or extended. If the "WRITE_LOG" function is activated, each request from TTN is also written with the raw data in a line of the log file.

Output

The index file generates a small table from the MySQL database. Although the MySQL database stores all received records, the table automatically always outputs the latest record. Again, this is just an example that can be customized by the user to suit their needs.

Security information

LoRa data packets are always encrypted with AES up to the application server. If the HTTP integration is created with an HTTPS URL at TTN, the data transfer is also considered encrypted here. But beware: Anyone who knows the URL to the "ttn.php" file can also save data to the database. In productive use it is recommended to at least check the device ID to make sure that the data really comes from TTN. TTN also offers the option to include security parameters in the header. This can also be checked with PHP.