Downloading Ambient Weather Station Data into MariaDB/MySQL

<?php
//php 5.6 compatible
$debug = true;  //until this runs smoothly, look at system emails to get details if running in crontab

// this script needs php8.1-curl installed to work as well

// MAC Address:C8:C9:A3:16:B8:XX
// API Key: b3c0c4cbe4af495dbdb5597d72d5a7d6a6516aa76c874edaa1aee8a7acaeb4XX
// Application Key: 49cc82371bb4405b8f94442ed7aea30aa08d41cbe7af40a98994f0916116a9XX

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

//set this to your timezone
date_default_timezone_set('America/New_York');

//enter your Ambient api key and app key information
$site['AWNdid']   = 'id'; // your xx:xx:xx:xx:xx:xx device ID on Ambientweather.net
$site['AWNkey']   = 'apikey';    //apiKey - request this from Ambient
$site['SWXAWNAPPID'] = 'appkey';  //applicationKey - request this from Ambient

//set your mysql database variables
$hostname = 'IP or hostname'; //usually localhost
$database = 'weather';
$password = 'password';
$username = 'user';
$table = 'zastmet';  
   
//connect to mysql
$link = mysqli_connect($hostname, $username, $password, $database);
if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); }

//get unix timestamp for last entry
$sql_last = 'SELECT dateutc FROM `'.$table.'` WHERE dateutc=(SELECT MAX(dateutc) FROM `'.$table.'`);';
$result = mysqli_query($link,$sql_last); 
$row = mysqli_fetch_array($result);
$last_update = $row['dateutc'];
date_default_timezone_set('UTC');  //I think time() is the unix timestamp independant of timezone, but just to be sure . . . ) AW stores timestamps UTC
$now = time();
date_default_timezone_set('America/New_York'); //now switch it back

$limit = floor( ($now - $last_update)/300 );  //database has a new record every 300 seconds
if ($limit > 288) {$limit = 288;}  //max number of records
if ($limit == 0) { 
    echo "\nThere is no time gap in the database presently, ending script.<br> \n";
    exit();
}
$end_date = time()*1000;              // UTC by default and the timestamp is in microseconds on the Ambient server

/////////////////////////////////////////////////////////
///////////// URLs from AW to get data //////////////////
/////////////////////////////////////////////////////////

// Provides a list of the user's available devices along with each device's most recent data.
$AWNDEVurl = 'https://api.ambientweather.net/v1/devices/'.
             '?applicationKey='.$site['SWXAWNAPPID'].
             '&apiKey='.$site['AWNkey'];
//We won't use this, but I put it in here in case I want it later

// Fetch data for a given device. Data is stored in 5 or 30 minute increments.
// https://api.ambientweather.net/v1/devices/macAddress?apiKey=&applicationKey=&endDate=&limit=288
$AWNurl = 'https://api.ambientweather.net/v1/devices/'.$site['AWNdid']. 
    '?applicationKey='.$site['SWXAWNAPPID'].
		'&apiKey='.$site['AWNkey'];
$AWNurl = $AWNurl.'&endDate='.$end_date;
$AWNurl = $AWNurl.'&limit='.$limit;

echo 'URL to fetch is :'.$AWNurl.'<br><br>';
if ($debug) {
    echo "\nThe last row in the MySQL database is from: ".date('g:ia jS F Y', $last_update )."<br> \n";
    echo "Current system time is: ".date('g:ia jS F Y', $now )."<br> \n";
    echo "Thus, we will be trying to add ".$limit." records to the MySQL database.<br> \n";
}

// FROM AW DOCUMENTATION:
//A list of all possible fields is here: 
//   https://github.com/ambient-weather/api-docs/wiki/Device-Data-Specs

// QUERY DEVICE DATA:
//https://api.ambientweather.net/v1/devices/macAddress?apiKey=&applicationKey=&endDate=&limit=288

//      endDate = 
//          The most recent datetime. Results descend from there. 
//          If left blank, the most recent results will be returned. 
//          Date format should be in milliseconds since the epoch or 
//          string representations outlined here: 
//          https://momentjs.com/docs/#/parsing/string/
//          Note: datetimes are stored in UTC.

//      limit = 
//          The maximum number of results to return (max: 288) Default: 288.


