Processing PHP My SQL Client

Consolidated here by jamie allen, Feb 2007

(Written by Tom Igoe)

Below is three code listings you can use to write sensor data to a txt file on the network

1. Processing code that takes in Serial from the Arduino and then calls a PHP script on the network.
2. The Arduino code that produces the serial sensor data stream.
3. A php script that receives actions and data to pass to a MySQL database.
   
   

/* datalogging client
 by Tom Igoe

  All of this is described here:
  http://www.tigoe.net/pcomp/code/archives/php/000713.shtml

  Communicates between a microcontroller reading a 10-bit analog sensor and
  a PHP script, via Processing.  The PHP script logs the data to at MySQL database
  for later queries, etc. 

  The program starts by sending to the sensor for an initial value.
  When a good sensor value is obtained, the program checks to see if there's 
  a net send in progress.  If there's not, it sends the sensor reading to the net
  by starting a network client that connects to a server on port 80,
  sends an HTTP 1.1 GET request, and prints the results. 

  Once the sensor request is done, the client waits 3 seconds before the next send
  so as not to overwhelm the server.

  created 18 March 2005
  updated 24 Oct. 2005
  Jamie Allen did some code consolidation for Sensor Workshop, Feb 2007

 */

import processing.net.*;
import processing.serial.*;

Serial port;                            // The serial port
Client client;                          // the net client
int[] serialInArray = new int[2];       // Where we'll put what we receive
int serialCount = 0;                    // A count of how many bytes we receive
int sensorValue = 0;                    // value of the sensor
boolean firstContact = false;           // whether we've heard from the microcontroller
boolean netSendInProgress = false;      // whether or not the last net request is finished
boolean newSensorData = false;          // whether or not we have new sensor data

void setup()
{
  size(200, 200);
  // Print a list of the serial ports, for debugging purposes:
  println(Serial.list());

  // I know that the first port in the serial list on my mac
  // is always my  Keyspan adaptor, so I open Serial.list()[0].
  // On Windows machines, this generally opens COM1.
  // Open whatever port is the one you're using.
  port = new Serial(this, Serial.list()[2], 9600);
  port.write(65);    // Send a capital A to start the microcontroller sending
}

void draw()
{
  background(0);

// if there's any serial data available, get it:
  if (port.available() > 0) {
    serialEvent();
    // Note that we heard from the microntroller at least once:
    firstContact = true;
  }
  // If there's no serial data, send again until we get some incoming data.
  // (in case you tend to start Processing before you start your 
  // external device):
  if (firstContact == false) {
    delay(300);
    port.write(65);
  }

  // if we have new sensor data, check to see that there's no open
  // net connections. If there aren't, send the data.
  if (newSensorData) {
    if (!netSendInProgress) {
      sendToNet(sensorValue);
    }
  }


  // print the results of the net send:
  if (netSendInProgress) {
    if (client.available() > 0) {
      int inByte = client.read();
      print((char)inByte);
      // when we get a byte of value 0, it's the end of the response
      // from the server.  Stop listening and get some more data:
      if (inByte == 0) {
      netSendInProgress = false;
      // don't overwhelm the server:
      delay(3000);
      // Send a capital A to request new sensor readings:
      port.write(65);
      }
    }
  }
}
p

void serialEvent() {
  // Add the latest byte from the serial port to array:
  serialInArray[serialCount] = port.read();
  serialCount++;
  // If we have 2 bytes, combine them into one value:
  if (serialCount > 1 ) {
    sensorValue = serialInArray[1] * 256 + serialInArray[0];
    newSensorData = true;

    // Reset serialCount:
    serialCount = 0;
  } 
  else {
  // if we have only one byte, don't let the main loop 
  // send out yet:
    newSensorData = false;
  }
}


///SEND TO NET FOR PHP TO MYSQL 
void sendToNet(int sensorValue) {
  // open a TCP socket to the host:
  client = new Client(this, "itp.nyu.edu", 80);

  //formatting 
  println();
  println();

  //print the IP address of the host:
  println(client.ip());

  // send the HTTP GET request:
  // These lines form a call to the sql_datalog.php script to insert
  // the passed value into the mySQL table specified in sql_datalog.php
  // We also have to convert the sensor data into a string 

  String sensorString = Integer.toString(sensorValue);
  client.write("GET /~ja771/sensorworkshop/datalogger/sql_datalog.php?action=insert&sensorValue=" + sensorValue + " HTTP/1.1\n");
  client.write("HOST: itp.nyu.edu\n\n");
  netSendInProgress = true;
}


////////////////////////////////////////////////////////////
// BELOW IS THE ARDUINO CODE THAT WORKS WITH THIS EXAMPLE //
////////////////////////////////////////////////////////////

/*
analog Call-and-Response for 10 bit sensor readings
by Tom Igoe

Created 26 Sept. 2005
Updated 30 May 2006

Cleaned up for use with the Network Data Logging Suite
by Jamie Allen, Feb 2007

Waits for serial input.  If the incoming value
is a valid byte (i.e. "A"), the program then
reads two analog inputs and one digital input.
It slices up the data into Most and Least Significant
BYTES and sends them out sequential to the
serial port

Arduino hardware connections:
A0: analog sensor on analog in 0

*/

