Using Flowcode to update a database

[chipfryer27]

Hi

Following on from this post viewtopic.php?f=4&t=2201 I thought I would write a guide to using Flowcode to update a MySql database in the hope it is of some use.

You will need an existing LAMP/WAMP installation and the guide gives an example of creating a database, the necessary PHP scripts and a Flowcode chart for an ESP32-WROOM (free target).

The examples given have been tested to work (thanks to RGV250) with the software versions shown.

Using Flowcode to Populate a MySql Database via PHP Script.pdf

(132.97 KiB) Downloaded 218 times

Update_my_db.fcfx

(17.96 KiB) Downloaded 186 times

Regards

[RGV250]

Hi,
I would just like to say a special thanks to Chipfryer as he went above and beyond for this, I have never created a database before, let alone tried to communicate with it. I would never have figured any of it out myself.
Now I have the step up I needed to progress.

Bob

[chipfryer27]

Hi

Updated document to include latest MySql and PHP

Using Flowcode to Populate a MySql Database via PHP Script.pdf

(137.87 KiB) Downloaded 243 times

Regards

Updated 24-11-23 to include syntax when creating the Timestamp

[chipfryer27]

Hi

In the newer versions of MySql (WAMP) there seems to be an issue with the included Command Line Interpreter in that it won't start when double clicked. The window briefly appears for a second or so.

Trawling the net it appears that this is a well known issue with MySql going back years. I tried pretty much everything that came up in searches relating to this but none worked for me.

You can however start it from within Windows Command Prompt by issuing : mysql -u user -p where user is your MySql user name.

Regards

[RGV250]

I made these mistakes so you don't have to.
I have to say I am no way as good at writing a document as Chipfryer but hopefully you will be able to learn from my mistakes.
Basically I thought I would include all (or the ones I can remember) of the pitfalls I came into when trying to modify the example in this post for my actual needs.

The first thing I did was edit the FC code to give the output I required.

FC edit.jpg (137.6 KiB) Viewed 15753 times

You need to add your data to the "calculation" but do not forget to change the "script" as well if you have renamed the PHP file.

So we then come to the PHP file, it is all pretty obvious but you will not believe the time I spent looking for issues.

PHP edit 1.jpg (71.81 KiB) Viewed 15753 times

The GET variables need to match what you have called them in the FC send string, the names of the global variables can be anything but need to match what you put in the bottom line (VALUES). The database table and titles in the INSERT INTO need to match what you have called them in your database/table.

Do not forget to change the database name and login details (in connect.php) if you have changed them.

PHP edit 2.jpg (27.66 KiB) Viewed 15753 times

It seems there is a limit of 3 attachments so what follows is some debugging help.

[RGV250]

If it does not work straight away (which is why I thought of doing this update) I found Wireshark a good tool.
There is a whole load of data flying around your network so I set up a filter for just what I needed. I was also only sending every minute so without it I was inundated with irrelevant stuff.

wireshark filter.jpg (15.99 KiB) Viewed 15753 times

This is the IP address of the sending device and the port you connected to in FC.

This shows the data captured and the entry in the dB.

Wireshark data.jpg (48.03 KiB) Viewed 15753 times

You can see the data in the packet match what has been entered into the DB. Ignore the NULL in the DB, that was an error on my part so nothing is sent in the data string for it.

One other thing that I did not know would happen, I had downloaded to the Pi while FC was running, I then made a mod and changed the filename and downloaded again. I was a bit puzzled why wireshark was showing 2 the new data and the old data as well. I had to stop one of the instances running.

I hope it helps.

Regards,
Bob

[RGV250]

Just for completeness I have recreated an issue where wireshark showed the packet showing the string which was as sent but the database did not update at all.
Here is the wireshark image showing the sent string.

wireshark error.jpg (23.83 KiB) Viewed 15739 times

If you look after the packet you will notice (I did not originally) that there is a response/reply from the receiving PC with "400 Bad request".
I could not find out why this was happening so I removed it and it is back working as planned, it is for debugging so not required for the finished project.

In the earlier good packet I clipped it without the good reply, if the response is good you should see "200 OK" shortly after the sent data.

Bob

[RGV250]

So following on from Chipfryers excellent example I needed to get the information out of the DB and onto a web page. I know it is not directly related to FC but I think it is relevant and hopefully it will save people hours and hours of trawling through books and the internet for what always seemed like over comlicated examples.

This example uses the my_db database that is created in the example above and shows the table on a web page, it also updates automatically without redrawing the page.

This is what it looks like.

Update example.jpg (48.53 KiB) Viewed 15709 times

