Page 1 of 1
[Resolved] Acces to Domoticz.db in PHP
Posted: Tuesday 04 September 2018 8:35
by jmv75013
Hello,
I want to access the domoticz database for consultation with a php page and I have some problems ...
Domoticz turns on a raspberry. I have on this same raspberry lighttp, php and sqlite3. My script is
Code: Select all
<?php
class MyDB extends SQLite3 {
function __construct() {
$chemin="/home/pi/domoticz/";
$dbname='domoticz.db';
$this->open($chemin.$dbname);
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully<br/>";
echo $db->lastErrorMsg(),"<br/>";
}
// Lecture des dernières températures et températures min et max pour toutes les sondes
$sql = 'SELECT ID, Name, sValue, LastUpdate FROM DeviceStatus WHERE Type = 80 ORDER BY ID ASC';
$results = $db->query($sql) ;
echo $db->lastErrorMsg(),"<br/>";
var_dump($results);
while ($row = $results->fetchArray(SQLITE3_NUM)) {
.... suite du code ..
}
The statement "$db =new (MyDB)" seems to be running correctly, but after that does not work.
Thanks for help.
Re: Acces to Domoticz.db in PHP
Posted: Tuesday 04 September 2018 17:10
by Egregius
I use this to clean stuff I'm not interested in, you probably could easily adapt the code for fetching data:
Code: Select all
<?php
$db = new SQLite3('/domoticz/domoticz.db');
$clean = strftime("%G-%m-%d %k:%M:%S",time()-3600*24);
$tables = array( 'LightingLog',
'MultiMeter',
'MultiMeter_Calendar',
'Meter',
'Meter_Calendar',
'Percentage',
'Percentage_Calendar',
'Rain',
'Rain_Calendar',
'Temperature',
'Temperature_Calendar',
'UV',
'UV_Calendar',
'Wind',
'Wind_Calendar');
echo PHP_EOL;
foreach($tables as $table)
{
$query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)");
if ($query)
{
$rows = $db->changes();
if($rows>0)
echo $rows." rows removed from $table".PHP_EOL;
}
sleep(2);
}
$query=$db->exec("DELETE FROM LightingLog WHERE Date < '$clean'");
if ($query)
{
$rows = $db->changes();
if($rows>0)
echo $rows." old rows removed from LightingLog".PHP_EOL;
}
sleep(2);
$query=$db->exec("DELETE FROM LightingLog WHERE DeviceRowID in (6,9,10,170,171,172,173,174,175,176,177,295,296,297,298,299,300,301,302)");
if ($query)
{
$rows = $db->changes();
if($rows>0)
echo $rows." rows removed from LightingLog".PHP_EOL;
}
On the other hand, most of the data is also available with in the json api. What do you want to show on the page?
Re: Acces to Domoticz.db in PHP
Posted: Wednesday 05 September 2018 8:39
by jmv75013
Hi Egreguis,
Thank you for your help, I will see with what you use if it works.
In fact, I have a website on which I want to show the temperature and electrical consumption curves.
Re: Acces to Domoticz.db in PHP
Posted: Wednesday 05 September 2018 10:24
by jmv75013
I have tried to adapt your PHP code like this.
Code: Select all
<?php
$db = new SQLite3('/home/pi/domoticz/domoticz.db');
var_dump($db);
var_dump($db->lastErrorMsg());
$sql = "SELECT ID, Name, sValue, LastUpdate FROM DeviceStatus WHERE";
$sql .= " Type = 80 ORDER BY ID ASC";
$statement = $db->prepare($sql);
var_dump($statement);
var_dump($db->lastErrorMsg());
$results = $statement->execute();
var_dump($results);
while($row = $results->fetchArray()) {
var_dump($row[1]);
}
?>
and the result are for var_dump
Code: Select all
object(SQLite3)# {}
string(12) "not an error"
bool(false)
string(28) "unable to open database file"
I think it is a permission problem, but i don't know how resolve it.
The user for lighttpd is www-data. I have added this user to group
pi and group
users because permissions for domoticz.db are :
Code: Select all
-rwxr-xr-x 1 pi users ... domoticz.db
but that doesn't work
Re: Acces to Domoticz.db in PHP
Posted: Wednesday 05 September 2018 17:43
by Egregius
Just give 777 rights on the folder and the database files
Re: Acces to Domoticz.db in PHP
Posted: Thursday 06 September 2018 6:54
by Egregius
I just remembered that I once used the domoticz database to show a graph on a php page, now I inject the values in a MySQL database.
Anyway, here's an old version of my temp.php page:
https://github.com/Egregius/PHP-Custom- ... 4cec08c9c0
Re: Acces to Domoticz.db in PHP
Posted: Thursday 06 September 2018 8:53
by jmv75013
Hi Egregius,
Thank very very much. It works. I was so simple...
Thank you for the link to your github.
Re: [Resolved] Acces to Domoticz.db in PHP
Posted: Thursday 06 September 2018 12:20
by Egregius
You're welcome, glad I could help
