Beginners Guide to Automation Episode 17

/*
https://youtu.be/3Ic7CZZ0a_U
ChilliChumps Beginners Guide to Automation, Episode 17

 Log in to your Raspberry Pi server with PuTTY, and run the following commands in the Shell:
- update: 5 nov 2020: It is possible that Apache webserver is not installed, I have added code to ensure this is installed.
- update: 6 nov 2020: added new command if you are having permissions issues writing to the SQLite database
*/

//Start  of the shell commands

sudo apt-get update
sudo apt-get upgrade

sudo apt-get install apache2
sudo systemctl enable apache2
sudo apt install php libapache2-mod-php
sudo apt-get install php-sqlite3
sudo systemctl start apache2

sudo apt-get install sqlite3

sudo apt install php-mbstring
sudo systemctl stop apache2
sudo systemctl start apache2
sudo mkdir /databases
sudo chmod +777 /databases

cd /var/www/html
sudo mkdir database
cd database
sudo wget https://bitbucket.org/phpliteadmin/public/downloads/phpLiteAdmin_v1-9-8-2.zip
sudo unzip phpLiteAdmin_v1-9-8-2.zip
sudo rm phpLiteAdmin_v1-9-8-2.zip
sudo cp phpliteadmin.config.sample.php phpliteadmin.config.php
sudo nano phpliteadmin.config.php

// ---Make changes to the config file as per the video---

// End of the shell commands


- URL for phpliteadmin: http://<rpi server ip>/database/phpliteadmin.php

- SQL for creating "IOTSensors" table:

CREATE TABLE "IOTSensors" ( ID INTEGER PRIMARY KEY, deviceName TEXT,'sensor' TEXT,'reading' INT ,'timestamp' DATETIME)


--- To ensure the permissions for the database you created is correct, use the below command in the Shell:

sudo chmod -R +777 /databases

--- NODE-RED:

- Function code for pushing data into your database:
// start of code

var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch
var theDevice = "growtent";
var theSensor = "t";

var theSQL = "INSERT INTO IOTSensors (timestamp, deviceName, sensor, reading) VALUES "
theSQL = theSQL + "('" + sqliteTimeStamp + "', '" +  theDevice +"', '" + theSensor + "', " + msg.payload + ");";
msg.topic = theSQL;

// end of code