Graphical and Table presentation of database info using Googlecharts and PHP

In an earlier post I discussed storing sensor data in a MySQL or MariaDB database (in combo with DeepSleep, which was the focus of the article).
The database part was based on an article by randomnerdtutorials.
Output of the gathered MySQL data was in shape of a table*).

Sometimes though a graph is a more practical way to look at data over a period of time. There are 3 values in the table: temperature, Humidity and Airpressure.
As I wanted one graph, I decided to leave out the Airpressure, as it’s high values  would skew the  Y-axis a bit, so i will be looking at  2 values only: temperature and humidity. The Airpressure is easy to add though.

There are various possibilities of  presenting data in a graph. ‘Highcharts ‘ for example, or jpGraph. This time though I decided for Googlegraphs. Fortunately there is a lot of info available and i found some frameworks at Techjunkgigs that I could rework.

That turned out something like this:

or, with more data:
You will find the program here. There are a few things you need to check. The program expects a database file that is called ‘esp-data’. It checks a table called ‘SensorData’. It looks for fields called ‘value1’, ‘value2’ and ‘reading_time ‘. If you are using different names, you need to adpt tghe program. You also need to insert your database username and password.

You will notice that when time passes by the graph will be condensed more and more.
That is why I added a ‘WHERE’ statement in the query:
$query = “SELECT * from SensorData WHERE id >=1”;

where you can enter a higher ID number (or insert other criteria such as date, or temperatures above a certain value).
However, if you would only be interested in say only the last 100 records, it is better to replace the query by:

$query = "SELECT * FROM(SELECT * from SensorData WHERE id >=1 ORDER BY id DESC LIMIT 100) AS `table` ORDER by id ASC";
_______________

*) The presented table is a bit basic. There are more interactive ways to present data in a table.
espdata-search

You will find the code here.

2 thoughts on “Graphical and Table presentation of database info using Googlecharts and PHP”

  1. For this type of data you might want to take a look at InfluxDB and Graphana. It’s much more tailored for exactly what you are doing.

    1. Thanks. Yes have been using influxdb and grafana as well. Mainly from within OpenHAB. Very good packages.
      May follow up with an article on posting esp data in influxdb

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.