Introduction: Aquarium Controller III - Logging to MySQL

By

Did you read my instructions about the Aquarium Controller I and II?
Ok, this is a continuation and can not be used separately. (only in changed form)

I would like to show how I store data from the Aquarium Controller into a mySQL database.

I use an mySQL database and PHP from the Internet provider "Strato.de" / "Strato.com". This allows me to view the data from anywhere on the Internet via PC, smartphone or tablet.

To realize that I use an Arduino Uno with an Ethernet shield and send the data to my online database. I call this unit AquaController Logger.

To send the data from the AquaController to the logger I use radio units. I call this unit AquaController Logger-Slave.

I still had some RFM12b radio modules and an Ethernet-shield from an older projekt in the drawer ;)

This project describes my system that sends data from the AquaController to the AquaController-Slave via I2C and than via Radio to the AquaController-Logger which sends the values via Internet into my online Database.

The Auqa Controller IV (next Instructable) describes my way to visualize it in charts.

UPDATE: New php-scripts. If you are using a newer php-version. The scripts are tested with php 7.0.13

Step 1: What You Will Need

- 1x Domain / WebSpace with ...

... 1x mySQL Database and PHP included

- 1x Arduino Uno as "AC-Logger"

- 1x Ethernet-shield

- 1x Arduino Nano as "AC Logger Slave"

- 2x RFM12b radio units (on ebay)

- 2x Breakout shields* Digital Smarties (see picture above)

- 6x 10k resistor if you don´t use the breakoutshield from Digital Smarties

- 6x 4k7 resistor if you don´t use the breakoutshield from Digital Smarties

- 1x prototype board (hole grid board)

- 1x A case you like

- wires, cables, ribbon cable*

- screwing connectors*

- male & female plug connectors*

- some tools

You can get all parts on eBay or Amazon, for example.

Step 2: Create a Table in Your Database

First of all we need a table in our database. See file above (acsensorwerte.sql)

After you logged in to your domains administrations section you will find an admin tool called phpMyAdmin. You can run the sql script in the SQL window of the phpMyAdmin (copy & paste). (See picture)

It´s impotant to use the original fieldnames and tablename, because these names are used in php-scripts and in the whole project!

Attachments

Step 3: We Need Access to Your Webspace With an FTP-Client

After that we need some PHP Scripts to login into the database and receive the values from the AquaControllerLogger and send them into the table of the database.

After you register a domain you'll get some web-space. To access this webspace I use an FTP Client like WinSCP. This is an open source tool and a good choice. You can get it everywhere with many different languages. (Link to british WinSCP site)

For an access to your webspace you need your login data. You got username and password from you provider when you create an FTP-user. Please refer the dox how to do that.

After installing and starting WinSCP a Window appears (see 1st picture) Fill in your personal data and connect to your web directory (root).

You need information about:

  • what kind of FTP you can use (FTP /SFTP) and the port (21/22)
  • the name of the server (it´s like 'ftp.yourprovidersserver.com')
  • your username (it's like 'ftp@yourprovidersserver.com')
  • your password (it's like upps... no example ;) but make it strong!

You are successfull, I know it, so please create 4 directorys and use the same names like mine. It's important, because we use them in the scripts. Ok, we need:

  • ac_includes
  • ac_scripts
  • ac_styles
  • ACMesswerte

Step 4: Edit Your Php-scripts

Download the four files above and rename three of them from filename.txt to filename.php.

Open db.inc.php in your editor and define your Host, the name of the database, your username and your password you got from your domain / provider.

If you use Strato it looks like:

define('DB_HOST', 'rdbms.strato.de:');

define('DB_NAME',"DB1234567");

define('DB_USER',"U1234567");

define('DB_PASSWORD',"yourPassword");

Then open SaveMessToMySQL.php in the editor and define the KEY and the USERAGENT. You can use, what you want, but remember your choice you need it later! I already define something, you can use it as an example, if you want.

You can let the third php-file unedit, but it´s in german. You can translate titles and headers it if you want.

Attachments

Step 5: Upload Php-files to Your Directorys

Start your FTP-Client (WinSCP) and login to your webspace.

Change the directorys as you need and upload the files:

"db.inc.php" to "ac_inc"

"default.css" to "ac_styles"

"SaveMessToMySQL.php" to "ACMesswerte"

"ViewData.php" to the root-directory

You can change this name from "ViewData.php" to "index.php" if you use your domain only for this.

"www.yourDomainName.xxx" will show you your values without any other inputs.

Step 6: The Logger

Ok, now we need our Arduino Uno, the Ethernet-Shield and the radio unit RFM12b.

Be aware, the RFM12b unit needs 3,3V NOT 5V! That is why we use the resistors as voltage dividers.

Put the Ethernet Shield on the Arduino Uno. Then I take a small piece of hole grid, which is as wide as the Uno. I soldered pin headers, so I can plug the board to the sockets between power supply and D8-D13. Look at the photo above. The 4k7 Resistors are under the radio unit, the 10k resistors on the right. Make all connections as shown in the drawing. Be sure, you use the 3,3V output for the radio unit!

Attention: Perhaps you have already wired an RFM12, in my projekt you must use D8 instead of D10 for SS (Select), because, we need digital output D10 for the Ethernet shield!

And an antenna is needed – a 165mm piece of wire can be used (a quarter wavelength at 433MHz). For 868 MHz you need a 82mm long piece of wire.

Step 7: The AC-Logger Slave

The Logger Slave will be connected with the AquaController only via I2C. So we need our Arduino Nano, an hole grid circuit board and for example an breakout board for another radio module RFM12b. You can also use the radio unit with the resistors like before. I only want to show you both possibilities ;)

For the connection between AquaController and AC-Logger-Slave we need an 4-wire cable. 5V, GND, Data and Clock. (5V to 5V, GND to GND, Data A4 to A4, Clock A5 to A5)

I2C need Pullup resistors on Data and Clock. The library uses the internal resistors of the Arduino, but I had some problems with that. So I found some hints that the Arduino resistor with about 30-50k is too big. I use now in addition 1K5 resistors (see picture above) and it works fine.

Ok, did you build both modules? Then we are close to the final. ;)

