Populating MySQL database function.

[chipfryer27]

Hi

Just dug out my copy of the book. It's been a while........ I really need to go through things again to refresh, but you will be able to use Get/Post to populate your database without resorting to writing CSV.

I'm hoping I still have some example files kicking around, it will save me having to relearn

Regards

[RGV250]

Hi,
This is the bit I am having trouble with and why I thought the CSV way was the only good option. All the books / web links I have found seem to just show GET / POST transferring between web pages and files, not directly from a program IE Flowcode.

Bob

[chipfryer27]

Hi

The next few lines are just an example of how I use FC to connect and send variables, perhaps to ThingSpeak or similar. Usually I would be using WiFi (maybe an ESP8266) and be connected to my SSID.

I would then use ClientConnect to connect to "api.thingspeak.com" but this could also be any IP address and I see no reason not to use 127.0.0.1 (localhost) if server was on same machine (maybe using a Pi).

I'd then send the following "GET /update?api_key=aaabbbccc&field1=xx HTTP/1.1\r\nHost: api.thingspeak.com\r\n\r\n"

update?api_key=aaabbbccc is the script being called with aaabbbccc being my unique identifier (depends on requirements of what you connect to)
field1 is the variable I want to update
xx is variable value

Obviously the script / variables / etc being called would be dependent on your db design etc, and there is more to it than the above, but illustrates the main points.

Basically the "Get" above mimics what the book tells you to type in your browser, so to speak.

I've probably confused you more

Regards

[RGV250]

I've probably confused you more

Probably, I will have to try to digest it in the hotel bar tonight, I am off to Harrogate for my other hobby of woodturning so leaving the laptop behind.

Bob

[chipfryer27]

Hi

I need to do a new LAMP install. Hope to do this over the weekend.

Regards

[chipfryer27]

Hi

How was Harrogate? I used to live there in early 90's whilst working at the RAF base and very much enjoyed the place. I can imagine it being expensive now though

Anyway, I've reread the bits in the book of interest and I think I remember how I got it to work ages ago. Unfortunately I've no examples I can find of things.

I created my database with whatever fields I wished, then I think I created a script that added a new record with values, which were passed to the script from my "GET"

Roughly, I had Sensor_DB as my database with fields ID and Value, e.g.:-

Sensor_DB
ID Value

a 12
b 34
c 56
etc

My GET string I would send from my PIC would then include ID=a&Value=12 (or thereabouts <s>). Hopefully I can create an example over the weekend, if not too busy.

Regards

[chipfryer27]

Hi

I'm having to relearn this step by step as I'd forgotten how much I forgot

Using the book as a reference I've installed the server, PHP and MySql and have them configured to play nice together. The test steps confirm all is good with that.

I then created a database and added a table with two columns, and confirmed. From the MySql command line I then inserted new data and also created a script to do the same. Calling the script from the command line successfully inserted new test data.

I'll try and get further tomorrow.

Regards

[RGV250]

Hi,
The show was good as always. Harrogate is expensive so I stayed just outside Leeds. I stopped off to have a look round Bletchley park on the way back which was quite interesting.

I am getting confused with the GET command, I would have thought you would need to use POST to send data from the PIC and GET to read it from PHP?
The bit I cannot get my head around is the relationship between the 2 programs. I can understand it if a file that is referenced in GET which is why I thought CSV would be good. I can follow that one writes to it and the other reads it. Even if I use LocalHost I still cannot get my head around how it gets the data as it is just a variable running in a separate program.

Bob

[chipfryer27]

Hi

I'd love to visit Bletchley. Such an important place in history.

Regarding GET/POST, basically it is to do with HTML form handling and what we don't see behind the scenes.

GET/POST refer to how "requests" are made with each having their own pros and cons. This link will explain far better than I, but briefly GET makes the request in the URL whilst POST makes it in the body. It's sort of how we transport the information the script will use.

https://www.w3schools.com/php/php_forms.asp

If you try each example (GET/POST) in Firefox with More Tools>Web Developer Tools enabled, and viewing Network you will see what happens when you submit the form. In the GET, you will see the key-pairs being sent in the URL, in POST you won't.

With the limited resources of a microcontroller, it is far easier in my opinion to just send a GET string.

At the server, it really doesn't care much how you make the request as you (as developer) can do as you wish with the information once received. Both GET and POST are SuperGlobals meaning the information is available for use in any function you wish.

Your script in the server will accept your values (key-pairs) and update the associated database with the values.

It might be easier to think on it as a form on a page. You would fill in the requested details and submit. GET/POST is just how we transfer that information to the server script for processing. However instead of completing a form we will just send the information directly to the script using the GET format.

Just to confuse you more, there is nothing to stop you sending data to a script using POST, and that script using the data as parameters in a database search, returning the search results back to you. Sounds like a GET but you used POST

The PHP script running on your server is the brains and can fully interact with your database using the information you provide. It's up to you to decide what the script does. First it will connect to the specified database then Insert/Delete/Update etc as per your wish.

Regards

[RGV250]

Hi,
I did have a look with the Firefox tools etc and was even more confused, a lot of information there.
I am pretty sure I am OK with storing the data in the DB once I get it but this is where I hit a blank. From all the things I read they all require a form, file or web page etc to extract the data from. It is this bit that I am having the issue with.

In your PIC example do you have any code in the PIC exporting the variables, even if not there is only one program. In my case, if I used localhost, the script would still need to know where the data resides.

For me it feels a bit like the old tee shirt


Bob