EG254S

Creating My Very Own IoT Dashboards

With the help of IoT dashboards, the pest control company can view and (remotely) monitor the rat activities and the rat poison bait level from anywhere locally or in the world. The IoT dashboard is the key HMI (Human-Machine Interface) component that organizes and presents the data I have collected in my IoT data solution using MySQL database server on the Raspberry Pi.

How to Draw Bar Graph using Data from MySQL Table and PHP

(Adapted from: https://www.dyclassroom.com/chartjs/chartjs-how-to-draw-bar-graph-using-data-from-mysql-table-and-php)

Let's draw two Bar Graphs, one for the rat activities and the other for the rat poison bait level, using the data from MySQL table and PHP.

Requirement

We will need the following items to complete this task.

Text Editor like SublimeText, TextMate, Coda, NotePad++ (which I use) or IDE like Eclipse
Web Browser like Chrome or Firefox
PHP development environment on the Raspberry Pi. (For installation, we can refer to our lab sheet from previous modules.)
MySQL (Refer to IoT Data using MySQL)
ChartJS (Using ChartJS 2.x Download or download here.)

So lets get started...

Create a New Folder

Create a new project folder and name it chartjs, which I had placed in the DocumentRoot that was set to /var/www/html by the Apache server on the Raspberry Pi. Inside the chartjs project folder create a subfolder and name it js. This will hold all the javascript files which was downloaded from above.

Create data.php file to fetch Data from MySQL Table

Create a new file data.php inside the chartjs folder. This file is going to contain php code that will fetch data from the score table and display it in JSON format.

We need to query MySQL database server to extract out two columns from the table: stationNo and ratCounter. The rows we want is the latest ratCounter from each stationNo in the table and the rows arranged in ascending order of the stationNo. This is the query statement for MySQL:

“SELECT stationNo, ratCounter FROM RatActivity AS t

CROSS JOIN ( SELECT stationNo, MAX(DateTime) AS DateTime FROM RatActivity GROUP BY stationNo )

AS sq USING (stationNo, DateTime) ORDER BY stationNo"

File: data.php

<?php

//setting header to json

header('Content-Type: application/json');

//database

define('DB_HOST', '127.0.0.1');

define('DB_USERNAME', 'root');

define('DB_PASSWORD', 'admin');

define('DB_NAME', 'RatBaitStation');

//get connection

$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

if(!$mysqli){

die("Connection failed: " . $mysqli->error);

}

//query to get data from the table

$query = sprintf("SELECT stationNo, ratCounter FROM RatActivity AS t CROSS JOIN ( SELECT stationNo, MAX(DateTime) AS DateTime FROM RatActivity GROUP BY stationNo ) AS sq USING (stationNo, DateTime) ORDER BY stationNo");

//execute query

$result = $mysqli->query($query);

//loop through the returned data

$data = array();

foreach ($result as $row) {

$data[] = $row;

}

//free memory associated with result

$result->close();

//close connection

$mysqli->close();

//now print the data

print json_encode($data);

Output of data.php file

To get the output of data.php file, open it in your web browser

URL: localhost/chartjs/data.php

Writing the javascript in ratApp.js

We will be making AJAX call from ratApp.js file to data.php file to fetch the JSON data which will be used to draw the rat activities bar graph.

File: ratApp.js

$(document).ready(function(){

$.ajax({

url: "http://10.1.2.104/dashboard/chartjs/data.php",

method: "GET",

success: function(data) {

console.log(data);

var station = [];

var rat = [];

for(var i in data) {

station.push(data[i].stationNo);

rat.push(data[i].ratCounter);

}

var chartdata = {

labels: station,

datasets : [

{

label: 'Number of Rat Activity',

backgroundColor: 'rgba(186, 85, 211, 0.48)',

borderColor: 'rgba(186, 85, 211, 0.68)',

hoverBackgroundColor: 'rgba(186, 85, 211, 1)',

hoverBorderColor: 'rgba(186, 85, 211, 1)',

data: rat

}

]

};

var ctx = $("#mycanvas");

var barGraph = new Chart(ctx, {

type: 'bar',

data: chartdata

});

error: function(data) {

console.log(data);

}

});

Create ratActivitiesBarGraph.html file to Display the Rat Activities Bar Graph

Now create a new file inside chartjs project folder and name it ratActivitiesBarGraph.html. This file is going to display the rat activities bar graph.

File: ratActivitiesBarGraph.html

<!DOCTYPE html>

<head>

<!--[if lt IE 9]>

<![endif]-->

#chart-container {

width: 780px;

height: auto;

}

</style>

</head>

<body>

<nav>

<li role="presentation"><a href="../../about/index.html">About</a></li>

<li role="presentation"><a href="../../contact/index.html">Contact</a></li>

</ul>

</nav>

<h3 class="text-muted">EG254S: IoT System Project</h3>

</div>

<a href="../index.html">IoT Dashboards</a>

<img src="../../images/images.png" alt="IoT Dashboards"

style="vertical-align:text-center; height:38px"> </div>

Rat Activities</h2>

<table>

<tr>

</div>

</td>

</tr>

</table>

</div>

Dashboard</a></p>

</div>

</footer>

</div>

</body>

</html>

Output

Writing the javascript in baitApp.js

We will be making AJAX call from baitApp.js file to data.php file to fetch the JSON data which will be used to draw the rat poison bait level bar graph. The data fetched had to be manipulated to reflect the rat bait level left.

File: baitApp.js

$(document).ready(function(){

$.ajax({

url: "http://10.1.2.104/dashboard/chartjs/data.php",

method: "GET",

success: function(data) {

console.log(data);

var station = [];

var rats = [];

for(var i in data) {

station.push(data[i].stationNo);

rats.push(data[i].ratCounter);

}

var baits = [];

for(var i = 0; i < rats.length; i++) {

baits.push((10 - rats[i]) * 10);

}

var chartdata = {

labels: station,

datasets : [

{

label: 'Poison Bait Level (%)',

backgroundColor: 'rgba(34, 139, 34, 0.48)',

borderColor: 'rgba(34, 139, 34, 0.48)',

hoverBackgroundColor: 'rgba(34, 139, 34, 1)',

hoverBorderColor: 'rgba(34, 139, 34, 1)',

data: baits

}

]

};

var ctx = $("#mycanvas");

var barGraph = new Chart(ctx, {

type: 'bar',

data: chartdata

});

error: function(data) {

console.log(data);

}

});

Create ratBaitsBarGraph.html file to Display the Rat Poison Bait Level Bar Graph

Again, create a new file inside chartjs project folder and name it ratBaitsBarGraph.html. This file is going to display the rat poison bait level bar graph.

File: ratBaitsBarGraph.html

<!DOCTYPE html>

<head>

<!--[if lt IE 9]>

<![endif]-->

#chart-container {

width: 780px;

height: auto;

}

</style>

</head>

<body>

<nav>

<li role="presentation"><a href="../../about/index.html">About</a></li>

<li role="presentation"><a href="../../contact/index.html">Contact</a></li>

</ul>

</nav>

<h3 class="text-muted">EG254S: IoT System Project</h3>

</div>

<a href="../index.html">IoT Dashboards</a>

<img src="../../images/images.png" alt="IoT Dashboards"

style="vertical-align:text-center; height:38px"> </div>

Rat Poison Baits</h2>

<canvas id="cvs1" width="230" height="230">[No canvas support]</canvas>

<p style='font-size:11pt;text-align:center;font-weight: normal; color:forestgreen;font-family:"Tahoma"'>RatBS005</p></td>

<canvas id="cvs2" width="230" height="230">[No canvas support]</canvas>

<p style='font-size:11pt;text-align:center;font-weight: normal; color:forestgreen;font-family:"Tahoma"'>RatBS006</p></td>

</tr></table></div>

new RGraph.Gauge({

id: 'cvs1',

min: 0,

max: 100,

value: 60,

options: {

valueText: true,

valueTextDecimals: 0,

valueTextBounding: false,

valueTextYPos: 0.3,

valueTextUnitsPost: '%',

textSize: 11,

greenColor: '#afa',

greenWidth: 10,

redWidth: 15,

yellowWidth: 15,

labelsCentered: true,

labelsOffsetAngle: 0,

labelsOffsetRadius: 11,

labelsOffsetx: 0,

labelsOffsety: 0

}

}).draw();

</script>

window.onload = function ()

{

var gauge = new RGraph.Gauge({

id: 'cvs2',

min: 0,

max: 100,

value: [70,7],

options: {

titleTop: 'Bait',

titleTopSize: '13',

titleTopFont: 'Impact',

titleTopColor: 'white',

titleBottom: 'Rats',

titleBottomSize: 'Italic 10',

titleBottomFont: 'Impact',

titleBottomColor: '#ccc',

titleBottomPos: 0.2,

backgroundColor: 'black',

backgroundGradient: true,

centerpinColor: '#666',

needleSize: [null, 40],

needleColors: ['Gradient(transparent:white:white:white:white:white)', 'Gradient(transparent:#d66:#d66:#d66:#d66)'],

textColor: 'white',

tickmarksBigColor: 'white',

tickmarksMediumColor: 'white',

tickmarksSmallColor: 'white',

borderOuter: '#666',

borderInner: '#333',

colorsRanges: [[160,200,'rgba(255,0,0,1)'], [120,160,'rgba(255,255,0,0.95']],

textAccessible: true

}

}).draw();

};

</script>

<table>

<tr>

</div>

</td>

</tr>

</table>

</div>

Dashboard</a></p>

</div>

</footer>

</div>

</body>

</html>

Output

Conclusion

There we have our very own IoT dashboards showing the rat activities and the rat bait level using Bar Graphs. By clicking on the Dashboard button, we get the real-time data from MySQL database server on the Raspberry Pi. Using IoT platforms like the IoT dashboards, the pest control company can benefit from real-time insights and analytics, which would help them make smarter decisions.

EG254S: IoT System Project - IoT Platforms

Creating My Very Own IoT Dashboards

How to Draw Bar Graph using Data from MySQL Table and PHP

Requirement

Create a New Folder

Create data.php file to fetch Data from MySQL Table

Output of data.php file

Writing the javascript in ratApp.js

Create ratActivitiesBarGraph.html file to Display the Rat Activities Bar Graph

Output

Writing the javascript in baitApp.js

Create ratBaitsBarGraph.html file to Display the Rat Poison Bait Level Bar Graph

Output

Conclusion