list($RC,$data) = AWN_fetchData($AWNurl); // do the data fetch 

if($RC == 0 || $RC == 429) {
    if ($debug) { 
        echo "\nDid not get any data - RC = ".$RC.". Exiting script\n"; 
    }
    exit();
    $weather_dataJSON = '';
}
elseif($RC == 200) {
    $weather_dataJSON = json_decode($data, true); //this means we got data y'all!!
} 
elseif ($RC = 401 || $RC = 404) {
		//$json = json_decode($content);
    if ($debug) { 
        print "\nError: Ambientweather.net returns ERROR: ".$RC.". Exiting script\n"; 
    }
    exit();
    $weather_dataJSON = '';
}
else {
    if ($debug) {echo "\nAWN_fetchData error, RC = ".$RC." on fetch of ".$AWNurl.". Exiting script\n"; }
    exit();
    $weather_dataJSON = ''; 
}

if($weather_dataJSON){   //assuming we got weather data back...use the first array data set to get the data names. That way if a weather sensor is not reporting, we don't get a row mismatch error from mysql
    $sql = 'INSERT IGNORE INTO '.$table.' (';  //We're building the mysql insert statement!!!
    foreach ($weather_dataJSON[0] as $server_column_name=>$server_value ) {  //use the first data set to get this info
        $server_columns_arr[] = $server_column_name;   //we'll need an array of the data headings to call the actual values from the data arrays later. . .
        $sql = $sql.' '.$server_column_name.',';   //and adding column names to the statement
        
        // comment below out after debugging
        // echo "<br>\n Server Column Names: ".$server_column_name." <br>\n";
    }
    $sql = rtrim($sql, ',');      // get rid of that last comma to avoid an error . . . .
    $sql = $sql.') ';             // and close the parenthesis
}

if($weather_dataJSON){
    $sql = $sql.'VALUES ';
    $data_set_counter = 0;
    //if ($debug) { echo "\nEnding date/time for the data we got = ".date('g:ia \o\n l jS F Y', ($weather_dataJSON[0]['dateutc']/1000) )." \n"; }
    foreach ($weather_dataJSON as $key=>$data_arr) {     //here $key is going to be an integer
        $date = ($data_arr['dateutc']/1000) - 25200;
        $date = ($data_arr['dateutc']/1000);
        $date = date('g:ia \o\n l jS F Y', $date);
        //echo '<p>At '.$date.':<br>';
        $sql = $sql.' (';
        //foreach ($data_arr as $measure=>$value){
        foreach ($server_columns_arr as $value){     //loop through the keys of the data we got from the server
            //if($measure == 'date' || $measure == 'lastRain' ) {
            if($value == 'date' || $value == 'lastRain' ) {
                //$value = str_replace('T', ' ', $value);
                //$value = str_replace('.000Z', '', $value);
                //$value = '\''.$value.'\'';
                $data_arr[$value] = str_replace('T', ' ', $data_arr[$value]);  //make date into a mysql format
                $data_arr[$value] = str_replace('.000Z', '', $data_arr[$value]);  //make date into a mysql format
                $data_arr[$value] = '\''.$data_arr[$value].'\''; //escape the quotes
                //probably should mysqli_real_escape_string this stuff . . .
            }
            if($value == 'loc') { $data_arr[$value] = '\''.$data_arr[$value].'\'';}
            if($value == 'dateutc') { $data_arr[$value] = $data_arr[$value]/1000;}

            // comment line below when not testing
           // echo $value.' = '.$data_arr[$value].', ';
            $sql = $sql.' '.$data_arr[$value].',';
        } 
        $sql = rtrim($sql, ',');      // get rid of that last comma to avoid an error . . . .     
        $sql = $sql.'),';
        $data_set_counter++;
        //echo '</p>';       
    }
    $sql = rtrim($sql, ',');      // get rid of that last comma to avoid an error . . . .     
}
if ($debug) { echo "\nWith api limit set to ".$limit.", API rest call sent us ".$data_set_counter." records in total \n"; }
$last = count($weather_dataJSON)-1;
if ($debug) { 
    echo "\nStarting date/time for the API data we got = ".date('g:ia jS F Y', ($weather_dataJSON[$last]['dateutc']/1000) )." \n"; 
    echo "Ending date/time for the API data we got = ".date('g:ia jS F Y', ($weather_dataJSON[0]['dateutc']/1000) )." \n";
}
// debug statement
echo '<br><br><br><br>MySQL statement =<br><br>'.$sql;

