[Resolved] Acces to Domoticz.db in PHP

[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.

[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?

[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.

[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

[Egregius]

Just give 777 rights on the folder and the database files

[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

[jmv75013]

Hi Egregius,

Thank very very much. It works. I was so simple...
Thank you for the link to your github.

[Egregius]

You're welcome, glad I could help