store and display data from/to a web server
Post date: Mar 04, 2016 10:35:53 AM
Mechanism:
Posting and getting data from the arduino:
The arduino indoor sensor is posting the outdoor and indoor data to a mysql database trough a php page add.php (see below)
the add.php page read the datas from the form and connect to the DustDensity database hosted on a mysql database server and store the outdoor and indoor data on a dustSensor table trough SQL query.
"INSERT INTO `DustSensor` (`Indoor`, `Outdoor`) VALUES ('".$indoor."','".$outdoor."')";
The DustSensor table got three fields.
timeStamp: SQL timestamp generated when new datas are inserted.
Indoor: int(11) field to store the indoor reading
outdoor: int(11) field to store the oudoor reading.
I'm using phpmyadmin to create the dustDensity database and the table DustSensor but the same thing can be achieved by using SQL script on the command line.
I put the database connection username and password on a separate connect.php page (see below)
The arduino indoor sensor is getting the 24h average dust density trough the getavg.php page (see below)
The getavg.php page connect to the mysql database and querry the 24h avg dust density form the DustSensor table on the DustDensity database via the following SQL query"
"SELECT AVG(Outdoor), AVG(Indoor) FROM `DustSensor` WHERE timeStamp > DATE_SUB(now(), INTERVAL 1 DAY) ORDER BY `timeStamp` DESC"
The result is parsed on the webpage by using json (javascript object notation) parser.
If i open the getavg.php page form my browser i will have the following display:
[{"AVG(Outdoor)":"13.1466","AVG(Indoor)":"22.5551"}]
Displaying data from a browser:
The page view data.php (see below) is showing the data in a html table.
The page query the data from the mysql database by running the following SQL querry:
SELECT * FROM `DustSensor` ORDER BY `timeStamp` DESC"
To graphically display the data on a webpage i'm using D3.js javascript data driven document code.
D3.js is a very interesting piece of javascript library to graphically display data on a webpage with plenty different format.
check http://www.d3noob.org/ and https://github.com/mbostock/d3/wiki for D3 documentation.
i build the code from http://bl.ocks.org/d3noob/13a36f70a4f060b97e41 and http://www.d3noob.org/2013/02/using-mysql-database-as-source-of-data.html
The graph.php page get the data to display by calling the getdat.php page (see below).
I want to print the last 24 hours data from the outdoor and indoor sensor.
The getdata.php page run the following SQL querry and parse them to the calling page graph.php via json javascript object.
SELECT timeStamp, Outdoor, Indoor FROM `DustSensor` WHERE timeStamp > DATE_SUB(now(), INTERVAL 1 DAY) ORDER BY `timeStamp` DESC"
The graph.php page display the data from the Json dataset
PHP code:
add.php
<?php
include("connect.php");
$link=Connection();
$UnixTime=$_POST["UnixTime"];
$indoor=$_POST["indoor"];
$outdoor=$_POST["outdoor"];
$query = "INSERT INTO `DustSensor` (`Indoor`, `Outdoor`)
VALUES ('".$indoor."','".$outdoor."')";
mysql_query($query,$link);
mysql_close($link);
header("Location: index.php");
?>
getavg.php
<?php
include("connect.php");
$link=Connection();
$query=mysql_query("SELECT AVG(Outdoor), AVG(Indoor) FROM `DustSensor` WHERE timeStamp > DATE_SUB(now(), INTERVAL 1 DAY) ORDER BY `timeStamp` DESC",$link);
if ( ! $query ) {
echo mysql_error();
die;
}
$data = array();
for ($x = 0; $x < mysql_num_rows($query); $x++) {
$data[] = mysql_fetch_assoc($query);
}
echo json_encode($data);
mysql_close($server);
?>
getdata.php
<?php
include("connect.php");
$link=Connection();
$query=mysql_query("SELECT timeStamp, Outdoor, Indoor FROM `DustSensor` WHERE timeStamp > DATE_SUB(now(), INTERVAL 1 DAY) ORDER BY `timeStamp` DESC",$link);
if ( ! $query ) {
echo mysql_error();
die;
}
$data = array();
for ($x = 0; $x < mysql_num_rows($query); $x++) {
$data[] = mysql_fetch_assoc($query);
}
echo json_encode($data);
mysql_close($server);
?>
graph.php
/ Adds the svg canvas
var svg = d3.select("body")
.append("svg")
.attr("width", width + margin.left + margin.right)
.attr("height", height + margin.top + margin.bottom)
.append("g")
.attr("transform",
"translate(" + margin.left + "," + margin.top + ")");
// Get the data
d3.json("getdata.php", function(error, data) {
data.forEach(function(d) {
d.timeStamp = parseDate(d.timeStamp);
d.Outdoor = +d.Outdoor;
d.Indoor = +d.Indoor;
});
// Scale the range of the data
x.domain(d3.extent(data, function(d) { return d.timeStamp; }));
//y.domain([0, d3.max(data, function(d) { return Math.max(d.Outdoor, d.Indoor); })]);
y.domain([0, 550]);
// Add the valueline path.
svg.append("path")
.attr("class", "line")
.attr("d", valueline(data));
svg.append("path") // Add the valueline2 path.
.attr("class", "line")
.style("stroke", "red")
.attr("d", valueline2(data));
// Add the X Axis
svg.append("g")
.attr("class", "x axis")
.attr("transform", "translate(0," + height + ")")
.call(xAxis);
// Add the Y Axis
svg.append("g")
.attr("class", "y minor endticks classical")
.call(yAxis);
});
</script>
</body>
viewdata.php
<?php
include("connect.php");
$link=Connection();
$result=mysql_query("SELECT * FROM `DustSensor` ORDER BY `timeStamp` DESC",$link);
?>
<html>
<head>
<title>Dust density Data</title>
</head>
<body>
<h1>Dust density sensor readings</h1>
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<td> timeStamp </td>
<td> Indoor </td>
<td> Outdoor </td>
</tr>
<?php
if($result!==FALSE){
while($row = mysql_fetch_array($result)) {
printf("<tr><td> %s </td><td> %d </td><td> %d </td></tr>",
$row["timeStamp"], $row["Indoor"], $row["Outdoor"]);
}
mysql_free_result($result);
mysql_close();
}
?>
</table>
</body>
</html>
connect.php
<?php
function Connection(){
$server="localhost";
$user="dbusername";
$pass="dbpass";
$db="dustDensity";
$connection = mysql_connect($server, $user, $pass);
if (!$connection) {
die('MySQL ERROR: ' . mysql_error());
}
mysql_select_db($db) or die( 'MySQL ERROR: '. mysql_error() );
return $connection;
}
?>