ESP8266 and MYSQL: anyone did it?

For Flowcode users to discuss projects, flowcharts, and any other issues related to Flowcode 7.

Moderator: Benj

MJU
Posts: 429
Joined: Wed Nov 07, 2007 6:51 pm
Location: Antwerp Belgium
Has thanked: 105 times
Been thanked: 94 times
Contact:

ESP8266 and MYSQL: anyone did it?

Postby MJU » Sun Apr 02, 2017 11:03 pm

Did anyone use a ESP8266 to send data to a MYSQL database in Flowcode?

I remember a discussion a few years ago where people were trying to accomplish this.

I have a MYSQL database with fixed IP address, I want to store some data (like temperature that I measure with an controller) wireless into this database.
Anyone has an example he wants to share?

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Mon Apr 03, 2017 10:12 am

Hello,

I'm going to explore this again shortly. There are lots of different ways to do it but I'm not sure which one is best.

1) On a http request just output the values in raw text form or in XML form, a separate web server running a cron task can perform the page requests, parse the values and stuff into mySQL.

2) Use the module as a client which posts values to a matched server on a regular basis.

3) Use a system such as JSON or MQTT to lower the amount of communications data and standardise parsing, works with methods 1 or 2.
These users thanked the author Benj for the post:
MJU (Mon Apr 03, 2017 6:38 pm)
Rating: 5.26%
 

MJU
Posts: 429
Joined: Wed Nov 07, 2007 6:51 pm
Location: Antwerp Belgium
Has thanked: 105 times
Been thanked: 94 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby MJU » Mon Apr 03, 2017 5:14 pm

Benj wrote:Hello,

I'm going to explore this again shortly. There are lots of different ways to do it but I'm not sure which one is best.

1) On a http request just output the values in raw text form or in XML form, a separate web server running a cron task can perform the page requests, parse the values and stuff into mySQL.

2) Use the module as a client which posts values to a matched server on a regular basis.

3) Use a system such as JSON or MQTT to lower the amount of communications data and standardise parsing, works with methods 1 or 2.


Me neither.
Do you have an idea how to send data to a txt-file on the server using the ESP8266?

For me the ESP8266 component is great but I'm still hoping to get it send email for me, or send data to a SQL database.
I would hope that anyone that has these features in Flowcode would share it..

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Mon Apr 03, 2017 5:46 pm

Hello,

I know a fair bit of PHP so I might use the PHP function Curl to get a HTML page or simply some data values from the ESP8266 module. Then once the data is loaded in I would have to parse through it converting the ASCII string into binary values which could then be stored into mySQL. If you want to play around with PHP then I highly recommend the easyPhP software, it's free and seems to work well out of the box. Though don't use this as is for a online server as it's a bit too open and easy to take over and control with the default settings.

I've got a project comping up to do this kind of thing so I'll post what I generate though of course there are many ways to do the same thing. I have some existing hardware somewhere which I tried to dig out on the weekend but so far it's eluding me. Maybe I have to build up another board :roll:
These users thanked the author Benj for the post:
MJU (Mon Apr 03, 2017 6:38 pm)
Rating: 5.26%
 

MJU
Posts: 429
Joined: Wed Nov 07, 2007 6:51 pm
Location: Antwerp Belgium
Has thanked: 105 times
Been thanked: 94 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby MJU » Mon Apr 03, 2017 6:37 pm

Benj wrote:Hello,

I know a fair bit of PHP so I might use the PHP function Curl to get a HTML page or simply some data values from the ESP8266 module. Then once the data is loaded in I would have to parse through it converting the ASCII string into binary values which could then be stored into mySQL. If you want to play around with PHP then I highly recommend the easyPhP software, it's free and seems to work well out of the box. Though don't use this as is for a online server as it's a bit too open and easy to take over and control with the default settings.

I've got a project comping up to do this kind of thing so I'll post what I generate though of course there are many ways to do the same thing. I have some existing hardware somewhere which I tried to dig out on the weekend but so far it's eluding me. Maybe I have to build up another board :roll:


I'm very curious for some examples.

I learn most of things I can get my hand on.
So anything that kind is welcome for me to investigate.
Even how you would Curl data to a file on a server is great to have a look at (for me).

Thanks BenJ

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Wed Apr 05, 2017 12:27 pm

I've made some improvements to the ESP8266 component which are now available here.

