egregius.be

Urban Exploration, PHP and others…

domoticzphp450

Store temperature data from Domoticz in mySQL for historical analyses

This PHP script will retrieve temperature data from Domoticz and store it in a MySQL database. This way you can keep detailed information for more than 7 days, which is the limit for the short log in Domoticz.

Detailed temperatures are stored in the table temp_day. Daily minimal, average and maximum temperatures in temp_month.
The script automatically adds columns for each thermometer you put in the devices array. Column names are set to the name of the device in Domoticz. This means that if you ever replace a thermometer you only have to give it the same name and change the idx in the script.

All you need to do is create a database and adjust the items for each define line, the script does all the rest.

<?php
define('sqlserver','127.0.0.1');
define('sqluser','domotica');
define('sqlpassword','domotica');
define('sqldatabase','domotica');
define('updatedatabase',true);//Set to false once the database is created, set to true to add devices
define('domoticzip','127.0.0.1');
define('domoticzport','8080');
define('numberofhours',1);//Defines how many hours of detailed data is updated in _day table. Set to a high number for initial run.
define('numberofdays',1);//Defindes how many days of min,avg and max is updated in _month table. Set to a high number for initial run.
define('devices',array(231,236,238,573,588,1260,1475));//IDXs of temperature devices
define('sleep',10000);//Defines how many microseconds of sleep there should be between queries, can be usefull on low power devices
if (updatedatabase) {
createupdatedatabase();
}
$db=new mysqli(sqlserver,sqluser,sqlpassword,sqldatabase);
if ($db->connect_errno>0) {
die('Unable to connect to database ['.$db->connect_error.']');
}
foreach (devices as $idx) {
$device=json_decode(
file_get_contents(
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx),
true
);
if (!empty($device['result'][0]['Name'])) {
$name=$device['result'][0]['Name'];
$day=json_decode(
file_get_contents(
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=graph&sensor=temp&idx='.$idx.'&range=day'),
true
);
if (!empty($day['result'])) {
foreach ($day['result'] as $i) {
$stamp=$i['d'];
if(strtotime($stamp)>time()-(3600*numberofhours)){
$temp=$i['te'];
$query="INSERT INTO `temp_day` (`stamp`,`$name`)
VALUES ('$stamp','$temp')
ON DUPLICATE KEY UPDATE `$name`='$temp'";
echo $query.'<br>';
if (!$result=$db->query($query)) {
echo('There was an error running the query "'.$query.'" - '.$db->error);
}
usleep(sleep);
}
}
}
usleep(sleep);
$month=json_decode(
file_get_contents(
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=graph&sensor=temp&idx='.$idx.'&range=month'),
true
);
if (!empty($month['result'])) {
foreach ($month['result'] as $i) {
$stamp=$i['d'];
if (strtotime($stamp)>time()-(86400*numberofdays)) {
$min=$i['tm'];
$avg=$i['ta'];
$max=$i['te'];
$cmin=$name.'_min';
$cavg=$name.'_avg';
$cmax=$name.'_max';
$query="INSERT INTO `temp_month` (`stamp`,`$cmin`,`$cavg`,`$cmax`)
VALUES ('$stamp','$min','$avg','$max')
ON DUPLICATE KEY UPDATE `$cmin`='$min',`$cavg`='$avg',`$cmax`='$max'";
echo $query.'<br>';
if (!$result=$db->query($query)) {
echo('There was an error running the query "'.$query.'" - '.$db->error);
}
usleep(sleep);
}
}
}
usleep(sleep);
}
}
function createupdatedatabase()
{
$db=new mysqli(sqlserver,sqluser,sqlpassword,sqldatabase);
if ($db->connect_errno>0){
die('Unable to connect to database ['.$db->connect_error.']');
}
$sqldatabase=sqldatabase;
$result=mysqli_fetch_assoc(
mysqli_query(
$db,
"SELECT count(*) as count
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = '$sqldatabase')
AND (TABLE_NAME = 'temp_day')"
)
);
if ($result['count']==0) {
$query="CREATE TABLE temp_day (
stamp datetime PRIMARY KEY
)";
if (!$result=$db->query($query)) {
die('There was an error running the query ['.$query.' - '.$db->error.']');
}
}
foreach (devices as $idx) {
$device=json_decode(
file_get_contents(
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx),
true
);
if (!empty($device['result'][0]['Name'])) {
$name=$device['result'][0]['Name'];
$result=mysqli_fetch_assoc(
mysqli_query(
$db,
"SELECT COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'temp_day'
AND COLUMN_NAME = '$name';"
)
);
if ($result['COLUMN_TYPE']!='float(3,1)') {
$query="ALTER TABLE `temp_day` ADD `$name` FLOAT(3,1) NULL;";
if (!$result=$db->query($query)) {
die('There was an error running the query ['.$query.' - '.$db->error.']');
}
}
}
}
$result=mysqli_fetch_assoc(
mysqli_query(
$db,
"SELECT count(*) as count
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = '$sqldatabase')
AND (TABLE_NAME = 'temp_month')"
)
);
if ($result['count']==0) {
$query="CREATE TABLE temp_month (
stamp date PRIMARY KEY
)";
if (!$result=$db->query($query)) {
die('There was an error running the query ['.$query.' - '.$db->error.']');
}
}
foreach (devices as $idx) {
$device=json_decode(
file_get_contents(
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx),
true
);
if (!empty($device['result'][0]['Name'])) {
$name=$device['result'][0]['Name'];
$tables=array($name.'_min',$name.'_avg',$name.'_max');
foreach ($tables as $table) {
$result=mysqli_fetch_assoc(
mysqli_query(
$db,
"SELECT COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'temp_month'
AND COLUMN_NAME = '$table';"
)
);
if ($result['COLUMN_TYPE']!='float(3,1)') {
$query="ALTER TABLE `temp_month` ADD `$table` FLOAT(3,1) NULL;";
if (!$result=$db->query($query)) {
die('There was an error running the query ['.$query.' - '.$db->error.']');
}
}
}
}
}
}
?>
view raw storetemps.php hosted with ❤ by GitHub

2 reacties

  1. Hello,
    I’ve tried this code.
    I’m running php on a synology, so i have to access the database of the raspberry pi where the data is stored.
    Now i use a username and pasword.
    let me know how i can access the database on the pi with a username ans password to load it into the sql on the synology

    • Hi Stef,

      I think that should be something like http://username:password@‘.domoticzip.’:’.domoticzport.’/json.htm?type=devices&rid=’.$idx on line 23.
      Or add your Synology’s IP address to the Local Networks (no username/password) of the Domoticz settings page to bypass authentication.

Geef een reactie

Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.