YellowJacket Arduino with WiShield Wifi (Part 12)

Processing Data Obtained Over Wifi from an Arduino Data  Server — 

In Part 11 of this series, temperature and photocell data were sent as JSON formatted data from a YellowJacket Arduino data server (YJ56) to a YellowJacket Arduino client (YJ57) using the local wifi network.

In this Part 12, sensor data will be sent via wifi to a local Apache/php server, and php will write the data to a local mySQL data base table.

There are three software packages that had to be installed and implemented on the Mac used for software development:

• Apache (Ref: http://httpd.apache.org/download.cgi)
• php (Ref: http://php.net/manual/en/install.php)
• mySQL (Ref: http://dev.mysql.com/doc/refman/5.7/en/installing.html)

A fourth software, Sequel Pro (http://www.sequelpro.com), was installed to take advantage of its user-friendly GUI and ease of use. Each of these packages was installed individually rather than using MAMP or other bundled group of packages. MAMP was easily installed and made operational, but it installed in the user space rather than a system-wide install. After testing, MAMP was determined to conflict with Mac native installs of Apache, php, and MySQL. MAMP was then removed.

Each of these installs is fairly involved, decisions must be made about how to install them as either a system-wide or user install, file permissions must be set to work within the host computer OS security requirements, and network sharing must all be configured. This all can take minutes to hours to get working. There are several mySQL management packages that are a personal choice: Sequel Pro, MySqlWorkbench, or phpMyAdmin. Each takes some time to use proficiently, since data bases and data tables must be designed, and SQL queries are required to enter and manipulate data added to tables. Just move forward with the installs and soon they will all be working.

The installation and familiarity with Apache server, php, mySQL, and any of the mySQL management packages is outside the scope of this series. However, they will be required for the rest of this series, and must be installed on the user’s software development computer or network.

Other references (many on the web) that may help:

• https://discussions.apple.com/docs/DOC-3083
• http://osxdaily.com/2012/09/10/enable-php-apache-mac-os-x/
• http://www.sequelpro.com/docs/Install_MySQL_on_your_Mac
• http://jason.pureconcepts.net/2015/10/install-apache-php-mysql-mac-os-x-el-capitan/
• Google for help with any problems or issues

For this series on YellowJacket Arduino Wifi, the following versions of software were used:

• Terminal command >> Mac: me$ httpd -v
• Server version: Apache/2.4.18 (Unix)

• Safari browser command >> http://localhost/phpinfo.php
• PHP Version 5.5.34

• mySQL Version: 5.7.11

• Sequel Pro Version: 1.1.2

• Arduino IDE 1.6.5

• Mac OSX 10.11 El Capitan and 10.10 Yosemite

Some notes:

On Mac, localhost php files are stored in /Library/WebServer/Documents.

The localhost php default port is 80.

To test that the web page server is operational: Safari> localhost <cr> should return: It works! The web page server works independently of mySQL being on or off.

Mac /Library/WebServer/Documents is the location for Safari> “http://localhost/phpinfo.php” which gives the system info on Apache, php, and mysql.

The file Test.php was saved into the /Library/WebServer/Documents directory.

<?php
     echo "Hello from PHP on Mac";
?>

Safari: localhost/Test.php displays the “Hello from PHP on Mac”.

php uses port 80, mysql uses port 3306 on Mac.

Alternatively, local webpages are run from the “Sites” location (e.g., file:///Users/me/Sites/…). Sites was probably chosen as a standard location for html web pages since the /Library/ and /usr/ locations are system file folders with system security.

Sequel Pro: To connect to MySQL on Mac: Sequel Pro: “Favorites: Mac_MySQL:Arduino”.

Connect as “Socket”, Name: localhost, Username: root, Password: <Mac mysql password>, Database: Arduino, Socket: optional.

The login fields can be saved under “Name: Arduino” as “Sequel Pro:File:Add to Favorites” which will save the login info for a specific mysql database.

Submitting data from php to MySQL

Ref: http://www.icreateproject.info/uncategorized/arduino-save-data-to-database/

This Ref explains how to send data from Arduino through the local network to a php file which then submits the data to mysql for adding to a mysql data base table.

As in Ref, the mysql data base “Arduino” was created on Mac mySQL using Sequel Pro. The “Arduino” data base has table “sensor” to hold the data submitted by php. The “sensor” table has three fields: 1) id (automatic sequential integer), 2) time (automatic timestamp entered by mySQL), and 3) value (user added).

The php file “/Library/WebServer/Documents/write_data.php” contains the code to accept the data from the Arduino over the network and submit it to mySQL.

<?php
 // ref: http://www.icreateproject.info/uncategorized/
  arduino-save-data-to-database/
 // Prepare variables for database connection
 $dbhost = 'localhost:3306';
 $dbuser = "arduino";  // enter database username "arduino"
 $dbpass = "arduinotest";  // enter database password "arduinotest" 
 $conn = mysql_connect($dbhost, $dbuser, $dbpass);

 if(! $conn ) {
   die('Could not connect: ' . mysql_error());
 }

 // Prepare the SQL statement
 $sql = "INSERT INTO Arduino.sensor (value) VALUES (".$_GET["value"].")";    

 // Execute SQL statement
 mysql_select_db(‘Arduino’);
 $retval = mysql_query( $sql, $conn );

 if(! $retval ) {
   die('Could not enter data: ' . mysql_error());
 }

 echo "Entered data successfully\n";

 mysql_close($conn);

?>

http://localhost/write_data.php?value=92 writes the value 92 into the mysql “Arduino:sensor” table with the return statement “Entered data successfully”.

Write Temperature and Light Data to mySQL

In MySQL, add a new table “sensor2” with 4 fields:

• id (int 11, unsigned, key: Primary, extra: auto_increment)
• time (TIMESTAMP, default: CURRENT_TIMESTAMP)
• temperature (VARCHAR 10)
• light (VARCHAR 10)

Add a new php file, write_2data.php to your php documents directory:

<?php

 // File "write_2data.php"
 // written as part of Arduino sketch "Send2Values_04"
 // to insert two values into MySql table
 // Prepare variables for database connection

 $dbhost = 'localhost:3306';
 $dbuser = "arduino";  // enter database username
 $dbpass = "arduinotest";  // enter database password
 $conn = mysql_connect($dbhost, $dbuser, $dbpass);

 if(! $conn ) {
   die('Could not connect: ' . mysql_error());
 }

 // Prepare the SQL statement
 $sql = "INSERT INTO Arduino.sensor2 (temperature,light) VALUES (".$_GET["temperature"]. ",".$_GET["light"]. ")"; 
 //echo $sql;
 
 // Execute SQL statement
 mysql_select_db(‘Arduino’);
 $retval = mysql_query( $sql, $conn );

 if(! $retval ) {
   die('Could not enter data: ' . mysql_error());
 }

 echo "\nEntered 2 data values successfully into Table sensor2\n";

 mysql_close($conn);

?>

The Arduino Send2Values_04.ino sketch collects data from temperature and photocell sensors and submits those through php file write_2data.php which inserts a data record with two values (temperature (f-6.2 tempF), light(int photocell)) into MySql database “Arduino” and table “sensor2”.

/***************************************************
 * Send2Values_04
 *
 * March 10, 2016
 * This sketch
 *    reads two sensors
 *    processes the data
 *    creates a php request
 *    sends the php request to Apache
 *    the php request is submitted to mysql
 *    data elements are written to mysql table
 *
 *    The sketch can format the data as int, float, or char array
 *    and submit the request as a char array.
 *
 *    The dtostrf function for converting a float to a char array
 *    will preface the array with a blank for the - sign.
 *    Using an F0.2 format will dispose of leading blanks which
 *    cause errors loadint the data into mysql.
 *
 * The Atmel Reference Manual addresses the issue
 * of left or right justification by using the (-) Width. 
 * That does not fix the problem of padding with leading
 * or trailing blanks in the output to php.
 * 
 ****************************************************/

#include 
#include "SPI.h"

/************************* WiFi Access Point *********************************/

#define WLAN_SSID       "Network"
#define WLAN_PASS       "Network_Passcode"

/*---------------------------------------------------------------------------
 *
 * WiServer set up
 *
 */

#define WIRELESS_MODE_INFRA  1
#define WIRELESS_MODE_ADHOC 2

// Wireless configuration parameters ----------------------------------------
unsigned char local_ip[] = {10, 0, 1, 56}; // IP address of WiShield
unsigned char gateway_ip[] = {10, 0, 1, 1}; // router or gateway IP address
unsigned char subnet_mask[] = {255, 255, 255, 0}; // subnet mask for the local network
const char ssid[] PROGMEM = {"Network"};   // max 32 bytes
char* myhost_ip = "10.0.1.56";

unsigned char security_type = 3;  // 0 - open; 1 - WEP; 2 - WPA; 3 - WPA2
// WPA/WPA2 passphrase
const char security_passphrase[] PROGMEM = {"Network_Passcode"};  // max 64 characters

// WEP 128-bit keys
// sample HEX keys
const uint8_t wep_keys[] PROGMEM = {  0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x09, 0x0a, 0x0b, 0x0c, 0x0d, // Key 0
                                      0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // Key 1
                                      0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // Key 2
                                      0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00  // Key 3
                                   };

// setup the wireless mode
// infrastructure - connect to AP
// adhoc - connect to another WiFi device
unsigned char wireless_mode = WIRELESS_MODE_INFRA;

unsigned char ssid_len;
unsigned char security_passphrase_len;

//---------------------------------------------------------------------------

/****************************** Feeds ***************************************/
/* ---------------------------------------------------------------------------
*
*  A simple data logger for the Arduino analog pins
*
*/

//#define aref_voltage 3.3// we tie 3.3V to ARef and measure it with a multimeter!
#define aref_voltage 5.0  // default internal 5V to ARef

int photocellPin = 1;     // the cell and 10K pulldown are connected to a1
int photocellReading;     // the analog reading from the analog resistor divider

int temperaturePin = 0;   //the analog pin the TMP36's Vout (sense) pin is connected to
//the resolution is 10 mV / degree centigrade with a
//500 mV offset to allow for negative temperatures
int temperatureReading;   // the analog reading from the sensor

char inCMD[18] = "/write_2data.php?";
char requestCMD[80];

float tempF;
int tF;

/*---------------------------------------------------------------------------*/

/*************************** Sketch Code ************************************/

// Function that prints data returned from the server
void printData(char* data, int len) {
  // Note that the data is not null-terminated, may be broken up into smaller packets,
  // and includes the HTTP header.
  while (len-- > 0) {
    Serial.print(*(data++));
  }
}

// Function that assembles CMD to send to the server
void myCMD() {
  /* ----------------------- data logger ----------------------------------------*/
  // Read data

  photocellReading = analogRead(photocellPin);
  temperatureReading = analogRead(temperaturePin);
  // converting that reading to voltage, which is based off the reference voltage
  float voltage = temperatureReading * aref_voltage / 1024;
  // calculate temperature
  float temperatureC = (voltage - 0.5) * 100 ;  //converting from 10 mv per degree wit 500 mV offset
  //                                            //to degrees ((voltage - 500mV) times 100)
  //Serial.print(temperatureC); Serial.println(" degrees C");

  // convert C to Fahrenheit
  float temperatureF = (temperatureC * 9 / 5) + 32;

  tempF = temperatureF; // save alue in global tempF

  double dF = temperatureF;

  Serial.println("... ");
  // Serial.println(""); Serial.print("Light reading = "); Serial.println(photocellReading);
  // the raw analog reading

  // DEBUG the calculated temperatureF to the Serial Monitor
  Serial.print(temperatureF); Serial.println(F(" degrees F"));

  // conversion of int or float to char[] may not be needed, since "WiServer.print (tempF);"
  // displays the same number (83.49) as "dtostrf(temperatureF, 6, 2, data);"
  // which converts float to char[] array (data), f6.2 format but has leading space for - sign
  // do not know how WiServer.print formats to f6.2 by default.

  char data1[10];
  //sprintf will convert int to char, but not float to char which requires dtostrf

  dtostrf(dF, 0, 2, data1);        // converts float to char[] array (data)
  // f0.2 format forces left justify which deletes any leading spaces which cause problems in mysql
  Serial.print(data1); Serial.println(F(" data1 char dtostrf degrees F"));

  //tF = (int)temperatureF;
  //Serial.print(tF); Serial.println(F(" int degrees F"));

  char data2[10];
  sprintf(data2, "%i", photocellReading); // converts int to char[] array (data)
  Serial.print(data2); Serial.println(F(" data char int degrees Photocell"));

  /* -------------------End Data Logger ----------------------------------------*/

  // Send an int value to mysql works reliably
  //sprintf(requestCMD, "%s=%i", inCMD, tF); // appends int number; this works

  // Send an int value as a char[] to mysql works reliably
  //sprintf(requestCMD, "%s=%s", inCMD, data1); // appends char number.

  // Send a double value as a char[] to mysql works reliably with f0.1 format
  //sprintf(requestCMD, "%s=%s", inCMD, data); // appends dtostrf char double number.

  char tc[40];
  sprintf(tc, "%stemperature=%s", inCMD, data1); // appends dtostrf char double number.
  sprintf(requestCMD, "%s&light=%s", tc, data2); // appends dtostrf char double number.

  Serial.print(" requestCMD="); Serial.println(requestCMD);
}

uint8_t request_ip[] = {10, 0, 1, 11}; // IP Address for requested server Mac
char* myhost = "Mac_localhost";

// A request that gets a file from server at request_ip
GETrequest getMyRequest(request_ip, 80, myhost, requestCMD);

void setup() {
  Serial.begin(115200);
  delay(10);

  // Connect to WiFi access point.
  Serial.println();
  Serial.print(F("Init WiServer Client to External php-MySQL Server: "));
  Serial.println(myhost);

  // Initialize WiServer and have it use the sendMyPage function to serve pages
  // WiServer.init(sendMyPage);
  WiServer.init(NULL);

  // Ask WiServer to generate log messages (optional)
  WiServer.enableVerboseMode(true);

  // Have the processData function called when data is returned by the server
  getMyRequest.setReturnFunc(printData);

  Serial.println (">>> Send data on 30 sec intervals... ");
  Serial.println (" ");
}

// Time (in millis) when the data should be retrieved
unsigned long tempTime = millis();
unsigned long updateTime = tempTime;
unsigned long waitTime = 1000UL * 30UL; // * 30UL; seconds as long numbers

void loop() {

  tempTime = millis();

  // Check if it's time to get an update
  if (tempTime >= updateTime) {

    myCMD();

    Serial.println (" "); // space to display
    getMyRequest.submit();
    Serial.println (" "); // space to display
    // Get another update one hour from now
    updateTime += waitTime;
  }

  // Run WiServer
  WiServer.server_task();

  delay(100);
}

The Arduino Serial Monitor displayed:

Init WiServer Client to External php-MySQL Server: Mac_localhost
>>> Send data on 30 sec intervals... 
 
80.87 degrees F
80.87 data1 char dtostrf degrees F
327 data char int degrees Photocell
 requestCMD=/write_2data.php?temperature=80.87&light=327
 
 
Connected to Mac_localhost
TX 102 bytes
RX 0 bytes from Mac_localhost
RX 242 bytes from Mac_localhost
HTTP/1.1 200 OK
Date: Wed, 25 May 2016 16:25:54 GMT
Server: Apache/2.4.18 (Unix) PHP/5.5.34
X-Powered-By: PHP/5.5.34
Content-Length: 55
Connection: close
Content-Type: text/html


Entered 2 data values successfully into Table sensor2
Ended connection with Mac_localhost
... 
79.99 degrees F
79.99 data1 char dtostrf degrees F
306 data char int degrees Photocell
 requestCMD=/write_2data.php?temperature=79.99&light=306
 
 
Connected to Mac_localhost
TX 102 bytes
RX 0 bytes from Mac_localhost
RX 242 bytes from Mac_localhost
HTTP/1.1 200 OK
Date: Wed, 25 May 2016 16:26:24 GMT
Server: Apache/2.4.18 (Unix) PHP/5.5.34
X-Powered-By: PHP/5.5.34
Content-Length: 55
Connection: close
Content-Type: text/html


Entered 2 data values successfully into Table sensor2
Ended connection with Mac_localhost
... 

And the mySQL Arduino::sensor2 data table displayed:

id      time                    temperature     light
4 2016-05-25 12:26:54 79.99         332
5 2016-05-25 12:27:24 80.87         310

This exampled demonstrated:

• data collection by Arduino
• converting the int or float data to char array values
• packaging the char array data as name/value pairs similar to JSON formatting
• transmitting the data package via wifi to a Mac-based Apache/php server
• php writing the data package into a mySQL data base table “sensor2”

The next example will expand on this work to:

• collect the data on YJ56 as a data server
• convert the int or float data to char array values
• transmit the data package via wifi to YJ57 as JSON formatted data
• YJ57 will transmit the data via wifi to a Mac-based Apache/php server
• php write the data package into a mySQL data base table

(Mar 10, 2016)