viewtopic.php?f=63&t=18581&p=80576#p80576

Now that it's working very reliably I'll put together a small PHP example which requests the served page and stores the results into a mySQL DB.

I'll let you know once this is up and running.

MJU
Posts: 429
Joined: Wed Nov 07, 2007 6:51 pm
Location: Antwerp Belgium
Has thanked: 105 times
Been thanked: 94 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby MJU » Fri Apr 07, 2017 5:14 pm

Benj wrote:I've made some improvements to the ESP8266 component which are now available here.

viewtopic.php?f=63&t=18581&p=80576#p80576

Now that it's working very reliably I'll put together a small PHP example which requests the served page and stores the results into a mySQL DB.

I'll let you know once this is up and running.


I'm anxious.

Meanwhile I'm trying to send email via a PHP script on an server.
More about this later..
These users thanked the author MJU for the post:
Benj (Fri Apr 07, 2017 5:17 pm)
Rating: 5.26%
 

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Fri Apr 07, 2017 5:18 pm

Sorry it's taking a while, I'll see if I can get a basic example working over the weekend to share with you.

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Fri Apr 07, 2017 7:31 pm

Example 1 - Server Requests Data From ESP8266

This example is favorable if you have a mains powered ESP8266 module and want to drive everything from a remote server, e.g. Linux PC or Raspberry Pi.

Here is the Flowcode project for reference.
WStation.fcfx
(47.1 KiB) Downloaded 206 times


First of all I installed easyPHP on my windows laptop.

Eventually I'll just install Apache, PHP and MySQL on my Linux server but for now I use my Windows laptop to do everything.

Once EasyPHP is installed and running you go to the following URL to access the server config.

http://localhost/home/index.php

Open the MySql Admin and create a new database, I named mine ESP8266.

I then created the following php files, you will need to extract the zip archive into the "C:\Program Files (x86)\EasyPHP-DevServer-14.1VC11\data\localweb" folder or the server root folder if simply using Apache.

ESP8266.zip
(3.4 KiB) Downloaded 191 times


You can then access the site using the following URL.

http://localhost/ESP8266

PageRequest.jpg
PageRequest.jpg (61.79 KiB) Viewed 7767 times


Details of the files are shown below.

index.php - main page with various includes, this is our portal into the site and the page we will use to view everything.
links.php - some html links between pages - shown at top and bottom of index
dbopen.php & dbclose.php - Some DB management - open and close the DB
InitTables.php - Initialise and clean the tables
ReqData.php - Requests data from the ESP8266 and adds to MySQL DB
ShowData.php - Shows the contents of the MySQL DB

Please note my PHP code is very quickly bashed out, you would ideally have a lot more error checking and data parsing to accommodate things like the URL not loading etc. Also as i'm on a private server I use root with no password to access the DB. Of course do not do this if your allowing inbound communications from the web.

On the linux server I would do a page request using a Cron task to setup an automated regular poll of the ESP8266 module and it's sensors.

Page request URL from Cron task.
http://localhost/ESP8266/index.php?p=ReqData


Example 2 - ESP8266 Posts Data to Server

This example is better suited to when you need to be able to power down the ESP8266 module to save power for example when running from a battery. This time when the ESP8266 module is powered on it makes a request to a remote HTTP server on the network.

Example Flowcode project.
WStationClient.fcfx
(44.64 KiB) Downloaded 194 times


This has an additional PHP file, PutData.php which collects the page request from the ESP8266 and stores the URL parameters in the DB.

In the Apache configuration file look for this line.

Listen 127.0.0.1:80

And change to this to accept incoming connections from all IPs on the network.

Listen 80

You may also have to open the incoming TCP port 80 on the windows firewall.

ESP8266Client.zip
(3.8 KiB) Downloaded 192 times



Displaying the data

Once you have the data in MySql you can then use a PHP library to give you some nice charts and tables.

This looks like a nice library.
http://www.fusioncharts.com/php-charts/

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Mon Apr 10, 2017 5:51 pm

I've now installed LAMP (Linux, Apache, MySQL, PHP) and SSH onto my Linux Mint server and left a battery powered ESP8266 communicating to it every 2 and a half minutes all last night.

This morning I had a full nights worth of results sat in the DB so this is now working really well for me.

Here are instructions for installing LAMP on Linux Mint should you need them.

https://community.linuxmint.com/tutorial/view/486

