September 2015

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

tempdataIn 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…

 

 

Pushing Arduino Data to MySQL via PHP

Arduino+EthernetThis is part of my little ongoing project of learning with the Arduino.  I want to give a mention to Tweaking4all’s guide to PHP, SQL and Arduino, because I started out using it as a base for this section of the project, though I altered a few parts to work with my sensors and Ethernet board.  That guide definitely pointed me in the right direction and made me realize just how simple it would be to actually push data to the server.

My initial approach to the problem was that I needed the Arduino to run some SQL queries and interact directly with the database and insert readings and other variables.  In the end, all I needed was a PHP file on the server to interact with the database, and the proper call from the Arduino to the PHP file.

I started out with basic code to dump dummy data (ie not variables) to a database running on my laptop.  I couldn’t get UIPEthernet linked int he Tweaking4all guide to work so I just used the default Arduino Ethernet libraries.  I also simplified the code down to one probe, though adding more temperature probes will be trivial.  After I managed to get the dummy data to post reliably, I moved on to adding variables in place of the dummy values.  Once again, I couldn’t get the OneWire library to load properly and work with my senors, so I went back to the basic DHT-11 library that I knew work.  This actually simplified things considerably, I used the same calls I had done previously in testing and instead of pushing them directly tot he serial port, I dumped them to some variables which are then passed to the SQL statement.

I also added variables and functions to read Humidity, since he original article doesn’t have humidity readings included.

The final hurdle I came across, for some reason, the delay() function wasn’t working properly.  I set it to poll every 15 minutes (in milliseconds) but it never posted a new update beyond the initial one when powered on.  If I tried a shorter interval, such as 5 minutes (in ms), I got new readings every 30 seconds or so.   In the end, I used a better method of handling time with currentmills.  This reads the current number of milliseconds since the last reading.  By reading currentmills and comparing it to the last reading “time” I can verify if it’s been 15 minutes since the last reading.  This method is not super precise and has some play on interval but I’m not doing anything requiring perfect timing with this project.

In the end, I ended up with the following code for the Arduino:

#include <SPI.h>
#include <Ethernet.h> // Used for Ethernet
#include “dht.h”

dht DHT;

#define DHT11_PIN 5

// **** ETHERNET SETTING ****
// Arduino Uno pins: 10 = CS, 11 = MOSI, 12 = MISO, 13 = SCK
// Ethernet MAC address – must be unique on your network – MAC Reads T4A001 in hex (unique in your network)
byte mac[] = { 0x54, 0x34, 0x41, 0x30, 0x30, 0x31 };
// For the rest we use DHCP (IP address and such)

EthernetClient client;
char server[] = “SERVERIP“; // IP Adres (or name) of server to dump data to
int  interval = 360000; // Wait between dumps
unsigned long previousMillis=0;

void setup() {

Serial.begin(9600);
Ethernet.begin(mac);

Serial.println(“RamenJunkie’s Ethernet Temperature Probe based on Tweaking4All Probe”);
Serial.println(“-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\n”);
Serial.print(“IP Address        : “);
Serial.println(Ethernet.localIP());
Serial.print(“Subnet Mask       : “);
Serial.println(Ethernet.subnetMask());
Serial.print(“Default Gateway IP: “);
Serial.println(Ethernet.gatewayIP());
Serial.print(“DNS Server IP     : “);
Serial.println(Ethernet.dnsServerIP());
}

void loop() {

unsigned long currentMillis=millis();

if((currentMillis – previousMillis) > 900000)
{
previousMillis=currentMillis;
// READ DATA
Serial.print(“DHT11, \t”);
int chk = DHT.read11(DHT11_PIN);
switch (chk)
{
case DHTLIB_OK:
Serial.print(“OK,\t”);
break;
case DHTLIB_ERROR_CHECKSUM:
Serial.print(“Checksum error,\t”);
break;
case DHTLIB_ERROR_TIMEOUT:
Serial.print(“Time out error,\t”);
break;
default:
Serial.print(“Unknown error,\t”);
break;
}

// if you get a connection, report back via serial:
if (client.connect(server, 80)) {
Serial.println(“-> Connected”);
// Make a HTTP request:
client.print( “GET /temps/add_data.php?”);
client.print(“serial=”);
client.print( “Probe1” );
client.print(“&”);
client.print(“temperature=”);
client.print(DHT.temperature,1);
//      client.print(“88”);
client.print(“&”);
client.print(“humid=”);
client.print(DHT.humidity,1);
//      client.print(“88″);
client.println( ” HTTP/1.1″);
client.print( “Host: ” );
client.println(server);
client.println( “Connection: close” );
client.println();
client.println();
client.stop();
}
else {
// you didn’t get a connection to the server:
Serial.println(“–> connection failed/n”);
}

}

Serial.print(currentMillis);
Serial.print(”  “);
Serial.println(previousMillis);

delay(10000);
}

A few notes to anyone wanting to try to use this.

  • You will need to set the server IP to the IP of your database.
  • You can alter the time interval between readings with the interval variable as well.
  • If you plan to use more than one Arduino probe (which you can), you’ll want to change the MAC address settings.  it can be any MAC value really since you’re just assigning it manually but having multiple devices with the same MAC will cause issues.  I’d recommend simply increasing the last value (31) by one.
  • You can change the line “client.print( “Probe1″ );” to name the probe whatever you want.  The original code read serial numbers but I’m not sure if DHT-11 handles serial numbers or not.  I’d also recommend changing it if you run more than one Arduino probe to the same database.

This will create basic output from the Arduino.  The other half of this is to set up a server running SQL to receive the data, but I’ll cover that in the next entry.  I based it off the code linked above in Tweaking4all but altered it to take in the Humidity as well as allow for viewing individual dates.

Tools I Use: Netscan and Fing

I wanted to do some occasional posts on some tools I use for various technical tasks.  Partially just to suggest some useful stuff, partially so I have some posts to reference anytime I reference said stuff.

I wanted to start off with Netscan and Fing, which serve the same basic purpose on two different platforms.  Both of these tools will scan the local IP range and return a list of every device connected to the network.  Netscan is what I use on windows, Fing is what I use on Android.

I use these tools very frequently, several times a week on average.  So what use is scanning the local network anyway?  I have two main uses, though both come down to Device Discovery.

Firstly, basic device discovery.  I’ve hooked something new to the network and I need to access it.  A lot of what I connect is headless with no easy way of discovering the IP aside from a scan.  An Arduino, a Raspberry Pi, a networked Webcam, all of these things need to be found once connected.  The scan is also useful for getting the MAC address of devices on the network.  The IP is dynamic on a network by network basis, a MAC address is a unique identifier.  Knowing the MAC address is useful for building firewall rules and setting up static IPs assigned by the router for devices like phones or laptops where assigning IPs on the device can get hairy.

The other reason for doing a network based scan is intrusion detection.  Generally speaking, I don’t expect to see hackers or anything on my home network.  This is more for checking things like “if my kids’ devices are connected” or occasionally if one of my kids has a new device borrowed or whatever that I am not aware of on the network.

Ultimately I want to set up a little network monitoring system on a server to do these sorts of checks in real time but both of these tools have served me well for years as doing the job quickly and simply.

Both are also useful for poking around foreign networks.  You can see what machines are on an open WiFi hotspot and see if they have any open shared files.  Though some open hotspots are smart enough to block such scans.