/*
int firstSensor = 0;    // first analog sensor
int inByte = 0;         // incoming serial byte
byte firstSensorLSB;    //LSB for the analog sensor value
byte firstSensorMSB;    //MSB for the analog sensor value

void setup()
{
  // start serial port at 9600 bps:
  Serial.begin(9600);
}

void loop()
{
  // if we get a valid byte, read analog ins:
  if (Serial.available() > 0) {
    // get incoming byte:
    inByte = Serial.read();

    if (inByte == 65)  //i.e.: it's an ASCII "A"
    {
    firstSensor = analogRead(0);
    firstSensorLSB = firstSensor;
    firstSensorMSB = firstSensor >> 8;

    Serial.print(firstSensorLSB, BYTE);
    Serial.print(firstSensorMSB, BYTE);
    }
  }
}
*/


///////////////////////////////////////////////////
// BELOW IS THE PHP THAT IS USED IN THIS EXAMPLE //
///////////////////////////////////////////////////

/* Copy the script below into a file called sql_datalog.php
and put it somewhere on your webserver mine was here 
(YOU NEED TO CHANGE THIS TO YOUR OWN SERVER, php locations):
/~ja771/sensorworkshop/datalogger/sql_datalog.php

Create appropriate fields in your MySQL database
Value (as int)
Timestamp (as TIME)
Date (as date)

*/

/*

<?php
// get username & pwd info:
include "secret.php";

// initialize variables:
$sensorValue = -1;        // value from the sensor
$date = -1;                // date string: YYYY-MM-DD
$time = -1;                // time string: HH:mm:ss in 24-hour clock
$recordNumber = -1;        // which record to delete
$list = 0;                // whether or not to list results in HTML format
$databaseName = 'ja771';
$tableName = 'datalogger';

// open the database:
$link = open_database('localhost', $databaseName, $username, $password);

//Note that our request is of the form
//sql_datalog.php?action=insert&sensorValue=" + sensorValue + " HTTP/1.1\n;

// process all the HTTP GET variables:
while(list($key, $value) = each($HTTP_GET_VARS)) 
{ 
    // action is the SQL action: insert, delete, select, etc.
    if ($key == "action") {
        $action = $value;
    }
    // sensorValue is the result from the remote sensor system
    if ($key == "sensorValue") {
        $sensorValue = $value;
    }
    // date that the sensor reading was taken
    if ($key == "date") {
        $date = $value;    
    }
    // time that the sensor reading was taken
    if ($key == "time") {    
        $time = $value;
    }
    // database record number (for deleting only):
    if ($key == "recNum") {    
        $recordNumber = $value;
    }
    // whether or not to print out results in HTML:
    if ($key == "list") {    
        $list = $value;
    }

}

// insert a new record in the database:
if ($saction == "insert") {
    // make sure date and time have values:
    if ($date == -1 || $time == -1) {  
        //here we have the option of sending the date and time from Arduino
        // if not values, generate them from the server time
        // (I should probably properly check for valid date and time strings here):
        list($date, $time) = split(" ", date("Y-m-d H:i:s"));
    }

    // Only insert if we got a sensor value from the GET:
    if (sensorValue != -1) {
        insert_record($tableName, $sensorValue, $date, $time);
    }
}

// if we're supposed to delete, delete:
if ($action == "delete") {
    // only delete if we got a record number from the GET:
    if ($recordNumber != -1) {
        delete_record($tableName, $recordNumber);
    }
}

// if we should list in HTML format, list the whole table:
if ($list == 1) {
    echo "<html><head></head><body>";
    // browse the whole table:
    browse_table($tableName);
    echo "</body></html>";
}

// close the database:
close_database($link);

// end with a 0 to close the session to the client:
echo "\0";
end;

//    Functions    -------------------------------

// Connect to a server and open a database:
function open_database($myServer, $myDatabase, $myUser, $myPwd) {
    $myLink = mysql_connect($myServer, $myUser, $myPwd)
       or die('Could not connect: ' . mysql_error());
    if ($list == 1) {
        echo 'Connected successfully';
    }
    mysql_select_db($myDatabase) or die('Could not select database');
    return $myLink;
}

// close an open database:
function close_database($myLink) {
    mysql_close($myLink);
}

// select all from a table:
function browse_table($myTable) {
    $query = "SELECT * FROM `$myTable`";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    // Printing results in HTML
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "\t<tr>\n";
           foreach ($line as $col_value) {
               echo "\t\t<td>$col_value</td>\n";
           }
           echo "\t</tr>\n";
    }
    echo "</table>\n";
    // Free resultset
    mysql_free_result($result);
}

// insert a new record in the table:
function insert_record($myTable, $recValue, $recDate, $recTime) {
    $query = "INSERT INTO `$myTable` (`Value`, `Date`, `Timestamp`) VALUES ('$recValue', '$recDate','$recTime')";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    // Free resultset
    mysql_free_result($result);

}

// delete a record from the table:
function delete_record($myTable, $recNum) {
    $query = "DELETE FROM `$myTable` WHERE `ID` = $recNum  LIMIT 1";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    // Free resultset
    mysql_free_result($result);
}

?>

*/