Populating MySQL database function.

[chipfryer27]

Hi

A LOT does depend on how you create your database.

A DB is nothing more than a group of records, and the record can have as many fields as you like.

Say you created an Address Book. Each record may contain the following fields:-

Name / 1st Line Address / 2nd Line Address / Town / etc etc. I'm sure you get the idea.

When you create a new record, you populate those fields. Within the DB you use commands to create / find / delete / list etc. The PHP scripts automate each command, so that calling it will create / find / delete / list etc based on the parameters passed.

To add a new record FC would simply call the script and pass the parameters. PHP does the actual job of creating the record.

I'll try and dig out an example.

I'm no expert in PHP/MySql, but thanks to FileMaker I'm no stranger to databases either (great program until crApple bought them over, in my opinion). I spent almost a week trying to get a PIC talking to a DB via an ESP8266 with no success about a decade ago, and reluctantly asked a friend for help (he was extremely busy). I hate him....... It took him about 45 minutes from the end of the call to get a simple PoC running on his server that I could populate from anywhere. That was my intro to PHP/MySql

Regards

[RGV250]

Hi,
I sort of understand the idea behind databases, I was a bit annoyed when the company I worked for sent a few people on a course but I got left behind (face didnt fit etc).
I am pretty sure once I get the basics of the device writing to it I will be on my way. My plan is to have a Raspberry Pi sampling the sensors data and then writing the data to a database on the same device which will then be the server. Hopefully this is not a stupid idea, I am aiming for this setup to save power etc.
My thoughts at the moment are to have a simple counter running in Flowcode and the database just recording the count to get started, once I have that I am pretty sure I can move foreward to logging the actual data.

Bob

[chipfryer27]

Hi

Pi's certainly have there place, but until you get to grips with things it may may be better to use a LAMP/WAMP on a separate desktop/laptop as you are more likely to succeed. In my opinion, and no doubt many will disagree, learning the basics of PHP/MySql is better if hosted on a "PC" as it is a far more powerful machine and less likely to get hung up.

Once you have the hang of things migrating to a Pi shouldn't be any great issue.

Regards

[RGV250]

Hi,
I am doing it all on a PC as the book doesn't cover the Pi and the MySQL version is different, once I do get it working I can then try to migrate it. I wonder if I run the Flowcode in debug it will be similar to maving both on the Pi.

Bob

[viktor_au]

Hi
I am not sure how much time do you have.
The Yii framework is based on PHP but the benefit of it is: it creates a lot of code automatically.
You have to fine tune this code (if you need to) later.
I did the project a few years back and don't remember the details.
In my case Arduino saved to sd card the CSV rows.
Syntax of CSV should be exactly the same as MySQL table syntax.
If it is, you create the cron job and the table data will be aupdated automatically.
It is a bit of a work and some time to be spent.
I think it is the OOP- object oriented programming.
Please read more about yii.

[RGV250]

Hi,
Thanks for all the replies, I have found a couple of PHP examples using fgetcsv() to read from a CSV file to a MySQL database so that seems to be the thing to try. The good thing is that I can understand them:-)
I am still working through the PHP & MySQL book and I think Yii will add to the learning curve so I am not going down that route yet. There are Yii examples to do the same thing so I may look at that later.

Regards,
Bob

[chipfryer27]

Hi

Unfortunately I will need to reinstall MySql etc on a machine so it will be later in the week before I get a chance to create any example, but I'm very sure the book gives plenty.

If I remember correctly it first gets you up and running with PHP and MySql giving test scripts to call from a browser, then the next few chapters are about what you can do within the scripts (e.g. calculations or the like) and again tests scripts you can call from a browser. It then goes into the database, before showing how scripts can populate and interact with the DB.

Regards

[RGV250]

Hi,
I think I am almost there, I can write to the CSV file in Flowcode and then use PHP to populate a database. It is all with simulated data at the moment but looking good. I just need to get it all working seamlessly now.

I have found another book "PHP 5 in easy steps", it is quite an old book but less than £3. One thing I did not know was possible is to send emails with PHP.

Bob

[chipfryer27]

Hi

Good to hear you are making progress and it should be possible to update the db without first writing to CSV. FC should be able to send a string with your variables that PHP will use to update.

Regards

[RGV250]

Hi,

FC should be able to send a string with your variables that PHP will use to update.

That is the bit I am having a problem with, I was only using the CSV file to pass the variables but I only want one row which is proving rather difficult, may not be possible.
The only other way I found was to use Python inside PHP which then seemed to get very messy so would be interested if you are able to get PHP to read variables directly.

Bob,