if (mysqli_query($link, $sql)) {
    if ($debug) { echo "\nMySQL query: ".mysqli_affected_rows($link)." new rows added successfully. \n"; }
} 
else {
    if ($debug) { echo "\nError: ".$sql."<br>".mysqli_error($link)." \n"; }
}

//see if there is a data gap > 7 minutes in the data stream
//$last_update = $row['dateutc']; //remember this from above?
$first_record_added = $weather_dataJSON[$last]['dateutc']/1000;
$data_gap = $first_record_added - $last_update;
if($data_gap < 430) {
    if ($debug) {
        echo "\nData appears to be in continuity - time gap between the last record on the database and the new added data rows is ".$data_gap." seconds. (".($data_gap/60)." minutes). \n\n\n"; 
    }
}
else {
    if ($debug) {
        echo "\nThere may be a data continuity issue, the time gap was '.$data_gap.' You may want to check data. \n\n\n"; 
    }
}

mysqli_close($link); //close it down, nuthin' to see here . . .
////////////////////////////////////////////////////////////
////////////////////////// FUNKSHINS ///////////////////////
////////////////////////////////////////////////////////////


function AWN_fetchData($AWNurl) {
    $numberOfSeconds = 3; //you may have to futz with this depending on your server connectivity
    $ch = curl_init();    
    curl_setopt($ch, CURLOPT_URL, $AWNurl); // connect to provided URL
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0); // don't verify peer certificate
    curl_setopt($ch, CURLOPT_USERAGENT, 'horca.net');   
    curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Accept: text/plain,application/json" ) ); // request LD-JSON format :
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // return the data transfer
    curl_setopt($ch, CURLOPT_NOBODY, false); // set nobody
    curl_setopt($ch, CURLOPT_HEADER, FALSE);
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $numberOfSeconds); //  connection timeout
    curl_setopt($ch, CURLOPT_TIMEOUT, $numberOfSeconds); //  data timeout
        
    $data = curl_exec($ch); // execute session
    $cinfo = curl_getinfo($ch); // get info on curl exec. 
    if(isset($cinfo['http_code'])) { $RC = $cinfo['http_code']; } 
    else { $RC = 0; }
    $to_return = array($RC,$data);    
    curl_close($ch);    
    return $to_return; 
}

// Table Design. The table needs to be very bare (columns must match JSON keys, all columns must be nullable
// triggers broke my arrays for some reason) for the SQL to work. Use a view on the table to get fancy.

/* CREATE TABLE `zastmet` (
    `dateutc` bigint(20) DEFAULT NULL,
    `tempf` double DEFAULT NULL,
    `humidity` double DEFAULT NULL,
    `windspeedmph` double DEFAULT NULL,
    `windgustmph` double DEFAULT NULL,
    `maxdailygust` double DEFAULT NULL,
    `winddir` double DEFAULT NULL,
    `uv` double DEFAULT NULL,
    `solarradiation` double DEFAULT NULL,
    `hourlyrainin` double DEFAULT NULL,
    `eventrainin` double DEFAULT NULL,
    `dailyrainin` double DEFAULT NULL,
    `weeklyrainin` double DEFAULT NULL,
    `monthlyrainin` double DEFAULT NULL,
    `yearlyrainin` double DEFAULT NULL,
    `totalrainin` double DEFAULT NULL,
    `battout` double DEFAULT NULL,
    `tempinf` double DEFAULT NULL,
    `humidityin` double DEFAULT NULL,
    `baromrelin` double DEFAULT NULL,
    `baromabsin` double DEFAULT NULL,
    `feelsLike` double DEFAULT NULL,
    `dewPoint` double DEFAULT NULL,
    `feelsLikein` double DEFAULT NULL,
    `dewPointin` double DEFAULT NULL,
    `lastRain` timestamp NULL ,
    `date` timestamp NULL DEFAULT '0000-00-00 00:00:00',
    -- uniq key to keep dupes out
    UNIQUE KEY `uniq_recs` (`dateutc`,`tempf`,`humidity`,`windspeedmph`,`windgustmph`)
  )  */

?>