Introduction: Aquarium Controller IV - Visualize MySQL to Charts

Did you read my instructions about the Aquarium Controller III?
Ok, this is an addon and can not be used separately. (only in changed form)

I would like to show how I visualize my controller values from a mySQL database in charts.

I use an mySQL database and PHP from my Internet provider "Strato.de" / "Strato.com". I guess, you can use any other... Online visualization allows me to check the values from my three AquaController from anywhere on the Internet via PC, smartphone or tablet.

You may need some knowledge from the AquaController III-project. Especially to directories, FTP and databases. I will not repeat this in detail in this project.

This project shows 12 Sensors/Values (and timestamp) in one chart with different colors and linetype.

You find an sql-script to make a table you need in the last projekt (AquaController III) and in this projekt sample data for about 6 hours. So you can see how it works.

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

- values from a table of your database

- an software archive from HighCharts.com

- A cup of coffee or tea ;)

Step 2: You Need HighCharts.com

To visualize my data, I chose HighCharts. HighCharts.com offers free use for private users.

So, please go to http://www.highcharts.com/products/highcharts and find out all about HighCharts, if you want. I think you should ;)

Then download the archive from HighCharts that we need for visualization - http://www.highcharts.com/download. You get an archive called "Highcharts-5.0.7.zip" Maybe the version number is higher than this. Please unzip the archiv.

For our application we need only 1 file.You'll find it here: ...\your-download-dir\Highcharts-5.0.7.zip\code\js\highcharts.js

You can also download the single file on http://code.highcharts.com/. But for better understandig you should download the whole zip-file and look at the reference page http://api.highcharts.com/highcharts.

Please upload this single file via FTP to your webspace into the directory \your-root\ac_scripts

Step 3: You Need Something...

Did you upload the PHP files from the project AquaController III?

You need: db.inc.php -> \your_root\ac_inc

And you need 2 new php-scripts:

  • charts.php (rename it to index.php to show the charts by viewing your website)

you must download this file, see above and rename it from charts.txt to charts.php and than upload this file via FTP to the root-dir of your webspace.

  • functions.inc.php

you must download this file, see above and rename it from functions.inc.txt to functions.inc.php and than upload this file via FTP to the root-dir\ac_includes of your webspace.

In addition, you will get about 6 hours of sample data. (acsensorwerte.sql)

It´s important, no... you should not change any code. Some vars and defines need each other. Only if you exaclty know what to do, you should change something, because errors are difficult to find. But you can translate headlines without risk ;)

You can do a lot with HighCharts, this is really fun! And if you know the code, feel free to make your own fun ;)

Step 4: Final

Checklist

- You have created a table (acsensorwerte - see AquaController III) with an admin tool phpMyAdmin (or similar)?

- You have filled the table with sample values (acsensorwerte - this projekt) with phpMyAdmin (or similar)?

- You have created dirs under the root dir in your webspace and fill them with files?

  • ac_inc -> db.inc.php
  • ac_includes -> functions.inc.php
  • ac_scripts -> highcharts.js
  • sc_styles -> default.css (later)
  • ACMesswerte -> SaveMessToMySQL.php (only if you collect values from sensors)
  • (root-dir) -> charts.php (renamed in index.php)

The chart script has two parameters that you can or must use because there are default values in my scripts.

  1. H = hours of values to show in the chart
  2. A = Aqua Controller (I2C id from the Logger slave)

e.g. www.your_domain.com/?A=2&H=6 means: show me a chart from my AquaController 2 with values of the last 6 hours. Without parameters the chart shows AquaController 2 and 24 hours.

At the position of the mouse in the diagram, all the values determined at this time are displayed in tabular form. See picture 1.

By clicking on the identifiers in the x-axis, individual curves can be switched off. See picture 2.

The graph refreshes every 300 seconds. See meta data in charts.php