Add new comment

PHP - Internet of Things Web Logger

By Daniel
Fri, 06/30/2017 - Updated 2 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

<?php
/* =============================================================
/   * Internet of Things IoT CSV Logger
	* Version 1.2 (04/07/2017)
	*
	* This application logs any HTTP GET requests in a CSV file that can be directly opened by Excel.
	* The GET variable names will be used as column headers and the values will sorted in the rows.
	* 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.
	* In case of success return TRUE, in case of errors return FALSE.
	* 
	* Developed by Daniel Brooke Peig - daniel@danbp.org
	* http://www.danbp.org
	* Copyright 2017 - Daniel Brooke Peig
	*
	* This software is distributed under the MIT License.
	* 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:
	*
	* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
	*
	* 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.
	*
	*
/* =============================================================*/
 
//------------------------------------------------
//Configurations
//
$fileName = "iotcsvlog.csv";
date_default_timezone_set('America/Sao_Paulo');
$delimiter = ";";
$enclosure = '"';
$maxLogSize = 10000; //Most Excel versions can read only about 16000 lines
$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.
//------------------------------------------------
 
//Variable initialization
$logLines = array();
$inputLine = array();
$responseMessage = "FALSE";
 
if($_GET["LogClear"] === "TRUE"){
	//If this is set, the source file will not be processed and will be erased after the new log entry is created
}
else if(file_exists($fileName)){ // File exists
 
	// Reads lines of file to array
	$fileLines = file($fileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
 
	//Not Empty file
	if($fileLines !== array()){
 
		//Extract the existing header from the file
		$lineHeader = array_shift($fileLines);
		$logOriginalHeader = array_map('trim', str_getcsv(substr($lineHeader,3), $delimiter, $enclosure));
 
		//Process the file only if the system could find a valid header
		if(count($logOriginalHeader) > 0) {
			//Define the new header
			$logHeader = array_combine($logOriginalHeader, $logOriginalHeader);
 
			//Get each line of the array
			$linesToSkip = (count($fileLines)+1>$maxLogSize) ? (count($fileLines)-$maxLogSize+1) : 0; //Trim the log size
			foreach ($fileLines as $line) {
				if(trim($line) !== ''){ //Remove blank lines
					if($linesToSkip <= 0) {
						$arrayFields = array_map('trim', str_getcsv($line, $delimiter, $enclosure)); //Convert line to array
						$logLines[] = array_combine($logOriginalHeader, $arrayFields); //Uses the existing headers as keys for the log lines
					}
					else $linesToSkip--;
				}
			}
		}
	}
}
//Set the mandatory log headers (if they were not there during the file load)
$logHeader["Date and Time"] = "Date and Time";
$logHeader["IP"] = "IP";
 
//Now process the inputs
foreach($_GET as $key => $value){
	//Performs some cleaning and adjustments
	if($key == "Date and Time") $key = "Date and Time_user"; //Prevents overwriting internal data
	if($key == "IP") $key = "IP_user"; //Prevents overwriting internal data
	$key = preg_replace('/[^\w\-\s\.]/', '', trim($key)); //Allow only alphanumeric characters, underscore and hyphens in the header
	$value = str_replace($enclosure, '\'', trim($value)); //Escape the delimiter and enclosure
 
	if(($key != "LogPass" || $password ==='') && $key != "LogClear"){ //Ignore the password parameter if the password is set
		$logHeader[$key] = $key; //Adds the new header cell if needed
		$inputLine[$key] = $value; //Defines the input array
	}
}
 
//Set defaults to the system internal fields
$outputLine["Date and Time"] = date("Y-m-d H:i:s O"); //Set the timestamp
$outputLine["IP"] = $_SERVER['REMOTE_ADDR']; //
$outputLine = (count($inputLine)>0) ? array_merge($outputLine, $inputLine) : $outputLine;
 
//Put the new output into the log array
array_push($logLines, $outputLine);
 
//Open an output temporary file, if it does not exist create it
$fileW = fopen($fileName.".tmp","w+");
 
//If the system was able to open the file
if($_GET["LogPass"] === $password || $password === '')
if($fileW !== false){
 
	//File headers
	fputs($fileW, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) )); //BOM for Excel UTF-8
 
	//Write the table header
	foreach($logHeader as $headerKey => $headerValue)
		$finalHeader .= $enclosure.$headerValue.$enclosure.$delimiter; //Create the line
	$finalHeader = substr_replace($finalHeader, "\n", -1); //Replace the last delimiter by a new line
	fputs($fileW,$finalHeader); //Write to the file
 
	//Write the table lines
	foreach($logLines as $logKey => $logValue){
		$finalLine = "";
		foreach($logHeader as $headerKey => $headerValue){
			$fieldValue = array_key_exists($headerKey, $logValue) ? $logValue[$headerKey] : ''; //Place the content under the right header
			$finalLine .= $enclosure.$fieldValue.$enclosure.$delimiter; //Create the line
		}
		$finalLine = substr_replace($finalLine, "\n", -1); //Replace the last delimiter by a new line
		fputs($fileW, $finalLine); //Write to the file
	}
 
	if(fclose($fileW)){ //Close the temporary log file
		if(file_exists($fileName)) unlink($fileName); //Delete the old log file since we already have the TMP file
		if(!file_exists($fileName) && rename($fileName.".tmp",$fileName))	//Rename the temporary log file to the original name
			$responseMessage="TRUE"; //If everything went well return TRUE
	}
}
//Output the HTTP response message
header("Content-Type: text/plain"); //Text output for compatibility with devices that don't process HTML
echo $responseMessage;
The content of this field is kept private and will not be shown publicly.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.