Imagine a distributed cloud spread all over your campus. The main server is sitting somewhere on the Internet (or Intranet) and maintaining a big database having a huge number of tables. The tiny upload nodes are running on ESP8266 or ESP32 and are connected to several sensors which are uploading data at regular intervals to the respective tables of the database.
The huge campus is spread over several zones and there are several WiFi hotspots available all across. Our ESP nodes are intelligent enough to connect whatever WiFi is available at that zone and then start uploading data. After uploading data at a particular time interval it goes for deep sleep. Being a low power device (2.5 volts to 3.3 volts) combined with deep sleep state the power consumption is pretty manageable on LiPo (or latest LiFePo4) battery for a couple of days. For maintaining time we can manage the ESPs to connect the NTP (National Time Protocol) server and get time from there or we can have a small DS1307 RTC (Real Time Clock – $0.5) connected with each ESP.[In the prototype section we have both the models]
The server is having Linux-Apache-MySql-PHP installed. Linux is not compulsory but Apache-MySQL & PHP is essential to have the system works. It gets data all across the nodes. Each table has an ‘id’ field which is an ‘auto_increment’ type. For those tables which are having the same periodicity can be joined on the ‘id’ fields.
The output of the data is available for view on ChartDirector or any other program of yours.
The cloud is ready now!
Those who have seen my “Cloud computing with Thingspeak.com” for them this is just that now you are having your own ‘ThingSpeak.com server” which is completely under your control. Nobody can access your data unless you allow. Two levels of authentications are used – One with the WiFi connection and the other with the MySQL database connections. Many more levels of authentications can be inserted with the power of Apache-PHP combinations. All these authentications are hardcoded/burnt inside the sketches, therefore, it’s pretty safe!
Principles of operation
There is some claimant on the Internet who claims to have successfully integrated MySQL with Arduino but to be frank I’ve tried many of them and though few alpha libraries did work on Ethernet connections none of them worked on WiFi connections. So I discarded all of them. But the HTTPClient.h library on ESP32 and ESP8266HTTPClient.h library on ESP8266 is very powerful and works flawlessly for GET connections. These two libraries are now in tandem with a small PHP script does the trick for uploading the MySQL tables for us and that’s the crux of this project – Tapping the Power of PHP scripting by ESPs! Thus you can do many more things with this library combined with PHP.
Once the data is available in MySQL database with date-time stamp along with id field, it’s now easy to create & display output program – either by using PHP or by using the powerful ChartDirector which is available in many languages – VB+, C, Java, PHP and many more. Download your choice and deploy the database connection to get on line data output.
A. Create the MySQL database & tables
Experts create MySQL database & tables by the stroke of a few SQL lines on a terminal but for me, it’s rocket science. Hence so I rely on PHPMyAdmin for creating my database and tables. Here’s the out of PHPMyAdmin – by clicking here and there [in the root user mode] I do create my MySQL database and table.
B. Create the PHP scripts for uploading
We create two tiny PHP scripts of barely 25 lines – one for each table: c1z1.php and c1z2.php, these files are attached herewith. These files are to be inside the document directory of Apache (browser page sort of things) so that the ESPs can open them from outside. The ESPs open these pages, add sensor data to it and then post it so that the data goes straight to the MySQL tables.
C. Install ChartDirector
It has many flavors – C++, .net, java, asp /com/vb, Ruby, ColdFusion, Python, and PHP. The download page is available here – www.advsofteng.com/download.html
Select your flavor & operating system and then download. Read the instructions, it’s very easy. So is easy to create a chart out of it. It’s not fully open source. You may register it for a fee to get rid of a small banner at the bottom of the chart otherwise it’s neat and clean.
I’ve used a simple DHT-22 sensor on ESP8266 (WeMos D1) board and a DHT-22, RTC-DS1307 & TGS2600 (general purpose smoke sensor) module on an ESP32 to upload to two different tables of MySQL database hosted on my remote server at – berapi.ddns.net