Arduino Xport My SQL

by [~jamie|jamie allen], Feb 2007

This is some Arduino code that can be used to ask an XPORT or COBOX to call a php function. The idea being that the PHP function then puts the data passed to it into a MySQL database.

The below code is based on T.Igoe's earlier microcode example, and his php script for writing to the database is pasted in below.

/*  Arduino to Xport to PHP to MySQL

    Arduino Code for interfacing directly to an XPORT for
    posting to a MySQL database through a PHP script

    Originally developed by Tom Igoe, Oct 2005
    Translated from the Pic Basic by Jamie, Feb 2007

    Tom's php script is also down below...

XPORT SETTINGS 

*** Channel 1
Baudrate 9600, I/F Mode 4C, Flow 00
Port 10001
Remote IP Adr: --- none ---, Port 00000
Connect Mode : D4
Disconn Mode : 00
Flush   Mode : 00

*** Expert
TCP Keepalive    : 45s
ARP cache timeout: 600s
High CPU performance: disabled
Monitor Mode @ bootup : enabled
HTTP Port Number : 80
SMTP Port Number : 25

*/

// define variables:
int adcVar = 0;
int inByte = 0;
int tcpPin = 13;
int connected = 0;
long measurementPeriod = 60000; //i.e.: ~60 seconds between measurements

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

  // pause to let Xport boot up:
  delay(2000);
}

void loop()
{
    blinkie(5, 100);

    // if you're connected to the server, then 
    // make a HTTP call.  If not, then connect
    // to the server:

    if (connected == 1)
    {
     // read sensors, convert to bytes:
     adcVar = analogRead(0);
     // send HTTP GET request for php script:
     http_request();
    } else {
     // attempt to connect to the server:
     xport_connect();
    }

    digitalWrite(tcpPin, connected);
    // pause so we're not overwhelming the server:
    delay(measurementPeriod/2); 
    //Serial.print("After 3 second delay in main loop");
}

void xport_connect()
{
    // turn off LED to indicate HTTP GET is in progress:
    digitalWrite(tcpPin, LOW);

    //if (Serial.available() > 0) {
    // get incoming byte:
      inByte = Serial.read();
      //Serial.print(inByte);

      // wait for a "C" byte to come back:
      while (inByte != 67)
      {
        //Serial.print("Waiting for xport to send back C");
        Serial.print("C128.122.253.189/80");
        Serial.print(10, BYTE);
        delay(100);
        inByte = Serial.read();
      }

    connected = 1;    

}

void http_request()
{
    Serial.print("GET /~ja771/sensorworkshop/datalogger/sql_datalog.php?action=insert&");
    Serial.print("sensorValue=");
    Serial.print(adcVar,DEC);
    Serial.print(" HTTP/1.1");
    Serial.print(10, BYTE);
    Serial.print("HOST: itp.nyu.edu");
    Serial.print(10, BYTE);
    Serial.print(10, BYTE);

    //we should really be waiting for the return '0' here
    //from the script, but it just never seemed to come,
    //hanging the program in a while loop like the one above
    //so instead we just delay until the Xport lets go of it's connection
    //and then we try again

    delay(measurementPeriod/2);
    connected = 0;
}

void blinkie(int number, int speed)
{
  int i;
  for (i=0; i<number; i++)
  {
    digitalWrite(tcpPin, HIGH);
    delay(speed);
    digitalWrite(tcpPin, LOW);
    delay(speed);

  }
}


//////////////////////////////////////////////////
//THE PHP SCRIPT USED WITH THIS EXAMPLE IS BELOW//
//////////////////////////////////////////////////

/*

<?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);

// 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 ($action == "insert") {
    // make sure date and time have values:
    if ($date == -1 || $time == -1) {
        // 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);
}

?>

*/