Step 8: The Final :) (Part-1)

OK, now it's getting exciting :)

You have your AquaController, the new AquaController Logger-Slave and the new AquaController Logger. The AquaController must connected via 4 wire cable with the AC-Logger-Slave. And you must connect your AC-Logger via Ethernet to your Router.

I'm sorry, you'll have to replace your probably adapted version of AquaController. There are some changes. You have a second slave, you must transmit data from A to B and I've added some improvements and fixed minor bugs. So you have 4 new sketches. See files above.

I guess it´s a good idea to give you some hints to understand the whole system:

AquaController V4



• I have 3 tanks with different values. Each tank have it´s own identifier (A40, A60,A160). The identifer decide what kinde of code the controller sends to the sockets. Otherwise there would be quite a mess.
• You have to decide if you use an Slave (#define SLAVE)
• You have to decide what kind of Slave you use (P_Slave -> Pumps, L_Slave -> Logger, Feeder -> Feeder Slave)
• You must decide what number each slave get. This is the I2C adress. (P_ID, F_ID, L_ID)


AquaController-Logger-Slave



• You have a Slave-ID (see above) You must use the same here. It´s the I2C-Adress
• You have an Radio-Identifier and a group it´s within. The AquaController-Logger (Master) is the number 1 in group 100. The AC-Logger-Slave is in the same group, but have another identifer. It´s number 3 in this project.
• You have to decide what radio units you use. My units sends with 868MHz. If you have bought others, use "RF12_433MHZ" or "RF12_915MHZ".


AquaController-Logger



• This is the master. His ID is 1 and his group is 100.
•

You have to devide what radio units you use. My units sends with 868MHz. If you have bought others, use "RF12_433MHZ" or "RF12_915MHZ". But you must use the same you use for the slaves ;)


•

The MAC-Adress isn´t important. So you can use my values.


• You must decide what internal IP-adress you give to the slave.
• You must change the IP-Adress from "myServer" because the IP from your provider is an other!
• Host, Userkey and Useragent are the values you use in the file "SaveMessToMySQL"


Attachments

Step 9: The Filal (Part-2)

When you have made your personal settings in the files you can upload all files:

  • AquaControllerV4.ino -> into your AquaController
  • AquaControllerLSlave.ino -> into the Unit from Step 7
  • AquaControllerPSlave.ino -> into the Unit from my Projekt "AquaController II"
  • AquaControllerLogger.ino -> into the Unit from Step 6

Did you upload all PHP-Files to you webspace? See step 5!

Ok, POWER ON ;)

What should happen now?

  1. The lamps from then AC-Logger should flash irregularly.
  2. The TFT-Display should display everything as usual.
  3. Sorry, no tataaaahhh or something like that.... ;)

Did you define #define SERIAL ?

Each unit tells you what's going on:

  • The Logger-Unit tells you it's IP and status, waiting for data and tells you when data was sent.
  • The AC-Pump-Slave tells you when he get commands from the AquaController and what to do now.
  • The AC-Logger-Slave tells you when he get commands from the AquaController and what to do now.
  • The AC-Logger talks a lot ;) The Logger tell you something about his job and the data he sent.

Any change to the AquaController triggers a transfer to you Database. If really nothing happens, all 5 minutes starts a transfer. After a short while you will receive data.

Now you can view the values from your database:

Open you Browser and go to "www.yourDomainNameDOTxxx/ViewData.php" V & D are in uppercase!

You can use 2 parameter:

If you have no data

  • you have to specify the ID of your AquaControllerLogger like ".../ViewData.php?A=2"
  • You can specify the period of days, like ".../ViewData.php?A=2&D=1"

You should see something like the picture above.

The other picture shows you what happens in the next projekt.

Ok, I tried to write this whole text without Google Translater. :) I hope you understand everthing. If not feel free to ask.

This AquaController is a little tricky and I guess there is someone who can make it easier ;) But if you build this system I would like to say to you: Don't give up ;) It´s only FUN! Enjoy it.