Using Flowcode to update a database

[chipfryer27]

Hi

Below is another example of retrieving a value. This time the target is a Raspberry Pi and we use some of the Advanced String Functions to confirm our received data is valid and then extract the value. Note that whilst this runs in simulation I haven't had the chance to try on my hardware yet.

Request_my_db_RPi.fcfx

(28.58 KiB) Downloaded 205 times

Regards

[chipfryer27]

Hi

Recently I was experimenting with SIM7600 and SIM7670 modules to interact over the cellular network with limited success. To aid debugging I thought it would be beneficial if I "talked" to my own server as that way I could see what was arriving, especially with regards to Headers.

Having retired my previous WAMP machine, I needed a new server and thought this time to create a RAMP (Raspberry Pi, Apache, MariaDB, PHP). Also, as I intend to experiment with MQTT, I installed the Mosquitto Broker too.

The RAMP process was pretty straight forward, and even easier for Mosquitto (Flowcode WiKi provides a good guide).

I've updated and attached my previous guide to include the RAMP and MQTT install. The previous scripts and Flowcode charts in the above posts should still work with little or no modification.

I've also attached extremely basic Flowcharts to Subcribe to the MQTT Broker and also Publish. Again the WiKi has good examples of using MQTT. In my tests I successfully sent MQTT messages to turn On/Off a LED.

Flowcode with MySQL Databases & MQTT.pdf

(274.32 KiB) Downloaded 20 times

Subscribe.fcfx

(24.26 KiB) Downloaded 18 times

Publish.fcfx

(18.86 KiB) Downloaded 20 times

Regards

[mnfisher]

Many thanks to Iain for this detailed & comprehensive document...

I tried to follow along and install on a raspberry pi - and hit a few minor niggles on the way (with the pi rather than the instructions)

So:
After:
sudo apt update
sudo apt upgrade

The next command I tried - I got 'sudo is disabled' - rebooting the pi solved this.
Attempting to install apache2 - I got a heap of errors the first of which was an unresolved refence to xtrx-dkms (followed by lots of others)
This was fixed by doing 'sudo apt remove xtrx-dkms' )

Reinstalling apache2 - I could then connect and access the apache webserver

To install php - I needed sudo apt install php8.2 libapache2-mod-php8.2 php8.2-mbstring php8.2-mysql php8.2-curl php8.2-gd php8.2 -y (note it has a linefeed if copied from the pdf - and also the final '.zip' is removed)

Logging in to mysql - I must have mussed up the password creation - and just needed 'sudo mysql'
(sudo mysql -u root -p - just printed the help message)

So far - I haven't got the php to work - I 'think' the directory /val/www should be /var/www or maybe /var/www/html for connect.php and I also needed to use the command sudo nano /var/www/update.php (rather than sudo nano update.php /var/www)

More as I progress...

Martin

[chipfryer27]

Hi

Earlier in the document I mention that it's best to type in the commands rather than copy to avoid accidental line feeds etc.

Briefly, to install apache

sudo apt install apache2 -y

To make the User and permission changes

sudo usermod -a -G www-data $USER
sudo chown -R -f www-data:www-data /var/www/html
sudo chmod -R 770 /var/www/html

To reboot to apply the above

sudo reboot

To install PHP

sudo apt install php8.2 libapache2-mod-php8.2 php8.2-mbstring php8.2-mysql php8.2-curl php8.2-gd php8.2-zip -y

To install MariaDB

sudo apt install mariadb-server

To secure MariaDB (no password for root yet). Follow prompts and set

sudo mysql_secure_installation

Access MariaDB by

sudo mysql -u root -p (password you just set)

Install the PHP-MySQL connector

sudo apt install php-mysql

I'll double check my PHP scripts as I did slightly modify them prior to posting. My original scripts used for testing the RAMP had some extra code to echo back certain parameters etc as I was creating things, these were then removed but I may have accidentally removed something I shouldn't have..

The "connector" script should be in /var/www as it contains login details and it isn't a great idea to have this in a folder visible to the outside. The update script makes a call to this and is stored in /var/www/html (visible).

Regards

[chipfryer27]

Hi

Thanks to Martin for picking up on a typo on page 29. I had mistakenly typed val instead of var for a directory ( /val/www/ instead of /var/www). I have corrected the problem and reloaded the document.

Regards

[chipfryer27]

Hi

Thanks again to Martin. It appears that the auto-correct in Word had changed the spelling of the MQTT Broker from mosquitto to mosquito and I didn't catch before posting.

The above guide has been updated to reflect, but if I haven't caught all then is should be mosquitto (with two "t"'s) rather than a bug (mosquito).. Ouch!

Regards