{"id":482,"date":"2015-05-01T07:18:25","date_gmt":"2015-05-01T14:18:25","guid":{"rendered":"http:\/\/internetofhomethings.com\/homethings\/?p=482"},"modified":"2015-05-01T07:19:10","modified_gmt":"2015-05-01T14:19:10","slug":"using-php-to-read-esp8266-data","status":"publish","type":"post","link":"https:\/\/internetofhomethings.com\/homethings\/?p=482","title":{"rendered":"Using php to read ESP8266 data"},"content":{"rendered":"

While I\u00a0have published another post,\u00a0<\/a>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<\/a> is anything here is unclear.<\/p>\n

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\u00a0will not work. So here is my php script to pull data from my ESP8266 and save the values in a mySQL database.<\/p>\n

<?php\r\n\r\ninclude(\"access.php\");\r\nsession_start(); \/\/We need to have static variables\r\n$Esp8266SensorURL = $esp8266_9702_GetSensors_URL;\r\n\r\n\/\/---------------------------------------------------------------------\r\n\/\/Functions\r\n\/\/---------------------------------------------------------------------\r\n\/\/\r\n\/\/---------------------------------------------------------------------\r\n\/\/Name: get_fcontent( $url, $javascript_loop = 0, $timeout = 5 )\r\n\/\/Function: CURL that gets URL contents\r\n\/\/Parameter 1: $url - URL to retrieve\r\n\/\/Parameter 2: $javascript_loop - IDK, this function pulled from internet\r\n\/\/Parameter 3: $timeout - IDK, this function pulled from internet\r\n\/\/---------------------------------------------------------------------\r\nfunction get_fcontent( $url, $javascript_loop = 0, $timeout = 15 ) {\r\n    $url = str_replace( \"&amp;\", \"&\", urldecode(trim($url)) );\r\n    $cookie = tempnam (\"\/tmp\", \"CURLCOOKIE\");\r\n    $ch = curl_init();\r\n    curl_setopt( $ch, CURLOPT_URL, $url );\r\n    curl_setopt( $ch, CURLOPT_COOKIEJAR, $cookie );\r\n    curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, true );\r\n    curl_setopt( $ch, CURLOPT_ENCODING, \"\" );\r\n    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );\r\n    curl_setopt( $ch, CURLOPT_AUTOREFERER, true );\r\n    curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, false ); # required for https urls\r\n    curl_setopt( $ch, CURLOPT_CONNECTTIMEOUT, $timeout );\r\n    curl_setopt( $ch, CURLOPT_TIMEOUT, $timeout );\r\n    curl_setopt( $ch, CURLOPT_MAXREDIRS, 10 );\r\n    $content = curl_exec( $ch );\r\n    $response = curl_getinfo( $ch );\r\n    curl_close ( $ch );\r\n\r\n    if ($response['http_code'] == 301 || $response['http_code'] == 302) {\r\n        ini_set(\"user_agent\", \"Mozilla\/5.0 (Windows; U; Windows NT 5.1; rv:1.7.3) Gecko\/20041001 Firefox\/0.10.1\");\r\n\r\n        if ( $headers = get_headers($response['url']) ) {\r\n            foreach( $headers as $value ) {\r\n                if ( substr( strtolower($value), 0, 9 ) == \"location:\" )\r\n                    return get_url( trim( substr( $value, 9, strlen($value) ) ) );\r\n            }\r\n        }\r\n    }\r\n\r\n    if ( ( preg_match(\"\/>[[:space:]]+window\\.location\\.replace\\('(.*)'\\)\/i\", $content, $value) || preg_match(\"\/>[[:space:]]    +window\\.location\\=\\\"(.*)\\\"\/i\", $content, $value) ) && $javascript_loop < 5) {\r\n        return get_url( $value[1], $javascript_loop+1 );\r\n    } \r\n    else {\r\n        return array( $content, $response );\r\n    }\r\n}\r\n\r\n\/\/---------------------------------------------------------------------\r\n\/\/Name: getEsp8266Sensor($getUrl,$update,$k)\r\n\/\/Function: Gets value from ESP8266 (Return last value if 0)\r\n\/\/Parameter 1: $getUrl - ESP8266 URL that returned JSON with sensor values\r\n\/\/Parameter 2: $update - if \"Y\", get JSON from ESP8200, else use last JSON\r\n\/\/Parameter 3: $k - key to retrieve value of (key:value)\r\n\/\/---------------------------------------------------------------------\r\nfunction getEsp8266Sensor($getUrl,$update,$k) {\r\n    if($update==\"Y\") {\r\n        $_SESSION['fetches']=0;\r\n        $status[0]=\"\";\r\n        ob_start();\r\n        while((substr($status[0],0,1)!='{')&&($_SESSION['fetches']<20)) {\r\n            $status=get_fcontent($getUrl);\r\n            $_SESSION['fetches']=$_SESSION['fetches']+1;\r\n            ob_flush();\r\n            sleep(5);\r\n        }\r\n        $_SESSION['jsonstr'] = $status[0];\r\n    }\r\n    if($_SESSION['fetches']<20) {\r\n        $status_array = json_decode($_SESSION['jsonstr']); \/\/Decode json\r\n        foreach($status_array as $key => $value) {\r\n            if(strstr($key,$k)) {\r\n                $val = $value;\r\n            }\r\n        }\r\n    }\r\n    else {\r\n        $val=0;\r\n    }\r\n    \/\/return last value if current value is 0\r\n    if($val!=0) {\r\n        return $val;\r\n    }\r\n    else {\r\n        if(strstr($k,\"DS_TempInside\")) return $last_in;\r\n        if(strstr($k,\"DS_TempOutside\")) return $last_ou;\r\n        if(strstr($k,\"DS_TempAttic\")) return $last_at;\r\n        if(strstr($k,\"DH_Humidity\")) return $last_hu;\r\n        return $val;\r\n    }\r\n}\r\n\r\n\/\/Insert \"spaces\" spaces\r\nfunction insertSpace($spaces) {\r\n    for ($cnt=0; $cnt<$spaces; $cnt++) {\r\n        $sp .= \"&nbsp\";\r\n    }\r\n    return $sp;\r\n}\r\n\r\n\/\/---------------------------------------------------------------------\r\n\/\/Connect to database\r\n\/\/---------------------------------------------------------------------\r\n\r\n$link = mysqli_connect(\"localhost\", $mysqlUser, $mysqlPass, $mysqlUser);\r\nif (mysqli_connect_error()) {\r\n    die(\"Could not connect to database\");\r\n}\r\n\/\/---------------------------------------------------------------------\r\n\/\/Get latest values from database\r\n\/\/---------------------------------------------------------------------\r\n$query = \"SELECT * FROM temperature ORDER BY id DESC LIMIT 1\";\r\n\r\nif($result=mysqli_query($link, $query)) {\r\n    $row = mysqli_fetch_array($result);\r\n    $last_id = $row['id'];\r\n    $last_in = $row['inside'];\r\n    $last_ou = $row['outside'];\r\n    $last_at = $row['attic'];\r\n    $last_hu = $row['humidity'];\r\n}\r\n\r\n\/\/---------------------------------------------------------------------\r\n\/\/Get Sensor Data\r\n\/\/---------------------------------------------------------------------\r\n\r\n\/\/----------------------------------------\r\n\/\/-----Get current LOCAL time\r\n\/\/----------------------------------------\r\n$now = time();\r\ndate_default_timezone_set('America\/Los_Angeles');\r\n$localtime_assoc = localtime($now, true);\r\n\r\n\/\/Time\r\n$now_hr = sprintf('%02u',$localtime_assoc['tm_hour']);\r\n$now_mn = sprintf('%02u',$localtime_assoc['tm_min']);\r\n$now_sc = sprintf('%02u',$localtime_assoc['tm_sec']);\r\n$now_tm = $now_hr.\":\".$now_mn.\":\".$now_sc;\r\n\r\n\/\/Date\r\n$now_yr = $localtime_assoc['tm_year']+1900;\r\n$now_mo = sprintf('%02u',$localtime_assoc['tm_mon']+1);\r\n$now_dy = sprintf('%02u',$localtime_assoc['tm_mday']);\r\n$now_dt = $now_yr.\"-\".$now_mo.\"-\".$now_dy;\r\n\r\n\/\/Get Temperatures\r\n$temp_in = getEsp8266Sensor($Esp8266SensorURL,\"Y\",\"DS_TempInside\");\r\n$temp_ou = getEsp8266Sensor($Esp8266SensorURL,\"N\",\"DS_TempOutside\");\r\n$temp_at = getEsp8266Sensor($Esp8266SensorURL,\"N\",\"DS_TempAttic\");\r\n\r\n\/\/Get Humidity\r\n$temp_hu = getEsp8266Sensor($Esp8266SensorURL,\"N\",\"DH_Humidity\");\r\n\r\n\/\/Get Free Heap and ms since ESP8266 started\r\n$temp_hp = getEsp8266Sensor($Esp8266SensorURL,\"N\",\"SYS_Heap\");\r\n$temp_tm = getEsp8266Sensor($Esp8266SensorURL,\"N\",\"SYS_Time\");\r\n\r\n\/\/Insert a record into mySql\r\n$query = \"INSERT INTO `temperature` (`inside`,`outside`,`attic`,`humidity`,`time`,`date`,`FreeHeap`,`SysTime`,`fetches`)\r\nVALUES('\".$temp_in.\"','\".$temp_ou.\"','\".$temp_at.\"','\".$temp_hu.\"','\".$now_tm.\"','\".$now_dt.\"','\".$temp_hp.\"','\".$temp_tm.\"','\".$_SESSION['fetches'].\"')\";\r\n\/\/Report Results\r\nif ($result=mysqli_query($link, $query)) {\r\n    echo('<br>Success<br>');\r\n}\r\nelse {\r\n    echo('<br>Failed<br>');\r\n}\r\n\r\n?>\r\n\r\n<\/pre>\n
Now let’s break this down.<\/p>\n
include(\"access.php\");\r\n$Esp8266SensorURL = $esp8266_9702_GetSensors_URL;<\/pre>\n
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.<\/p>\n
session_start(); \/\/We need to have static variables $Esp8266SensorURL = $esp8266_9702_GetSensors_URL;<\/pre>\n
session_start() enables the use of static variables<\/p>\n
function get_fcontent( $url, $javascript_loop = 0, $timeout = 15 )<\/pre>\n
This function retrieves the contents returned from an URL, using CURL.<\/p>\n
function getEsp8266Sensor($getUrl,$update,$k) {\r\n    if($update==\"Y\") {\r\n        $_SESSION['fetches']=0;\r\n        $status[0]=\"\";\r\n        ob_start();\r\n        while((substr($status[0],0,1)!='{')&&($_SESSION['fetches']<20)) {\r\n            $status=get_fcontent($getUrl);\r\n            $_SESSION['fetches']=$_SESSION['fetches']+1;\r\n            ob_flush();\r\n            sleep(5);\r\n        }\r\n        $_SESSION['jsonstr'] = $status[0];\r\n    }\r\n<\/pre>\n
 <\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
The session variable $_SESSION[‘jsonstr’] saves the returned JSON string for subsequent calls to this function, when a request is not needed.<\/p>\n
The rest of the function simply decodes the JSON string and extracts the requested key value.<\/p>\n
The code snippet below retrieves the latest values stored in the database.<\/p>\n
\/\/---------------------------------------------------------------------\r\n\/\/Get latest values from database\r\n\/\/---------------------------------------------------------------------\r\n$query = \"SELECT * FROM temperature ORDER BY id DESC LIMIT 1\";\r\n\r\nif($result=mysqli_query($link, $query)) {\r\n    $row = mysqli_fetch_array($result);\r\n    $last_id = $row['id'];\r\n    $last_in = $row['inside'];\r\n    $last_ou = $row['outside'];\r\n    $last_at = $row['attic'];\r\n    $last_hu = $row['humidity'];\r\n}\r\n<\/pre>\n
These values are used if no valid values are received from the ESP8266 during the http GET transaction.<\/p>\n
I leave the rest of the code up to you to understand. It is all very straightforward stuff.<\/p>\n
Here is an extract from the data stored in mySQL. The data is\u00a0recorded every hour. This slice is from 3 am to 2\u00a0pm April\u00a030,2015. As you can see, the temperatures bottomed at 6 am and the worst case performance for the software was at 9 and 11\u00a0am when 6\u00a0GET requests were required before it was received successfully.<\/p>\n
\"temperature<\/a><\/p>\n
Wow, things really got toasty in my attic that day! 119.9 F at 1 pm.<\/p>\n
Hope you find this information as useful to you as it has been to me. Until next time…<\/p>\n","protected":false},"excerpt":{"rendered":"
While I\u00a0have published another post,\u00a0covering 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 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,10],"tags":[],"class_list":["post-482","post","type-post","status-publish","format-standard","hentry","category-alltheposts","category-esp8266"],"_links":{"self":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts\/482","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=482"}],"version-history":[{"count":5,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts\/482\/revisions"}],"predecessor-version":[{"id":505,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts\/482\/revisions\/505"}],"wp:attachment":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=482"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=482"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=482"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}