The SSH is not strictly necessary but it's nice to be able to use the Linux device remotely from another computer, e.g. my Windows laptop.

I also shared the Apache root folder as a Samba share and this way I can directly edit the servers PHP/HTML files from my Windows Laptop over the network. This took a bit of doing but got there in the end.


You can probably do the exact same thing on a raspberry Pi.

Please note that I have made a minor change to the ESP8266 component as there was a long delay (30-60 seconds) when establishing client communications which has now been fixed.

WLAN_ESP8266.fcpx
(25.03 KiB) Downloaded 174 times


I also put a double check into the initialise page so that you can't accidentally delete the contents of the table, as I managed to keep doing before adding the double check. I've now added this to the php zip archives above.

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Mon Apr 10, 2017 5:54 pm

Meanwhile I'm trying to send email via a PHP script on an server.


I've come across this problem before and there is a way to make it work reliably. I'll see if I can dig out how I did it in the end.

From memory you have to provide the from field in the email header and some email clients will double check this matches the IP used for communications before allowing the email to be received. If your sending from an IP without an attached domain name then set the from field as the IP Address. Probably the public WAN IP rather than the local network IP.

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Wed Apr 12, 2017 1:42 pm

My next step is to create some charts using the PHP librarys and I'm maybe going to have a look at JSON to try and hook into a MQTT server something like mosquitto, however it's a lot to take in :D

https://projects.eclipse.org/projects/t ... .mosquitto

chipfryer27
Posts: 164
Joined: Fri Jun 06, 2014 3:53 pm
Has thanked: 99 times
Been thanked: 63 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby chipfryer27 » Fri Apr 21, 2017 3:19 pm

Hi

Last year I use an ESP8266 to send / receive data to mySQL. Unfortunately I'm abroad at present and I don't have any access to previous files etc, but from memory I sent a GET request to a PHP script.

If you are still needing help with this when I return late May, let me know and I'll dig out the code.

Regards

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Tue Apr 25, 2017 10:22 pm

Here is an updated version of the php software. I now have a filter by day and a new chart page with min/max and averaging.

Data.jpg
Data.jpg (87.21 KiB) Viewed 7063 times


Graph.jpg
Graph.jpg (69.16 KiB) Viewed 7063 times


ESP8266.zip
(3.58 MiB) Downloaded 156 times


Remember to edit the includes/dbOpen.php file in a text editor to point to your MySQL table with the correct user details.

User avatar
petesmart
Valued Contributor
Valued Contributor
Posts: 395
Joined: Thu May 06, 2010 11:42 am
Location: Sydney, Australia
Has thanked: 190 times
Been thanked: 140 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby petesmart » Wed Apr 26, 2017 10:50 am

Hi,

I have been following your work with interest.

In parallel I have been working with dsPic miac publishing results to and IOT portal called thingspeak.. I'm currently operating in the Free mode which allows you to post at intervals of 15 second. The paid for version will allow updates every second – it doesn't look too expensive for this type of service.

The rig that I'm using is a dsPic miac and and ESP8266 module. I've had it working reliably over the last few days – click herehttps://thingspeak.com/channels/254968 to see the results.

Things speak is quite interesting as it is been purchased by Matlab which means you get access to all of the analysis tools. It even has add-ins to allow triggers to Twitter and all those other wonderful social media platforms.

It can operate either in private or public mode – the link above is the public option. Note I haven't set up the add-ins on the public yet, for example it has a nice feature that you can add Google gauges – see graphic attached

The API is quite straightforward and has a couple of options
Channels and Charts API

Use the REST and MQTT APIs to update ThingSpeak™ channels and to chart numeric data stored in channels
ThingSpeak is an IoT platform that uses channels to store data sent from apps or devices. With the settings described in Channel Configurations, you create a channel, and then send and retrieve data to and from the channel. You can make your channels public to share data. Using the REST API calls such as GET, POST, PUT, and DELETE, you can create a channel and update its feed, update an existing channel, clear a channel feed, and delete a channel. You can also use the MQTT Publish method to update a channel feed. Learn more about when to Choose Between REST and MQTT while updating a channel.

MATLAB® analysis and visualization apps enable you to explore and view your channel data. ThingSpeak enables you to interact with social media, web services, and devices.

REST API
Use REST API calls to create and update ThingSpeak channels and charts
MQTT API
Use the MQTT API to update ThingSpeak channels


Thought I would share – interested in your thoughts.

