SQL

IOT Projects Programming Projects

Pushing Arduino Data to MySQL via PHP

Ramen Junkie 2Comments

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.

« Previous Page