Firstly, royalldonkey can you let us know of your progress, have you some success as yet?
@JohnHoward, please be mindful not to highjack the thread of royalldonkey, but if some of this helps out for you, that is good.
Here is a little bit of the php script I use to collect the data sent by the Arduino with the code posted a few posts up.
It listens with a switch statement depending on the type of http request, POST, GET, PUT or DELETE methods.
The following code details the workings for a POST message and decodes the JSON formatted data as sent by the Arduino and stores certain values to a real time single record database table as well as storing other values to a standard mult-record incrementing database table. Again, you don't need to use JSON, it is just that I do for pushing data around. I use CSV for pulling the data out for trends on the client side of things.
I have altered any specific information, and as such you need to specify SQL username, password and database and table names as for your own configuration.
Basic PHP code to run on a server with PHP and SQL installed.
<?php
// date_default_timezone_set("Australia/Tasmania");
// date_default_timezone_set('UTC');
// echo date_default_timezone_get(), "\r\n";
$today = date("Y-m-d H:i:s"); // 2001-03-10 17:16:18 (the MySQL DATETIME format)
echo $today, "\r\n";
$data = null;
// Here we work out if we received a POST, GET, PUT or DELETE request from the client:
switch($_SERVER['REQUEST_METHOD']) {
case 'POST':
// post method
POST_sql();
break;
case 'GET':
// get method
break;
case 'PUT':
// update method
break;
case 'DELETE':
// delete method
break;
}
//-------------------------------------------------------------------
// Arrive here if we received a POST message from the client:
// And decide which POST message we received from the Arduino:
// Data from Arduino is JSON encoded, so use php JSON decode:
//
function POST_sql() {
$rawdata = file_get_contents('php://input');
$data = json_decode($rawdata, true);
// echo "Raw: ", $rawdata,"\r\n"; // we can echo back the raw data received from Arduino:
$table = $data['id'];
switch($table) {
case "live";
// update live:
liveTable($data); // update live and trend tables:
break;
case "stat";
// update live:
statTable($data); // update stat table:
break;
case "debug";
// update debug:
debugTable($data); // update debug table:
break;
}
}
//-------------------------------------------------------------------
// function liveTable updates two databases;
// first is the real-time 'live' table,
// second is 'trendSensor' table:
//
function liveTable($dataLive) {
// vars from Arduino sent as integer, divided by scaling factor to store as floating point:
$vB_pv = $dataLive['vB_pv'] /100; // Battery Voltage VDC:
$vT_pv = $dataLive['vT_pv'] / 10; // Turbine Voltage VAC:
$iT_pv = $dataLive['iT_pv'] / 50; // Turbine Charge Current in Amps:
$iS_pv = $dataLive['iS_pv'] / 50; // Solar Charge Current in Amps:
$fT_pv = $dataLive['fT_pv'] / 100; // Turbine Frequency in Hz:
$vT_cvPID = $dataLive['vT_cvPID'] / 10.23; // Turbine PID Dump Load %:
$vS_cvPID = $dataLive['vS_cvPID'] / 10.23; // Solar PID Dumpl Load %:
$myConnection = mysql_connect('mydomain.mySqlHost.com', 'username', 'password');
if (!$myConnection) {
die('mysql connect error: ' . mysql_error() . "\r\n");
}
// update first database table, 'live', a single record table:
// Select the database 'live':
mysql_select_db(live, $myConnection);
// Prepare $sql_Live var for update of data into 'live' table:
$sql_Live="UPDATE live SET vB_pv = $vB_pv, vT_pv = $vT_pv, iT_pv = $iT_pv, iS_pv = $iS_pv, fT_pv = $fT_pv, vT_cvPID = $vT_cvPID, vS_cvPID = $vS_cvPID";
if (!mysql_unbuffered_query($sql_Live,$myConnection)) {
die('mysql update live error: ' . mysql_error() . "\r\n");
}
else {
echo "mysql update live ok \r\n";
}
// update second database table, 'trendSensor', a multi-record table:
// reference for the insert command - INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...)
$t = time() + 36000 + 3600; // get current server time, add offset for AEST + DST, crude must do this better:
$sql_Trend="INSERT INTO trendSensor (time, vB_pv, vT_pv, iS_pv) VALUES ($t, $vB_pv, $vT_pv, $iS_pv)";
if (!mysql_unbuffered_query($sql_Trend,$myConnection)) {
die('mysql update trend error: ' . mysql_error() . "\r\n");
}
else {
echo "mysql update trend ok \r\n";
}
mysql_close($myConnection);
}
?>
With the above code and with the code for the Arduino installed, one can view the responses received by the Arduino on the serial monitor.
Have fun peeps.
Kind regards,
Paul