Here’s where things started to get a little more complex. At this point, I have a wireless system to transmit data from multiple nodes to a central hub (node0) which collects and shows the data via serial connection.
The general idea/goal is this:
- Arduino Nodes send data to central node
- Central node is connected to home network and sends this data to a LAMP server (Linux-Apache-Mysql-PHP)
- The server takes this data, breaks it into a mysql statement, and pushes it into the database to log
This was a pretty straightforward process but took a lot of planning before taking this on to ensure everything went smoothly. I’m not going into detail for setting up a LAMP server but there is a massive amount of information online for this. If you have an old computer kicking around you could run as a headless server or want to run it as a VM, it will take anywhere from 15-20 minutes to fully install and be ready for this.
I’ll break this down into different parts:
- Mysql database set up (database/tables)
- PHP script to accept the data and log this
- PHP script to show information in webpage
- Actual Arduino code to compile then send this
So, let’s begin: We’ll assume that you have a working LAMP server that is connected to your home network and able to communicate with the Arduino we’ll hook in later on, and also be able to do simple file editing using Vim or Nano. The table design was build using a log template format. We’ll start here by opening a root session and log in to mysql:
mysql -A
Next, we’ll create our empty database:
CREATE DATABASE arduino;
This creates an empty database to fill with tables. We’ll build this next, paying attention to the different data types (I’m not going into detail on this due to the amount of time to cover these):
CREATE TABLE `arduinoLog` ( `timeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `temperature` int(11) NOT NULL, `humidity` int(11) NOT NULL, `node` tinyint(10) DEFAULT NULL, `vcc` tinyint(10) DEFAULT NULL, `nodeCount` int(11) DEFAULT NULL, PRIMARY KEY (`timeStamp`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
This creates out table to log to. Notice that we can log time stamps, humidity, temperatures, etc. This all comes later. We now have an empty table to write/read data for our project. Let’s continue.
Next up, we need a way to read the data in an easier format than querying the database constantly, so we need a script to run in order to do this and show in a webpage by browsing to the IP of our LAMP server. First, move to your htdocs directory (Ubuntu should either be /var/www or /var/www/html):
cd /var/www/html
Now, open the file you want to edit and drop this script into it:
vim arduinoLog.php
Script:
<?php
include("connect.php");
$link=Connection();
$result=mysql_query("SELECT * FROM `arduinoLog` ORDER BY `timeStamp` DESC LIMIT 20",$link);
?>
<html>
<head>
<title>Sensor Data</title>
</head>
<body>
<h1>Temperature / moisture sensor readings</h1>
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<td> Timestamp </td>
<td> Temperature 1 </td>
<td> Moisture 1 </td>
<td> Node </td>
<td> Vcc </td>
<td> Message Count </td>
</tr>
<?php
if($result!==FALSE){
while($row = mysql_fetch_array($result)) {
printf("<tr><td> %s </td><td> %s </td><td> %s </td><td> %s </td><td> %s </td><td> %s </td></tr>",
$row["timeStamp"], $row["temperature"], $row["humidity"], $row["node"], $row["vcc"], $row["nodeCount"]);
}
mysql_free_result($result);
mysql_close();
}
?>
</table>
</body>
</html>
This queries the database and pulls the last 20 entries from it to display into the webpage. Write the file then pull open your web browser on another computer and pull up this page, substituting my IP with yours:
http://172.31.1.110/arduinoLog.php
And you should be showing a screen showing the log. Next, we have to build a script to add this data. Open another script here:
- vim addData.php
Drop this code into it and save:
<?php
include("connect.php");
$link=Connection();
$temp1=$_POST["temp1"];
$hum1=$_POST["hum1"];
$node=$_POST["node"];
$vcc=$_POST["vcc"];
$count=$_POST["count"];
$query = "INSERT INTO `arduinoLog` (`temperature`, `humidity`, `node`, `vcc`, `nodeCount`)
VALUES ('".$temp1."','".$hum1."','".$node."','".$vcc."','".$count."')";
mysql_query($query,$link);
mysql_close($link);
header("Location: arduinoLog.php");
?>
This script handles the extra data on the end of a web request from the Arduino and pushes the variables into mysql. Then, open the last script which tells these how to connect to the database:
vim connect.php
Code:
<?php
function Connection(){
$server="localhost";
$user="root";
$pass="";
$db="arduino";
$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;
}
?>
There’s all the hard work, right? This should leave us with a fully functioning server capable of pulling and pushing data which we can check from a webpage or database. Here’s where it gets tricky: The Wiznet ethernet shields for the Arduino’s have a nasty habit of hanging onto SPI connections which causes issues when using them with RF24 wireless modules. Thankfully, we have a way to get around this with SoftSPI. Again, there is plentiful documentation so I’ll just mention the libraries you need to add to your Arduino library:
- DHT11
- DigitalIO
- RF24-master
- RF24Network-master
Again here, I won’t be going to far into detail with ports and what not but pay special attention to the 6/7 pins of the RF24 being used in place of 9/10 from default. Code time:
- Nodes: sendFromNode
- Ethernet Hub/Central base: wirelessBaseEthernet
Also note that you must change your web address for your server under the wirelessBaseEthernet sketch. Otherwise, hopefully you now have a working and wireless data logger! Pay attention to your node numbers so it separates them out properly. I also have tested this with up to 4 nodes without issues, just increase the sleep timer so they don’t overlap.
Happy modding!