Populating MySQL database function.

[RGV250]

Hi,
I have been plodding my way through the PHP & mySQL book recommended by Chipfryer27, definitely is a good book so thanks.
I can see that I am going to have an issue when I get to the end of the book in as much as I will need to get the data from my Flowcode routine into the SQL database.
I have seen an article where it uses Python to populate the database and I was wondering if such a thing exists in Flowcode. I do not think so so I was wondering if a component (or function?) could be created to automate the task.

The article I found is here https://pynative.com/python-mysql-inser ... ase-table/
I think all I need is the "Use Python Variables in a MySQL Insert Query" but others may be useful.

My thoughts lookin at the article is to have an "Initialise" component where it has fields for Host, database, user & password. Then an "Insert" component where it has either an array of individual fields and a return value for the status.

Bob

[kersing]

What is the target for your components? Microcontrollers? I am asking because the picture on the page you refer to shows a MySQL connector ‘component’ which translates the simple python call into a binary protocol used to communicate to MySQL.

[RGV250]

Hi Kersing,
What I am hoping to do is run the web server and the Flowcode program on a Raspberry Pi. I have a serial device that I can read the data in Flowcode but then would like to log it in the database.

[chipfryer27]

Hi

I would normally have a LAMP/WAMP install on a separate machine with my client (e.g. PIC or the like) communicating with it over a LAN, but from what I understand you have the Pi acting as both the server and client in that your Pi, as well as being server also gathers your data.

I've not done that before but perhaps you could use the Network Component to connect to LocalHost (127.0.0.1) and pass data using "GET"? Not tried it myself but you should in theory be able to access PHP scripts (subsequently accessing your MySql) via localHost.

Regards

[RGV250]

Hi,
I will have a look but I think I will still need to use a lot of code to read the data and then populate the database. I had hoped for a Flowcode function to create the row and populate it with the specified data. There will be a timer in Flowcode to read the data from the sensor so it would be a lot neater to just send it straight after along with the timestamp.

Bob

[viktor_au]

Hi
I use the Yii Framework 1.0 version.
Much more easy to design a db and set it up on RPi or Win.
Very important to design tables before coding.
Google: CSV File To DB In Yii

[chipfryer27]

Hi

I had hoped for a Flowcode function to create the row and populate it with the specified data.

That is the job of the PHP script. You just need to call providing the parameters and it will create the entry. That book provides examples of doing so using a browser.

However as Viktor points out, you will need to design your database first, and this can be as simple or as complex as you wish. I'll try and have a look over the weekend for more specifics.

Viktor, I haven't yet got around to trying Yii, I'll have a look.

Regards

[LeighM]

Another idea would be to run a linux command using the system() call
e.g. if you create a macro to take a string command, where you just have a C icon with
system(FCL_COMMAND);
Then create and pass in a command string such as (where 1,2,3 are your variable values)
"echo 1,2,3 >> log.csv"
will create a simple csv file.
If you need to go all the way to using mysql, then I believe you can get mysql to execute an immediate statement with the -e parameter,
e.g. something like
"mysql -e INSERT INTO ..."

[viktor_au]

Re: Yii
I think a version 2 is popular now.
However I still use the version 1.
DB creation is a critical step.
The Yii created www site is based on that step.
If you make a mistake in relationship design between tables you have to re-do the db design and start from zero point again.

[RGV250]

Hi,
Lots to look into, my main concern with the GET aproach is how to address the array or variables. If I was to send from the Flowcode side I know the details of the database and what variables to send.

I did look at CSV but the issue seems to be the limit to the number of rows in a heet in Libre office, if it were excel I dont think it would be an issue. MySQL seems to be the best option and will probably be easier (once I get used to it) to format / display the data.

Looking at Yii I am a bit confused as somewhere I looked it said it was a PHP framework. Does this mean I have to use different syntax etc or will what I have read in the PHP & MySQL book work. Looking at the CSV import to MySQL, I am assuming I just over write the CSV data and not need to create new rows.

Bob