PHP - Internet of Things Web Logger

PHP - Internet of Things Web Logger

By Daniel
Fri, 06/30/2017 - Updated 11 months ago
0 comments

Server side PHP Script that captures any HTTP GET request data and stores in a CSV spreadsheet. Can be used with Raspberry PI circuits, Arduino, IntelGalileo, IntelEdison and even with Smartwatches that can connect to the internet using Ethernet cables, Bluetooth, USB or Wifi.

Description

This is a PHP script that records data from Internet of Things (IoT) devices in a CSV spreadsheet that can be opened from Excel. If you also need a web view for the log, check the CSV Viewer for Web Browsers.

Features

  • Input is automatically decoded from the URLs by using the HTTP GET standard (see examples below).
  • Records different variable sets in the same spreadsheet, each variable goes to the column it belongs.
  • If a new variable is set, a column for it will be automatically created.
  • No specific order required for the input data.
  • Records the timestamp of the request and the IP address of the client.
  • Output in the CSV format that can be directly opened in Excel.
  • Output is UTF-8 compatible (at lease should be).
  • If the log reaches the maximum size, older entries will be automatically purged.
  • Return TRUE if the record was set and FALSE in case of problems.
  • The parameter ?LogClear=TRUE clears the table data and columns.
  • Web display for the log available in CSV Viewer for Web Browsers.
  • If a password is set, the parameter ?LogPass=yourpassword will be required to allow data to be inserted in the table.

Requirements

  • Webserver with PHP 5.1.x or higher installed.
  • Permissions for the script to read, write and delete in the target CSV folder.

Installation

Just put the script in the folder and run it. There is a configuration section in the code for further adjustments.

Examples

  • Request 1: http://www.mysite.com/iotcsvlog.php
  • Request 2: http://www.mysite.com/iotcsvlog.php?Temperature=22
  • Request 3: http://www.mysite.com/iotcsvlog.php?Temperature=22&Humidity=60
  • Request 4: http://www.mysite.com/iotcsvlog.php?Humidity=80&Temperature=25
  • Request 5: http://www.mysite.com/iotcsvlog.php?Pressure=1000
  • Request 6: http://www.mysite.com/iotcsvlog.php?Temperature=30&Pressure=900&Humidity=50
  • Request 7 (with password set): http://www.mysite.com/iotcsvlog.php?Humidity=50&LogPass=correct_password
  • Request 8 (with password set): http://www.mysite.com/iotcsvlog.php?Humidity=50&LogPass=wrong_password
    Wrong pass! No data logged.
  • Request 9 (with password set): http://www.mysite.com/iotcsvlog.php?Humidity=50
    Wrong pass! No data logged.
  • Request 10: http://www.mysite.com/iotcsvlog.php?LogClear=TRUE
    New empty table is created.
  • Request 11 (with password set): http://www.mysite.com/iotcsvlog.php?LogClear=TRUE&LogPass=correct_password
    New empty table is created.
Output table from request 1, 10 and 11
Date and Time IP
2017-06-30 10:27:45 -0300 192.168.1.1

 

Output table from requests 1 and 2

Date and Time IP Temperature
2017-06-30 10:27:45 -0300 192.168.1.1  
2017-06-30 10:28:48 -0300 192.168.1.1 22

 

Output table from requests 1 to 9

Date and Time IP Temperature Humidity Pressure
2017-06-30 10:27:45 -0300 192.168.1.1      
2017-06-30 10:28:48 -0300 192.168.1.1 22    
2017-06-30 10:29:32 -0300 192.168.1.1 22 60  
2017-06-30 10:33:20 -0300 192.168.1.1 25 80  
2017-06-30 10:48:55 -0300 192.168.1.1     1000
2017-06-30 10:55:30 -0300 192.168.1.1 30 50 900
2017-06-30 10:59:19 -0300 192.168.1.1   50  

Known issues

The script was designed to output the CSV in the UTF-8 format so it could support a wide range of languages and character sets. Unfortunately not all systems support UTF-8 in the same way and some characters might still be displayed incorrectly. To solve this problem, configure the PHP script to output the result file in the .txt format and then open it in Excel using the Text Import function. This way you will be able to proper adjust the importation character encoding.

