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.

Project: Radio DJ Automation

If I earned a nickel for everything I’ve ever created that no one else cares about I’d probably be about to earn another nickel with this next post.

Anyway, one of my many pet projects has been setting up a private internet radio stream.  I actually toyed with the idea for a bit of building a small FM transmitter and going full on Pirate Radio but I decided that the current state of media doesn’t necessitate the need for radio waves when the internet is right there just waiting with a much broader reach.  The real issue is that it mostly just reaches me.

The nice part is, I don’t really mind.

I plan to put up a little page for Lameazoid Radio, but I’m still fleshing out the details.  A few people know the URL of the stream (hint: stream DOT) but I have no idea if they remember it and I doubt anyone listens.  I suppose the question is, why would anyone want to listen?  Can’t you get the same thing from your iPod?

This question includes myself.  Wouldn’t it be more beneficial to just load up the iPod and let it rip?  I do have some answer to this.  For example, my iPod isn’t large enough to hold my entire music library if I want it to.  It also is incredibly poor at shuffling music in a meaningful manner.  The Radio Automation software I’m using lets me sort things into nice themed play rotations.  i can interject little funny clips in at intervals as well to help break up the flow. 

I also run a lot of Podcasts.  The problem with using the iPod for Podcasts is that when it’s in my iTunes library, waiting to be played, I feel obligated to listen.  If it’s out of my control and playing on it’s own I can forgive and forget the playout if I miss one or come in partway through and leave early.  It’s expected I guess with radio that I may miss something.  It’s basically a subtle psychological point that works behind the scenes.

Anyway, I’ve managed to work the kinks out of the automated updates on Podcasts which leaves a few other projects that I want to implement on the table. 

First, I need to record some promos.  I want to make a few music based tags and a few others promoting the shows on the stream.  This basically requires time and a microphone.

Second I need to finish fleshing out the lineup.  I want to run new shows 7 days a week at 4 and 7.  I’ve got both slots about half filled now.  I also would like to find another short bit repeating news segment to compliment Tech 5 which plays every hour where there isn’t a show right now.

I also need to come up with something for the over night.  It’s likely no one will ever listen in the overnight so just running music all night is the easiest bet but I do like the idea of having some sort of “programming” there.  I’m thinking of pushing some audiobooks or something for lengthy periods but missing part of an audiobook is worse than missing part of a podcast.  I’m also thinking of compiling together my longform Techno mixes and running those.  Also possible some concerts in order.  Basically I’m looking to run longer blocks of related content in the overnight.

Thirdly i want to add Live Reporting.  I’ve set up Skype on this PC with auto answer for known callers.  This means i can call in and it’ll pick up.  Right now doing this kills the stream audio and it doesn’t return.  I have some software (freeware) that I believe I can use to create a gate that will shut off the stream when a call comes in and then resume it afterwards.  This would mean if I wanted to do “live reporting (to no one) I could call in through Skype on my phone.  Or better yet, if say, the GNR crew wanted to broadcast live they would simply call into the Skype and it would play.  On top of this I want to set up Skype to auto record and possibly auto rotate these short news bits.

Finally, at least on the list now, is to set up an automatic “now playing” Tweet.  I’m not sure the best way to accomplish this yet though the software does support a now playing.txt that is used by Icecast to set the title.  What wil be more likely is I’ll set up timed tweets for the time each show starts, like on @lameazoid.