and this is the code that needs to be saved as a php file in the htdocs folder as the connect.php and update_sensor_a.php files described in the example above are. It may be possible to place it elsewhere but I have only tested it there.
I think it can be saved as anyname.php but should be index.php if you want to run it from remote. I could be wrong about that as I have not got that far. Run it from the address bar as in the examples in the document above.

Code: Select all

<!-- This example has been modified from code found here.
<!-- https://www.geeksforgeeks.org/how-to-fetch-data-from-localserver-database-and-display-on-html-table-using-php/ -->

<!-- PHP code to establish connection with the localserver -->
<?php

// Username & Password
$user = 'user';
$password = '1234';

// Database name
$database = 'my_db';

// Server (localhost)
$servername='localhost';
$mysqli = new mysqli($servername, $user,
				$password, $database);

// Checking for connections
if ($mysqli->connect_error) {
	die('Connect Error (' .
	$mysqli->connect_errno . ') '.
	$mysqli->connect_error);
}

// SQL query to select data from database.
// Select one option.
//$sql = " SELECT * FROM sensor_a ORDER BY ID DESC "; //Shows ALL the rows in the table.
$sql = " SELECT * FROM sensor_a ORDER BY ID DESC LIMIT 10 "; //Shows the most recent 10 entries.
//$sql = " SELECT * FROM sensor_a ORDER BY ID ASC LIMIT 10 "; //Shows the first 10 entries.

$result = $mysqli->query($sql);
$mysqli->close();
?>

<!-- HTML code to display data in tabular format -->
<!DOCTYPE html>
<html lang="en">

<!-- Update the web page every 10 seconds -->
<meta http-equiv="refresh" content="10"> 

<head>
	<meta charset="UTF-8">

<!-- What is shown in space above address bar. -->
	<title>Display DB Example</title>
	
<!-- CSS FOR STYLING THE PAGE -->
	<style>
		table {
			margin: 0 auto;
			font-size: large;
			border: 1px solid black;
		}

		h1 {
			text-align: center;
			color: #006600;
			font-size: xx-large;
			font-family: 'Gill Sans', 'Gill Sans MT',
			' Calibri', 'Trebuchet MS', 'sans-serif';
		}

		td {
			background-color: #E4F5D4;
			border: 1px solid black;
		}

		th,
		td {
			font-weight: bold;
			border: 1px solid black;
			padding: 10px;
			text-align: center;
		}

		td {
			font-weight: lighter;
		}
	</style>
</head>

<body>
	<section>
		<h1>Sensor_a Table</h1>
		
<!-- TABLE CONSTRUCTION -->
		<table>
			<tr>
				<th>ID</th>
				<th>Sensor</th>
				<th>Value</th>
			</tr>
			
<!-- PHP CODE TO FETCH DATA FROM ROWS -->
			<?php 
			
// LOOP TILL END OF DATA
				While($rows=$result->fetch_assoc())
				{
			?>
			
			<tr>
<!-- FETCHING DATA FROM EACH ROW OF EVERY COLUMN -->
				<td><?php echo $rows['ID'];?></td>
				<td><?php echo $rows['Sensor'];?></td>
				<td><?php echo $rows['Value'];?></td>
			</tr>
			<?php
				}
			?>
		</table>
	</section>
</body>

</html>

I hope you find it useful, it would have saved me a lot of grief and giving up many a time.

Regards,
Bob

[chipfryer27]

Hi Bob

If it is saved as index it will be automatically loaded without the need to add extensions.

I created a script and FC chart to have FC retrieve a value, and updated the guide to include. I was using an ESP32-Lolin and used the onboard LED to indicate "whatever" depending on the value returned.

Whilst it worked in simulation I couldn't compile, with it appearing to cite issue with some strings. Unfortunately I then tried to update my instal to the newly recommended 5.1 and results were not good. Even System Restore doesn't resolve.

I'll PM it over to you and you can perhaps see if it compiles for you?

Regards

[chipfryer27]

Hi

Attached is an updated guide to include using Flowcode to retrieve values from the database we created earlier. Many ways to process and this example we use the Circular Buffer, and also a loop to extract values.

The example uses an ESP32-Lolin which has an onboard LED and we use this as an indicator. If the retrieved value is above a threshold level the LED illuminates and if below it extinguishes.

Again we will use a PHP script to handle our request and return values from the database.

I have had issues recently with my esp-idf installation so hopefully the attached files work for you.

Thanks again to RGV250 in testing.

Using Flowcode to Populate a MySql Database via PHP Script.pdf

(156.74 KiB) Downloaded 237 times

Request_my_db.fcfx

(28.27 KiB) Downloaded 237 times

Regards
PS
Once I resolve my esp issues I'll update to include using JSON to send/retrieve