Source code

  1. <?php
  2. /* =============================================================
  3. / * Internet of Things IoT CSV Logger
  4. * Version 1.2 (04/07/2017)
  5. *
  6. * This application logs any HTTP GET requests in a CSV file that can be directly opened by Excel.
  7. * The GET variable names will be used as column headers and the values will sorted in the rows.
  8. * The application can log completely different GET requests in the same CSV file and will automatically add the timestamp and the IP address of the client.
  9. * In case of success return TRUE, in case of errors return FALSE.
  10. *
  11. * Developed by Daniel Brooke Peig - [email protected]
  12. * http://www.danbp.org
  13. * Copyright 2017 - Daniel Brooke Peig
  14. *
  15. * This software is distributed under the MIT License.
  16. * Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
  17. *
  18. * The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
  19. *
  20. * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  21. *
  22. *
  23. /* =============================================================*/
  24.  
  25. //------------------------------------------------
  26. //Configurations
  27. //
  28. $fileName = "iotcsvlog.csv";
  29. date_default_timezone_set('America/Sao_Paulo');
  30. $delimiter = ";";
  31. $enclosure = '"';
  32. $maxLogSize = 10000; //Most Excel versions can read only about 16000 lines
  33. $password = ''; //Optional to prevent abuse. If set to [your_password] will require the &LogPass=[your_password] GET parameter to allow the data to be recorded.
  34. //------------------------------------------------
  35.  
  36. //Variable initialization
  37. $logLines = array();
  38. $inputLine = array();
  39. $responseMessage = "FALSE";
  40.  
  41. if($_GET["LogClear"] === "TRUE"){
  42. //If this is set, the source file will not be processed and will be erased after the new log entry is created
  43. }
  44. else if(file_exists($fileName)){ // File exists
  45.  
  46. // Reads lines of file to array
  47. $fileLines = file($fileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
  48.  
  49. //Not Empty file
  50. if($fileLines !== array()){
  51.  
  52. //Extract the existing header from the file
  53. $lineHeader = array_shift($fileLines);
  54. $logOriginalHeader = array_map('trim', str_getcsv(substr($lineHeader,3), $delimiter, $enclosure));
  55.  
  56. //Process the file only if the system could find a valid header
  57. if(count($logOriginalHeader) > 0) {
  58. //Define the new header
  59. $logHeader = array_combine($logOriginalHeader, $logOriginalHeader);
  60.  
  61. //Get each line of the array
  62. $linesToSkip = (count($fileLines)+1>$maxLogSize) ? (count($fileLines)-$maxLogSize+1) : 0; //Trim the log size
  63. foreach ($fileLines as $line) {
  64. if(trim($line) !== ''){ //Remove blank lines
  65. if($linesToSkip <= 0) {
  66. $arrayFields = array_map('trim', str_getcsv($line, $delimiter, $enclosure)); //Convert line to array
  67. $logLines[] = array_combine($logOriginalHeader, $arrayFields); //Uses the existing headers as keys for the log lines
  68. }
  69. else $linesToSkip--;
  70. }
  71. }
  72. }
  73. }
  74. }
  75. //Set the mandatory log headers (if they were not there during the file load)
  76. $logHeader["Date and Time"] = "Date and Time";
  77. $logHeader["IP"] = "IP";
  78.  
  79. //Now process the inputs
  80. foreach($_GET as $key => $value){
  81. //Performs some cleaning and adjustments
  82. if($key == "Date and Time") $key = "Date and Time_user"; //Prevents overwriting internal data
  83. if($key == "IP") $key = "IP_user"; //Prevents overwriting internal data
  84. $key = preg_replace('/[^\w\-\s\.]/', '', trim($key)); //Allow only alphanumeric characters, underscore and hyphens in the header
  85. $value = str_replace($enclosure, '\'', trim($value)); //Escape the delimiter and enclosure
  86.  
  87. if(($key != "LogPass" || $password ==='') && $key != "LogClear"){ //Ignore the password parameter if the password is set
  88. $logHeader[$key] = $key; //Adds the new header cell if needed
  89. $inputLine[$key] = $value; //Defines the input array
  90. }
  91. }
  92.  
  93. //Set defaults to the system internal fields
  94. $outputLine["Date and Time"] = date("Y-m-d H:i:s O"); //Set the timestamp
  95. $outputLine["IP"] = $_SERVER['REMOTE_ADDR']; //
  96. $outputLine = (count($inputLine)>0) ? array_merge($outputLine, $inputLine) : $outputLine;
  97.  
  98. //Put the new output into the log array
  99. array_push($logLines, $outputLine);
  100.  
  101. //Open an output temporary file, if it does not exist create it
  102. $fileW = fopen($fileName.".tmp","w+");
  103.  
  104. //If the system was able to open the file
  105. if($_GET["LogPass"] === $password || $password === '')
  106. if($fileW !== false){
  107.  
  108. //File headers
  109. fputs($fileW, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) )); //BOM for Excel UTF-8
  110.  
  111. //Write the table header
  112. foreach($logHeader as $headerKey => $headerValue)
  113. $finalHeader .= $enclosure.$headerValue.$enclosure.$delimiter; //Create the line
  114. $finalHeader = substr_replace($finalHeader, "\n", -1); //Replace the last delimiter by a new line
  115. fputs($fileW,$finalHeader); //Write to the file
  116.  
  117. //Write the table lines
  118. foreach($logLines as $logKey => $logValue){
  119. $finalLine = "";
  120. foreach($logHeader as $headerKey => $headerValue){
  121. $fieldValue = array_key_exists($headerKey, $logValue) ? $logValue[$headerKey] : ''; //Place the content under the right header
  122. $finalLine .= $enclosure.$fieldValue.$enclosure.$delimiter; //Create the line
  123. }
  124. $finalLine = substr_replace($finalLine, "\n", -1); //Replace the last delimiter by a new line
  125. fputs($fileW, $finalLine); //Write to the file
  126. }
  127.  
  128. if(fclose($fileW)){ //Close the temporary log file
  129. if(file_exists($fileName)) unlink($fileName); //Delete the old log file since we already have the TMP file
  130. if(!file_exists($fileName) && rename($fileName.".tmp",$fileName)) //Rename the temporary log file to the original name
  131. $responseMessage="TRUE"; //If everything went well return TRUE
  132. }
  133. }
  134. //Output the HTTP response message
  135. header("Content-Type: text/plain"); //Text output for compatibility with devices that don't process HTML
  136. echo $responseMessage;