Arduino Wireless Logging, Post Four: The Graphs

Posted byAustin

When I got all this data logged and running, I wanted an easier way to show data than spreadsheets or queries. The end goal was actual graphs to show trends and what not. Introducing “phpgraphlib”. This is an open source piece of code built to implement charts/graphs into webpages with ease. This took a bit of work, but here’s the idea behind it:

  • Be able to load a web page and show trends/data
  • Pull this information on demand from a database

Once the tinkering was over, I had a working dashboard to pull two images and show them in a webpage that refreshes every 60 seconds. I ended up going a little overboard in customization, but it happens. Here’s the final build:

  • Built a script that runs two sets of  data and groups by hour: humidity and temperature
  • Have a cron running every 5 minutes to compile this data into two separate PNG images to pull up online
  • Have a dashboard that refreshes every minute and shows both of these images

At this point in time, you can see the graphs here: https://aasullivan.com/arduino/dashboard.html

For now, I got a working system with graphs and full logging capabilities. Here’s the code for the graphs for temperature and humidity (respectively):

root@syr-arduino-monitor:/var/www/html/graphs# cat hourlyTemperature.php
<?php
include("phpgraphlib.php");

$graph=new PHPGraphLib(1500,400);

$link = mysql_connect('localhost', 'NOPE', 'NOPERS')
 or die('Could not connect: ' . mysql_error());

mysql_select_db('arduino') or die('Could not select database');

$dataArray1=array();
$dataArray2=array();
$dataArray3=array();
$dataArray4=array();

//get data from database
$sql="SELECT (AVG(al.temperature)) AS temperature, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=1 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $temp=$row["temperature"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray1[$hour]=$temp;
 }
}
$sql="SELECT (AVG(al.temperature)) AS temperature, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=2 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $temp=$row["temperature"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray2[$hour]=$temp;
 }
}
$sql="SELECT (AVG(al.temperature)) AS temperature, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=3 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $temp=$row["temperature"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray3[$hour]=$temp;
 }
}
$sql="SELECT (AVG(al.temperature)) AS temperature, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=4 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $temp=$row["temperature"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray4[$hour]=$temp;
 }
}

//configure graph
$graph->addData($dataArray1,$dataArray2,$dataArray3,$dataArray4);
$graph->setTitle("Overall Temperature Averages per Hour per Node");
$graph->setBars(false);
$graph->setLegend(true);
$graph->setLegendTitle("Node1","Node2","Node3","Node4");
$graph->setLine(true);
$graph->setRange(60,85);
$graph->setDataPointSize(10);
$graph->setDataPoints(true);
$graph->setLineColor("navy", "red","teal","maroon");
$graph->setDataPointColor('green');
$graph->setDataValues(true);
$graph->setDataValueColor('teal');
$graph->createGraph();
?>

And humidity:

root@syr-arduino-monitor:/var/www/html/graphs# cat hourlyHumidity.php
<?php
include("phpgraphlib.php");

$graph=new PHPGraphLib(1500,400);

$link = mysql_connect('localhost', 'NOPE', 'NOPERS')
 or die('Could not connect: ' . mysql_error());

mysql_select_db('arduino') or die('Could not select database');

$dataArray1=array();
$dataArray2=array();
$dataArray3=array();
$dataArray4=array();

//get data from database
$sql="SELECT (AVG(al.humidity)) AS humidity, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=1 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $humidity=$row["humidity"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray1[$hour]=$humidity;
 }
}
$sql="SELECT (AVG(al.humidity)) AS humidity, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=2 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $humidity=$row["humidity"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray2[$hour]=$humidity;
 }
}
$sql="SELECT (AVG(al.humidity)) AS humidity, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=3 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $humidity=$row["humidity"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray3[$hour]=$humidity;
 }
}
$sql="SELECT (AVG(al.humidity)) AS humidity, HOUR(al.timeStamp) AS hour FROM arduinoLog AS al JOIN lu_node lun ON lun.node=al.node WHERE al.node=4 GROUP BY HOUR(timeStamp)";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
if ($result) {
 while ($row = mysql_fetch_assoc($result)) {
 $humidity=$row["humidity"];
 $hour=$row["hour"];
 //add to data areray
 $dataArray4[$hour]=$humidity;
 }
}
//configure graph
$graph->addData($dataArray1,$dataArray2,$dataArray3,$dataArray4);
$graph->setTitle("Overall Humidity Averages per Hour per Node");
$graph->setBars(false);
$graph->setLegend(true);
$graph->setLegendTitle("Node1","Node2","Node3","Node4");
$graph->setLine(true);
$graph->setRange(30,70);
$graph->setDataPointSize(10);
$graph->setDataPoints(true);
$graph->setLineColor("navy", "red","teal","maroon");
$graph->setDataPointColor('green');
$graph->setDataValues(true);
$graph->setDataValueColor('teal');
$graph->createGraph();
?>

These, when run, will each make a PNG image. If run in a webpage, it will call it the .php script. If run in back end, capture the standard output like below to make the image:

php hourlyHumidity.php > hourly.PNG

As always, happy modding!