Using php to read ESP8266 data

While I have published another post, covering this topic using a Javascript AJAX call, using php has unique attributes, and must be used in certain cases. Please refer to that post is anything here is unclear.

In my case, I needed to periodically save my home weather sensor values into a mySQL database. While this can be accomplished with a CRON triggered php server side script, the client sided AJAX will not work. So here is my php script to pull data from my ESP8266 and save the values in a mySQL database.

<?php

include("access.php");
session_start(); //We need to have static variables
$Esp8266SensorURL = $esp8266_9702_GetSensors_URL;

//---------------------------------------------------------------------
//Functions
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
//Name: get_fcontent( $url, $javascript_loop = 0, $timeout = 5 )
//Function: CURL that gets URL contents
//Parameter 1: $url - URL to retrieve
//Parameter 2: $javascript_loop - IDK, this function pulled from internet
//Parameter 3: $timeout - IDK, this function pulled from internet
//---------------------------------------------------------------------
function get_fcontent( $url, $javascript_loop = 0, $timeout = 15 ) {
    $url = str_replace( "&", "&", urldecode(trim($url)) );
    $cookie = tempnam ("/tmp", "CURLCOOKIE");
    $ch = curl_init();
    curl_setopt( $ch, CURLOPT_URL, $url );
    curl_setopt( $ch, CURLOPT_COOKIEJAR, $cookie );
    curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, true );
    curl_setopt( $ch, CURLOPT_ENCODING, "" );
    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_setopt( $ch, CURLOPT_AUTOREFERER, true );
    curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, false ); # required for https urls
    curl_setopt( $ch, CURLOPT_CONNECTTIMEOUT, $timeout );
    curl_setopt( $ch, CURLOPT_TIMEOUT, $timeout );
    curl_setopt( $ch, CURLOPT_MAXREDIRS, 10 );
    $content = curl_exec( $ch );
    $response = curl_getinfo( $ch );
    curl_close ( $ch );

    if ($response['http_code'] == 301 || $response['http_code'] == 302) {
        ini_set("user_agent", "Mozilla/5.0 (Windows; U; Windows NT 5.1; rv:1.7.3) Gecko/20041001 Firefox/0.10.1");

        if ( $headers = get_headers($response['url']) ) {
            foreach( $headers as $value ) {
                if ( substr( strtolower($value), 0, 9 ) == "location:" )
                    return get_url( trim( substr( $value, 9, strlen($value) ) ) );
            }
        }
    }

    if ( ( preg_match("/>[[:space:]]+window\.location\.replace\('(.*)'\)/i", $content, $value) || preg_match("/>[[:space:]]    +window\.location\=\"(.*)\"/i", $content, $value) ) && $javascript_loop < 5) {
        return get_url( $value[1], $javascript_loop+1 );
    } 
    else {
        return array( $content, $response );
    }
}

//---------------------------------------------------------------------
//Name: getEsp8266Sensor($getUrl,$update,$k)
//Function: Gets value from ESP8266 (Return last value if 0)
//Parameter 1: $getUrl - ESP8266 URL that returned JSON with sensor values
//Parameter 2: $update - if "Y", get JSON from ESP8200, else use last JSON
//Parameter 3: $k - key to retrieve value of (key:value)
//---------------------------------------------------------------------
function getEsp8266Sensor($getUrl,$update,$k) {
    if($update=="Y") {
        $_SESSION['fetches']=0;
        $status[0]="";
        ob_start();
        while((substr($status[0],0,1)!='{')&&($_SESSION['fetches']<20)) {
            $status=get_fcontent($getUrl);
            $_SESSION['fetches']=$_SESSION['fetches']+1;
            ob_flush();
            sleep(5);
        }
        $_SESSION['jsonstr'] = $status[0];
    }
    if($_SESSION['fetches']<20) {
        $status_array = json_decode($_SESSION['jsonstr']); //Decode json
        foreach($status_array as $key => $value) {
            if(strstr($key,$k)) {
                $val = $value;
            }
        }
    }
    else {
        $val=0;
    }
    //return last value if current value is 0
    if($val!=0) {
        return $val;
    }
    else {
        if(strstr($k,"DS_TempInside")) return $last_in;
        if(strstr($k,"DS_TempOutside")) return $last_ou;
        if(strstr($k,"DS_TempAttic")) return $last_at;
        if(strstr($k,"DH_Humidity")) return $last_hu;
        return $val;
    }
}

//Insert "spaces" spaces
function insertSpace($spaces) {
    for ($cnt=0; $cnt<$spaces; $cnt++) {
        $sp .= "&nbsp";
    }
    return $sp;
}

//---------------------------------------------------------------------
//Connect to database
//---------------------------------------------------------------------

$link = mysqli_connect("localhost", $mysqlUser, $mysqlPass, $mysqlUser);
if (mysqli_connect_error()) {
    die("Could not connect to database");
}
//---------------------------------------------------------------------
//Get latest values from database
//---------------------------------------------------------------------
$query = "SELECT * FROM temperature ORDER BY id DESC LIMIT 1";

if($result=mysqli_query($link, $query)) {
    $row = mysqli_fetch_array($result);
    $last_id = $row['id'];
    $last_in = $row['inside'];
    $last_ou = $row['outside'];
    $last_at = $row['attic'];
    $last_hu = $row['humidity'];
}

//---------------------------------------------------------------------
//Get Sensor Data
//---------------------------------------------------------------------

