Featherwing Ethernet Mysql Datalogger

This is a tutorial to help you to get data from your Arduino compatible into your mysql database so that you can use it on a website. I used an IRremote in this example however, you can easily modify this to fit your needs and work with multiple different sensors.

The goal of this is to show people how to capture data from a microcontroller and log it in a mysql database. So that it can be used on the web. I am making an IRremote that will be able to change content on a website by taking the IR info from the remote and storing it into a central mysql database. This instructable will get data from the remote into your database so you can use it to control web applications or log data onto the web.

You could also set up several of these from different locations and have them all storing data in a central database with minor modification.

This instructable will allow you to capture infrared codes from an infrared remote and store them in a mysql database on a remote web server. This can work with any type of sensor really but I used an infrared remote because that is what I have here.
In this project I used the following things materials and software.

1) You will need a hosting account that runs MYSQL and PHP. With a domain name pointed to your hosting account that you can access on the internet. In this tutorial I just used a simple Godaddy hosting account.

2) I used an Adafruit Feather 32u4 Bluefruit LE microcontroller available here on the Adafruit Website however this will also work with other feather models compatible with theAdafruit Ethernet FeatherWing.

3) You will need an Adafruit Ethernet FeatherWing which available here on the Adafruit Website

4) I used an Elegoo IR Remote I cannot find where you can get this remote without purchasing a whole kit but you can use any IR Remote that uses Arduino IRremote Library.

5) Of course you will also need a breadboard and Ethernet Cable.

What you want to do now is to create a new mysql database on your webserver. In this example I am using PhpMyAdmin. Once you have a database created run the following SQL statement and thats it.

           CREATE TABLE `loggingtable` (
            `id` int(20) NOT NULL AUTO_INCREMENT,
            `json` text,
            `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `ip_address` varchar(20) DEFAULT '0.0.0.0',
            `flag` tinyint(1) NOT NULL,
            PRIMARY KEY (`id`)
            ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Now you want to open up your web server and create a directory called "mysqldatalogger". Than create a new file in the directory and call it index.php . Then you will open the file and add the following code to it. Than change the database connection settings to the settings you used for you database user.

<?

############################################################
#  Mysql Data Logger For Feather with Ethernet Support     #
#  Author: John Anderson                                   #
#  Location: Marshfield,VT 05658                           #
#  Email: Chillininvt@yahoo.com                            #
#  Website :www.vermontinternetdesign.com                  #
#  Licence: You can do whatever you want with this code    #
#  just give credit where rightfully deserved and leave    #
#  this intact.                                            #
############################################################
/*
       
   This script will take a string of url parameters URLTOSCRIPTDIR/index.php?param=1&m2=2&m3=3 
   than encodes it as a string of JSON and saves it to a mysql database on your server. 
   You will have to modify the Aurduino code if you would like to accept more variables than one. However, this script will 
   log the entire $_GET array.    

*/
class corefunctions{
    
    //Configure your database connection settings here.
    private $hostname = "";  // Your database host usually localhost but check your connection settings.
    private $username = "";  // Your Database username
    private $dbname = "";    // Your Database name
    private $password = "";  // Your database password
    
    protected $approved_ips = Array(); // I would add a list of IP addresses approved to connect to this script.
    
    protected function __construct(){
       //Connect to database
        mysql_connect($this->hostname, $this->username, $this->password) OR DIE ("Unable to connect to database! Please try again later.");
        mysql_select_db($this->dbname);    }}
class mysqlDataLogger extends corefunctions{
     public $clean = '';
     public $ipaddress = '';
     public function __construct(array $get){
        parent::__construct();
        $this->ipaddress = $_SERVER['REMOTE_ADDR'];
        $this->clean = $this->filter_data($get);
        $this->insertRecord($this->clean);
        
     }
     //Filters inputs this is not a fool proof security measure. I would recommend further tokenizing access to this script
     private function filter_data(array $data){
        $out = Array();               
        foreach($data as $k => $v){
         
           $out[$k] = mysql_real_escape_string($v);     
        }
        
        return $out; 
     }
     // Encodes Parameters in $_GET array and stores it into mysql Database
     protected function insertRecord(array $data, $flag = 0){
        $json = json_encode($data);
        $sql = "INSERT INTO loggingtable (json, ip_address, flag) VALUES ('".$json."','".$this->ipaddress."','".$flag."')";
        mysql_query($sql) or die(mysql_error());
     }
}

//This will only trigger is $_GET['data'] is set
if($_GET['data'] != '')
$instance = new mysqlDataLogger($_GET);<br>?>

In this tutorial I did not stack the ethernet wing onto the feather I hooked it up like the picture above. However, if your feather has stacking headers you can stack the ethernet wing onto the feather. Otherwise, you can set it up like I have above. Make sure that your IR receiver is hooked to the power and the ground and its data pin is connected to pin 11 on the Arduino. If you flip over the ethernet wing you will see pins that are labeled connect those to the corresponding pins on the feather. You do not need to connect all of the pins. Looks like I have 4 connections on the short side and six connections on the long side. They are labeled the same.

Once you are hooked up plug your ethernet cable into the slot.

Now you will want to open up the Arduino IDE and connect it to your feather. Once you have it ready to go make sure that you have SPI, Ethernet2, and IRremote libraries installed. If you don't know how to do that find a tutorial on installing Arduino Libraries. Anyway once you have done that load the following sketch onto the board. Make sure that you have the URLHOST and URLPATH set up to match your configuration.

<p>/*<br> 
 Featherwing Mysql Datalogger - With Infrared Remote
 By, John Anderson 
 Email: chillininvt@yahoo.com
 Web:http://www.vermontinternetdesign.com</p><p> This code modifies the below Adafruit Sample Code.
  
  Web client
 
 This sketch connects to a website (http://www.adafruit.com)
 using an Arduino Wiznet Ethernet shield/FeatherWing.
 
 created 18 Dec 2009
 by David A. Mellis
 modified 9 Apr 2012
 by Tom Igoe, based on work by Adrian McEwen
 
 */
 
#include SPI.h
#include Ethernet2.h
#include IRremote.h

int RECV_PIN = 11; // Infrared Recieve Pin on Microcontroller</p><p>IRrecv irrecv(RECV_PIN);</p><p>decode_results results; </p><p>// Enter a MAC address for your controller below.
// Newer Ethernet shields have a MAC address printed on a sticker on the shield
byte mac[] = { 0x98, 0x76, 0xB6, 0x10, 0x61, 0xBD };
// if you don't want to use DNS (and reduce your sketch size)
// use the numeric IP instead of the name for the server:
//IPAddress server(74,125,232,128);  // numeric IP for Google (no DNS)</p><p>#define URLHOST "www.YourDomainGoesHere.com"    // name of your domain on your hosting account or server</p><p>#define URLPATH "/mysqldatalogger/index.php" //Path to Mysql Datalogger on your web server
 
// Set the static IP address to use if the DHCP fails to assign
IPAddress ip(192, 168, 0, 102);
 
// Initialize the Ethernet client library
// with the IP address and port of the server
// that you want to connect to (port 80 is default for HTTP):
EthernetClient client;
 
unsigned long lastConnectionTime = 0;             // last time you connected to the server, in milliseconds
const unsigned long postingInterval = 10L * 1000L; // delay between updates, in milliseconds
// the "L" is needed to use long type numbers
 
//#define WIZ_RESET 9
 
#if defined(ESP8266)
  // default for ESPressif
  #define WIZ_CS 15
#elif defined(ESP32)
  #define WIZ_CS 33
#elif defined(ARDUINO_STM32_FEATHER)
  // default for WICED
  #define WIZ_CS PB4
#elif defined(TEENSYDUINO)
  #define WIZ_CS 10
#elif defined(ARDUINO_FEATHER52)
  #define WIZ_CS 11
#else   // default for 328p, 32u4 and m0
  #define WIZ_CS 10
#endif
 
 
void setup() {
#if defined(WIZ_RESET)
  pinMode(WIZ_RESET, OUTPUT);
  digitalWrite(WIZ_RESET, HIGH);
  delay(100);
  digitalWrite(WIZ_RESET, LOW);
  delay(100);
  digitalWrite(WIZ_RESET, HIGH);
#endif
 
#if !defined(ESP8266) 
  while (!Serial); // wait for serial port to connect.
#endif
 
  // Open serial communications and wait for port to open:
  Serial.begin(115200);
  delay(1000);
  Serial.println("\nHello! I am the Ethernet FeatherWing");
  irrecv.enableIRIn(); 
  Ethernet.init(WIZ_CS);
  
  // give the ethernet module time to boot up:
  delay(1000);
 
  // start the Ethernet connection:
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    // no point in carrying on, so do nothing forevermore:
    // try to congifure using IP address instead of DHCP:
    Ethernet.begin(mac, ip);
  }
  
  // print the Ethernet board/shield's IP address:
  Serial.print("My IP address: ");
  Serial.println(Ethernet.localIP());
}
 
void loop() {
  // if there's incoming data from the net connection.
  // send it out the serial port.  This is for debugging
  // purposes only:
  if (client.available()) {
    char c = client.read();
    Serial.write(c);
  }
 
  // if ten seconds have passed since your last connection,
  // then connect again and send data:
  if (millis() - lastConnectionTime > postingInterval) {
     
      if (irrecv.decode(&results)) {
            //Serial.println(results.value);
            String sendData = String(results.value);  
            httpRequest(sendData);
      }
  }
 
}
 
// this method makes a HTTP connection to the server:
void httpRequest(String params) {
  // close any connection before send a new request.
  // This will free the socket on the WiFi shield
        client.stop();
        Serial.println(params);
  // if there's a successful connection:
  
    if (client.connect(URLHOST, 80)) {  
        
        Serial.println("connecting...");
    // send the HTTP PUT request:
        client.println("GET " URLPATH "?data="+ params +" HTTP/1.1");
        client.println("Host: " URLHOST);
        client.println("User-Agent: arduino-ethernet");
        client.println("Connection: close");
       client.println();
    
    // note the time that the connection was made:
    lastConnectionTime = millis();
  }
  else {
    // if you couldn't make a connection:
    Serial.println("connection failed");
  }
 
}</p>

At this point you should be connecting to mysql and storing data from the IRremote. Hit some buttons on the IR remote and then open your database up and make sure that your data is in there. There once you have the IR data into the database you can use whatever you would like to access mysql and use the data on the internet.

This is my first time making an instructable so if you have any question or comments feel free to email me at info@vermontinternetdesign.com with any questions or concerns or visit my website at http://www.vermontinternetdesign.com