Populating MySQL database function.

[chipfryer27]

Ha ha

I remember those days well, when everyone pestered me for help in pretty much everything...

I'm actually heading to site soon and should have spent the day resting (or should that be REST-ing ?). However I can sleep when I die so played around a little more.

I have a MySql database with a table called "sensors" containing two fields, ID and Value as per a previous post.

Today I created a script that if called using "GET" updates the table with the values in the key-pairs.

Say my database is on my LAN with an IP of 192.168.1.2
My fields were called ID and Value
My script was called "update.php"
I want to send the values of 1 and 17 to fields ID and Value respectively

In my browser I would type the following

http://192.168.1.2/update.php?ID=1&Value=17

My browser calls the script update.php and passes the key-pair values (ID=1 and Value=17) to the script. The script then creates a query to update the database with the values.

Behind the scenes your browser is doing some t-shirt magic which we don't really care about (if you do, then enable the developer mode I spoke of). What we do like though is that it is very easy to get a microcontroller such as a PIC to send a GET.

I would have the PIC, perhaps using an ESP8266 connect to the server (192.168.1,2) using FC component macros

Then I'd send the following string (where x and y are the values I wish to send) again using FC

"GET /update.php?ID=x&Value=y HTTP/1.1\r\nHost: 192.168.1.2\r\n\r\n"

The server script will take the values in the key-pair and update the database.

It's a bit rough just now so when I GET a chance (see what I did?) I'll tidy it up and POST (ouch) so you can see what is going on. I haven't tested it yet with a PIC, but as it works with a browser I'm not expecting any real bother.

Regards

[RGV250]

Hi,
I appreciate the time you are putting into this, I just wish I could understand it quicker:-)
I think it is slowly sinking in but what might be causing me problems is how I have everything set up. It looks like in all examples etc everything is modular/seperate where my webserver, database and flowcode program are all on the same device, a Raspberry Pi.
If I am on the right track I would need to run "GET /update.php?ID=x&Value=y HTTP/1.1\r\nHost: 192.168.1.2\r\n\r\n" on the Pi. Do I use TCP_IP Socket Open, Send and socket close to send the string. I should probably use Localhost? which will update the update.php with the values.
Then the Webserver is running the update.PHP script using Localhost again to populate the DB with the values that were written to update.php?

Bob

[chipfryer27]

Hi

Until you become familiar with things I'd recommend using a "PC" (LAMP or WAMP). Once you have the hang of things then port to the Pi. A PC has far greater resources than the Pi so will be far quicker. A few months back I tried to run a version of ThingsBoard on a Pi. It was so slow as to be totally useless. Reflashing as "headless" it did run, but again was very, very slow. Not good when you are trying to learn how to use something.

I wanted to try TB on a Pi as I wanted a cloud free way to store data captured, and as I have a few Pi's kicking around I thought I'd try to use one. Unfortunately I didn't manage to get TB to work, so I thought on again using PHP & MySql but hadn't yet got around to it. This has been the prompt I needed to refamiliarise myself with things.

As PHP & MySql are used to create interactive websites it isn't any surprise that almost all examples from any source have the server being accessed from elsewhere using a browser. It's literally made for it. However as it's easy for any microcontroller running Flowcode to generate a "GET" string, we can connect IOT devices (or whatever) to the database. Assuming the Pi runs some form of "LAMP" then it can accept data from "devices" on the Pi using localhost/127.0.0.1 as address, or potentially any device on the same LAN or globally via the internet.

Potentially Flowcode App Developer on a WAMP machine could also use localhost/127.0.0.1 as the address to update a MySql database it hosts.

To create the connection, it depends on what chip you are using and how you connect. Examples in the WiKi but from memory I think the Pi has a component in which you would open a socket, connect to address, then send your string (the GET string from above posts).

Hopefully things will become clearer when I post an example.

One of the best, if not the only benefit of working through the night is that your girlfriend can't moan at you for enjoying a beer at breakfast. Not just for festivals......

Regards

[RGV250]

Hi,
I am using a PC at the moment but as it will be running 24/7 when it is done I fugured a Pi is the best option. I did try ESP32 but could not connect the sensor to it.
App developer would be great if it could run on a Pi but I do not think that will happen.
I used to enjoy a beer after nights as it was effectively your evening. I did sleep well after it but glad I am not doing nights any more.

Bob

[Steve-Matrix]

App developer would be great if it could run on a Pi but I do not think that will happen.

We hope to have some news regarding this next month. Watch this space!

[RGV250]

We hope to have some news regarding this next month. Watch this space!

Oooh, are we allowed to open our chrismas presents early

[chipfryer27]

Hi

Knocked up a quick test using an ESP32, mainly because I can just plug it into my laptop and go.

All it does is connect to my WiFi then enters a loop.

In the loop I gather data (just create a random value)
I then create the string I will send (GET blah blah)
I then obtain the length of the string
I open a connection
I send the string
I close the connection
I wait 30 seconds before repeating

Updates the MySql database without any issue.

When I get time I'll tidy things up and post.

Regards

PS
Good news about FAD and Pi

[RGV250]

Hi,
I have an ESP32 as well although the laptop I used with it has failed so I have not done a lot with it. If I try your example on the PC will I need WiFi on the PC or just a connection to the router WiFi from the ESP. I have a refurbished surface pro 6 on order so will have a laptop later.

Bob

[chipfryer27]

Hi

You only need a PC / Laptop to actually program the ESP. Once programmed the ESP will connect over your WiFi.

I forgot to switch off the ESP after I tested and I now have a few hundred entries in my database

Regards

[chipfryer27]

Hi

Just a quick update.

When using a microcontroller to send stuff over the internet, you need to include delays to allow for latency. The latency may only be a few hundred milliseconds which you won't notice but to a microcontroller ice-ages have came and went.

You need to allow for such in your design and typically 200 - 450mS isn't unreasonable for you to send, the server to process request and respond. On your own LAN this should be much, much faster.

Also, if using cloud based services you may have restrictions on how much data and frequency of submission you can send, depending on your subscription. Free services may only allow an update every 10 - 15 seconds.

You of course are only restricted by the speed of your infrastructure if hosting your own.

With nothing to hand to attempt to measure such, and no real desire to, I just shrank delays considerably to see what happened. I changed my chart to autoincrement a value and send some random data. This was a check to see if I "lost" anything.

The PC I'm using as a server isn't particularly powerful being a SFF device and the LAN being used is shared amongst other traffic.

I had no problem at all sending over 30 messages per second, losing none in the process. I'm sure I can go faster but no desire really to see where I fail

Regards

EDIT....
Couldn't resist, dropped the delays down to uS and easily doubled the above