//----------------------------------------
//-----Get current LOCAL time
//----------------------------------------
$now = time();
date_default_timezone_set('America/Los_Angeles');
$localtime_assoc = localtime($now, true);

//Time
$now_hr = sprintf('%02u',$localtime_assoc['tm_hour']);
$now_mn = sprintf('%02u',$localtime_assoc['tm_min']);
$now_sc = sprintf('%02u',$localtime_assoc['tm_sec']);
$now_tm = $now_hr.":".$now_mn.":".$now_sc;

//Date
$now_yr = $localtime_assoc['tm_year']+1900;
$now_mo = sprintf('%02u',$localtime_assoc['tm_mon']+1);
$now_dy = sprintf('%02u',$localtime_assoc['tm_mday']);
$now_dt = $now_yr."-".$now_mo."-".$now_dy;

//Get Temperatures
$temp_in = getEsp8266Sensor($Esp8266SensorURL,"Y","DS_TempInside");
$temp_ou = getEsp8266Sensor($Esp8266SensorURL,"N","DS_TempOutside");
$temp_at = getEsp8266Sensor($Esp8266SensorURL,"N","DS_TempAttic");

//Get Humidity
$temp_hu = getEsp8266Sensor($Esp8266SensorURL,"N","DH_Humidity");

//Get Free Heap and ms since ESP8266 started
$temp_hp = getEsp8266Sensor($Esp8266SensorURL,"N","SYS_Heap");
$temp_tm = getEsp8266Sensor($Esp8266SensorURL,"N","SYS_Time");

//Insert a record into mySql
$query = "INSERT INTO `temperature` (`inside`,`outside`,`attic`,`humidity`,`time`,`date`,`FreeHeap`,`SysTime`,`fetches`)
VALUES('".$temp_in."','".$temp_ou."','".$temp_at."','".$temp_hu."','".$now_tm."','".$now_dt."','".$temp_hp."','".$temp_tm."','".$_SESSION['fetches']."')";
//Report Results
if ($result=mysqli_query($link, $query)) {
    echo('<br>Success<br>');
}
else {
    echo('<br>Failed<br>');
}

?>

Now let’s break this down.

include("access.php");
$Esp8266SensorURL = $esp8266_9702_GetSensors_URL;

The included “access.php” file contains definitions for my URLs used to access my home devices. Maintaining these in a separate file keeps things tidy and hidden. The variable $esp8266_9702_GetSensors_URL is assigned in the access.h file to my ESP8266 that implements a web server listening on port 9702.

session_start(); //We need to have static variables $Esp8266SensorURL = $esp8266_9702_GetSensors_URL;

session_start() enables the use of static variables

function get_fcontent( $url, $javascript_loop = 0, $timeout = 15 )

This function retrieves the contents returned from an URL, using CURL.

function getEsp8266Sensor($getUrl,$update,$k) {
    if($update=="Y") {
        $_SESSION['fetches']=0;
        $status[0]="";
        ob_start();
        while((substr($status[0],0,1)!='{')&&($_SESSION['fetches']<20)) {
            $status=get_fcontent($getUrl);
            $_SESSION['fetches']=$_SESSION['fetches']+1;
            ob_flush();
            sleep(5);
        }
        $_SESSION['jsonstr'] = $status[0];
    }

 

Now this is the meat of this method. The function getEsp8266Sensor() mechanizes the process of retrieving values from a JSON string returned from an ESP8266. Since a single GET request returns a JSON string that contains all the sensor values, only one request is needed, the first one. The “$update” parameter is set to “Y” from the caller to perform the GET request. The GET is declared a success if the first character (after the header) returned is “{“, the start of the expected JSON string.

I have observed this request to fail sometimes, which is why it is put in a while loop, allowing up to 20 retries. Since one of the values stored in the mySQL is the number of GET requests required before a successful attempt, I have data to characterize the failure rate. After running this script 3 times per hour for a few days now, a maximum of six attempts before success have been recorded. Twenty retries were set to provide some margin. I’ll be reviewing the data as more run time is accumulated.

Notice the ob_start() and ob_flush() statements. This was necessary to clear out the buffers from the last GET request. Without these, the request often failed continuously, regardless of how many retries were attempted.

The session variable $_SESSION[‘jsonstr’] saves the returned JSON string for subsequent calls to this function, when a request is not needed.

The rest of the function simply decodes the JSON string and extracts the requested key value.

The code snippet below retrieves the latest values stored in the database.

//---------------------------------------------------------------------
//Get latest values from database
//---------------------------------------------------------------------
$query = "SELECT * FROM temperature ORDER BY id DESC LIMIT 1";

if($result=mysqli_query($link, $query)) {
    $row = mysqli_fetch_array($result);
    $last_id = $row['id'];
    $last_in = $row['inside'];
    $last_ou = $row['outside'];
    $last_at = $row['attic'];
    $last_hu = $row['humidity'];
}

These values are used if no valid values are received from the ESP8266 during the http GET transaction.

I leave the rest of the code up to you to understand. It is all very straightforward stuff.

Here is an extract from the data stored in mySQL. The data is recorded every hour. This slice is from 3 am to 2 pm April 30,2015. As you can see, the temperatures bottomed at 6 am and the worst case performance for the software was at 9 and 11 am when 6 GET requests were required before it was received successfully.

Wow, things really got toasty in my attic that day! 119.9 F at 1 pm.

Hope you find this information as useful to you as it has been to me. Until next time…

Share This: