Pushing Arduino Data to MySQL via PHP Part 2: The Server

Ramen Junkie September 24, 2015 1 Comments

In the last post, I talked about how to send data from the Arduino to the MySQL server.

Today, I’ll cover how I’m receiving and displaying that data on the server side.  I’ll put the code for the webpages into this post but I’ll put everything together in a nice little package at the end of everything.  I mentioned last post that the end trick was to let the server worry about processing the data instead of the Arduino.  I’m running this on my NAS but it could be run on any standard web server with PHP and SQL.

For the purposes of this code, I’ve placed the files in a directory called “temps” on the root of the web server.  If you want to put them elsewhere, such as “temperaturelog” or “home/temps” or wherever, you’d need to alter the code of the Arduino in the previous points to replace the temps directory with the directory you plan to use.  I’m going to assume that the reader has a basic LAMP (Linux, Apache, MySQL, PHP) stack style server and knows the basics of how to create databases and run things on it.  If not you can Google the basic set up, though I may do a quick write up and reference it here eventually.

You’ll need to create a database called “housetemps” and import the linked structure file into it.  (If you know what you’re doing in PHP you can rename housetemps)   This will create a table int he database called “temperature” with the following columns: id, event, sensor, celsius, humidity, datestamp.  This will give you the basic structure needed to run the php code on the server.  A quick rundown of what these values are used for…

• id – A standard auto incriminating id value for SQL
• event – Time and date of when the event happened
• sensor – A text based identifier for each probe.  This example uses one probe but could be altered for more.
• celsius – The temperature reading, it doesn’t actually have to be Celsius, that’s just the default of the probe.
• humidity – Humidity reading in percent humidity
• datestamp – A date only time stamp, used for sorting the data when you review it later.

Now that you have the core database structure set up, the server needs to know the credentials for connecting to the database.  Since this is used by several files, it’s best to put the info in it’s own file and use an include statement int he PHP to add it.

In the “temps” directory create a file dbconnect.php and add int he following code, changing the values listed for the values used when setting up the database…

<?php
$MyUsername = “USERNAME”;  // enter your username for mysql
$MyPassword = “PASSWORD”;  // enter your password for mysql
$MyHostname = “localhost”;      // this is usually “localhost” unless your database resides on a different server

$dbh = mysql_pconnect($MyHostname , $MyUsername, $MyPassword);
$selected = mysql_select_db(“housetemps”,$dbh);
?>

Next you’ll need a way to get data into the table.  This is done using add_data.php, which is called by the Arduino in the code shown previously.

Make a file in the “temps” directory called add_data.php, and add the following code.

<?php
// Connect to MySQL
include(‘dbconnect.php’);

{
$dateget = date(“Y-m-d”);
// Prepare the SQL statement
$SQL = “INSERT INTO housetemps.temperature (sensor ,celsius, humidity, datestamp) VALUES (‘”.$_GET[“serial”].”‘, ‘”.$_GET[“temperature”].”‘, ‘”.$_GET[“humid”].”‘, ‘$dateget’)”;

// Execute SQL statement
mysql_query($SQL);
}

// Go to the review_data.php (optional)
header(“Location: review_data.php”);
?>

The code is pretty straight forward. The first section includes our previously created log on credentials.  The last section forwards the page on to review the data.  The meat is in the middle with the SQL statement.  The line $dateget = date(“Y-m-d”); creates a Year-Month-Day date for sorting later.  The next line creates the SQL query using variables passed via the url, the third part executes this query adding the data to the database.

The final piece of this whole thing is the review_data.php file.  This file displays the results in a nice looking table.

<?php
// Start MySQL Connection
include(‘dbconnect.php’);
?>

<html>
<head>
<title>Arduino Temperature Log</title>
<style type=”text/css”>
.table_titles, .table_cells_odd, .table_cells_even {
padding-right: 20px;
padding-left: 20px;
color: #000;
}
.table_titles {
color: #FFF;
background-color: #666;
}
.table_cells_odd {
background-color: #CCC;
}
.table_cells_even {
background-color: #FAFAFA;
}
table {
border: 2px solid #333;
}
body { font-family: “Trebuchet MS”, Arial; }
</style>
</head>

<body>
<h1>Arduino Temperature Log</h1>

<p>Select Date:
<?php

$sql = “SELECT DISTINCT datestamp FROM temperature”;
$result = mysql_query($sql);

echo “<select name=’datestamp’ onchange=’location = this.options[this.selectedIndex].value;'”;
while ($row = mysql_fetch_array($result)) {
$current = $row[‘datestamp’];
echo “<option value=’review_data.php?dateselect=$current’>$current</option>”;
}
echo “</select>”;
?>

<table border=”0″ cellspacing=”0″ cellpadding=”4″>
<tr>
<!–    <td class=”table_titles”>ID</td> –>
<td class=”table_titles”>Date and Time</td>
<td class=”table_titles”>Sensor Serial</td>
<td class=”table_titles”>Temperature in Celsius</td>
<td class=”table_titles”>Percent Humidity</td>
</tr>
<?php
// Retrieve all records and display them
$SQL = “SELECT * FROM temperature WHERE datestamp LIKE ‘”.$_GET[“dateselect”].”%’ ORDER BY id ASC”;

//Execute the SQL
$result = mysql_query($SQL);

// Used for row color toggle
$oddrow = true;

// process every record
while( $row = mysql_fetch_array($result) )
{
if ($oddrow)
{
$css_class=’ class=”table_cells_odd”‘;
}
else
{
$css_class=’ class=”table_cells_even”‘;
}

$oddrow = !$oddrow;

echo ‘<tr>’;
//       echo ‘   <td’.$css_class.’>’.$row[“id”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“event”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“sensor”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“celsius”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“humidity”].'</td>’;
echo ‘</tr>’;
}
?>
</table>
</body>
</html>

The core of this file was lifted from the previously mentioned guide on Tweaking4All.  The problem I had with their results was that it simply displayed an endless list.  I am polling every 15 minutes, so this list tends to grow unwieldy very quickly.  This is where the datestamp marker comes into play.  I added this little chunk of code at the top of the table.

<?php

$sql = “SELECT DISTINCT datestamp FROM temperature”;
$result = mysql_query($sql);

echo “<select name=’datestamp’ onchange=’location = this.options[this.selectedIndex].value;'”;
while ($row = mysql_fetch_array($result)) {
$current = $row[‘datestamp’];
echo “<option value=’review_data.php?dateselect=$current’>$current</option>”;
}
echo “</select>”;
?>

This creates a menu based on unique values of datestamp.  Selecting a value forwards you to review_data.php with a date attached, then review_data.php only shows data that matches that datestamp.  This allows a single day to be viewed easily.  In the future I may add a bit more to this menu, eventually, the list of dates will also become unwieldy in length.  I also plan to run numerous sensors at once with different names so I’ll likely also add a second menu so the list can be sorted down by sensor name.

I’m also looking to add a bit more functionality to the code in the form of a graph, so it will be easier to see trend lines in the data.  The data I have now is from my office, in a well insulated basement room, so the trend lines are rather boring, but when there are several sensors in different rooms in the main area of the house, or even outside, this data becomes more interesting and useful.  I’ll go into this at a later day though…