All the best,

Pete
Attachments
thingspeak.PNG
(77.08 KiB) Not downloaded yet
These users thanked the author petesmart for the post (total 2):
Benj (Wed Apr 26, 2017 10:54 am) • chipfryer27 (Wed Apr 26, 2017 3:21 pm)
Rating: 10.53%
 
sorry about that Chief!

User avatar
Benj
Matrix Staff
Posts: 14307
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4400 times
Been thanked: 4126 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby Benj » Wed Apr 26, 2017 11:06 am

Hi Pete,

I've tried ThingSpeak in the past and failed so you have done better than me. It was a fair few years ago so they might have gotten better or I may have just been doing something fundamentally wrong.

Either way it's looking really good. Interesting it's now owned by Matlab and really great that you can now do it all from the MIAC. The gauges look very nice and remind me a lot of SCADA setups.

I've moved house now so no longer have my MIAC powered underfloor heating system so think there is probably a new project in the wings to take it's place :D Maybe a grid tie solar power system or solar heater all linked to a MIAC of course :wink:

Social media integration is interesting. I've made a note of a site called if this then that which seems quite powerful but not really explored the possibilities of it yet.

https://ifttt.com/

If thingspeak can do the same thing already then that's also very interesting.

I think I'm a bit old for Twitter and don't really see the point but maybe nice for notifications e.g. your solar heater has overloaded and your house is on fire!!! Email probably works just as well in my opinion.

Really I did this to keep my php and mysql muscles flexed as I've not really had a need to do any for a while. It's not the best code but it works and is simple for a closed network setup.

User avatar
petesmart
Valued Contributor
Valued Contributor
Posts: 395
Joined: Thu May 06, 2010 11:42 am
Location: Sydney, Australia
Has thanked: 190 times
Been thanked: 140 times
Contact:

Re: ESP8266 and MYSQL: anyone did it?

Postby petesmart » Wed Apr 26, 2017 11:41 am

Hi Ben,

Thanks for your thoughts... If you think you're too old for Twitter, then I'm a dinosaur! :lol:

I started off by looking at ifttt.com – agree it looks quite good however I couldn't get my head around interfacing it to miac at the time – now that I know a bit more, I'll go and have another look.

I haven't got too far into the remote triggers and management of events however I did come across this on the website.
Monitor and Act on Channel Inactivity Using ThingSpeak Apps

ThingSpeak™ provides apps to transform or visualize data or trigger an action. This example shows how to monitor your ThingSpeak channel activity using these ThingSpeak apps: React, MATLAB® Analysis, ThingHTTP, TalkBack and ThingTweet.

Consider a scenario where a pressure sensor monitors boiler pressure. The pressure sensor continuously feeds data to a ThingSpeak channel. If the channel stops receiving the pressure data, you receive notification about the event.

You can use React to trigger ThingTweet to tweet about the failure. In addition to the tweet, you can also use React to trigger TalkBack to issue the command to the boiler system to shut it down. While React can directly trigger ThingTweet and ThingHTTP, React cannot directly queue commands to TalkBack. Also, React cannot trigger multiple apps when it detects an event, but it can use MATLAB Analysis to trigger multiple apps as shown in this diagram:

Image

ThingSpeak Apps

Transform and visualize data or trigger an action
You upload data from the web or send data from devices to a ThingSpeak™ IoT platform channel. Use these apps to transform and visualize data on ThingSpeak channels or trigger an action.

Concepts
Actions
ThingTweet App
Use your devices to tweet alerts and messages.

TweetControl App
Respond to tweets that contain specific trigger words.

TimeControl App
Link other apps to the TimeControl app to perform or schedule repetitive actions.

React App
React to the data in a channel when specific conditions are met.

TalkBack App
Enable any device to act upon queued commands.

ThingHTTP App
Interface a device with web services and APIs.

Analytics
Overview of MATLAB Analysis App
Analyze the data in a channel.

Overview of MATLAB Visualizations App
Visualize the data in a channel.

Plugins App
Create plugins to display custom visualizations of data in a channel.


I'm going to keep on delving into things speak and its apps as it looks quite interesting for remote supervision and monitoring of various hi power transmitters and energy control systems

All the best

Pete
These users thanked the author petesmart for the post:
Benj (Wed Apr 26, 2017 12:01 pm)
Rating: 5.26%
 
sorry about that Chief!