Storing temperature data in postgresql

An arduino board is running as a web server serving temperatures read out from some DS18B20 sensors. The data are stored in a postgresql database. The data table has been defined as:

create table temps(
   id serial primary key,
   termid integer,
   temp double presicion not null,
   sensoraddr character varying(20),
   datetime  timestamp with time zone default now()
);

A php-script is set upp to poll the arduino and fetch data. It sends data as json, described here.

<?php
$server="192.168.0.177"; // Must be set up to match the arduino's IP.
$url="${server}/json";
$database='wdb';
$pgserver='localhost';   // postgres server
$username='.....'; // postgres user name
$password='.....'; // postgres password
$dbtype='pgsql';
try{
    $connectstring=$dbtype.':host='.$pgserver.';dbname='.$database;
    $dbh = new PDO($connectstring, $username, $password);
    if($dbtype=='pgsql'){
      $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

  }
  catch(PDOException $e){
    $message=$e->getMessage(); 
    exit( "

Cannot connect - $message
");
  }

$fp = fsockopen($server, 80, $errno, $errstr, 30);
$data='';
if (!$fp) {
    echo "$errstr ($errno)
\n";
} else {
    $out = "GET /json HTTP/1.1\r\n";
    $out .= "Host: $server\r\n";
    $out .= "Connection: Close\r\n\r\n";
    fwrite($fp, $out);
    while (!feof($fp)) {
        $data .= fgets($fp, 128);
    }
    fclose($fp);
}
$data=split("\n",$data); // cuts of the headers
$jsondata=json_decode($data[3]);
$sql="insert into temps(temp,sensoraddr)values(?,?)";
$sh=$dbh->prepare($sql);
for($i=0;$itemp);$i++){
        $sh->execute(array($jsondata->temp[$i],$jsondata->address[$i]));
}
?>

This can be trigged from some event, or it may be run periodically. To make it run once every 15 minutes, save the php script as ‘fetchtemp.php’ and make the following shell-script in the same directory:

#!/bin/bash
while true; do
php fetchtemp.php
sleep 900
done

(or call it from cron or any other scheduler)

I have also a system for presenting the data as a svg-graph.

This entry was posted in arduino, php, sql. Bookmark the permalink.