kylejohnson

MariaDB [house]> select * from statistics where statisticName != 'Voltage' order by statisticDate desc limit 20;
+-------------+---------------+----------------+---------------+
| statisticId | statisticName | statisticValue | statisticDate |
+-------------+---------------+----------------+---------------+
|      547354 | Kitchen       |          79.00 |    1345365307 |
|      547353 | Computer Room |          77.00 |    1345365305 |
|      547352 | Workshop      |          80.00 |    1345365303 |
|      547287 | Kitchen       |          79.00 |    1345364987 |
|      547286 | Computer Room |          77.00 |    1345364985 |
|      547285 | Workshop      |          81.00 |    1345364983 |

kylejohnson

+-------------+---------------+----------------+---------------+
| statisticDate | Workshop    | Computer Room  | Kitchen       |
+-------------+---------------+----------------+---------------+
| 1345365307   |    79.00     |    79.00       |    80.00      |
| 1345365367   |    80.00     |    79.00       |    81.00      |

kylejohnson

// Find all of the sensors
$query_sensors = 'select distinct statisticName from statistics where statisticName != "Voltage"';
$result_sensors = mysql_query($query_sensors) or die ('query failed: ' . mysql_error());
while ($row = mysql_fetch_array($result_sensors)) {
 $sensors[] = $row[0];
}

// For each sensor, query the values, put them into an individual array
foreach ($sensors as $sensor) {
 $$sensor = "select statisticName, statisticValue from statistics where statisticName = '$sensor' and date(from_unixtime(statisticDate)) = curdate() order by statisticDate";

kylejohnson

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| StatID    | int(11)     | NO   | PRI | NULL    | auto_increment |
| SensorID  | tinyint(4)  | NO   | MUL | NULL    |                |
| StatDate  | varchar(20) | YES  |     | NULL    |                |
| StatValue | varchar(5)  | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

kylejohnson

kjohnson@vps:~$ mysql -u house -p house -e 'select SensorName, StatDate, StatValue as Temperature from stats left join sensors on stats.SensorID=sensors.SensorID limit 10'
Enter password:
+---------------+------------+-------------+
| SensorName    | StatDate   | Temperature |
+---------------+------------+-------------+
| Workshop      | 1353440709 | 64.62       |
| Computer Room | 1353440710 | 75.65       |
| Workshop      | 1353440929 | 71.15       |
| Computer Room | 1353440931 | 64.51       |
| Kitchen       | 1353440932 | 75.54       |

kylejohnson

Date     |  Kitchen   |   Computer Room    |    Workshop
1:30     |   60       |       61           |     62
2:00     |   60       |       61           |     62
2:30     |   60       |       61           |     62
3:00     |   60       |       61           |     62
3:30     |   60       |       61           |     62

kylejohnson

MariaDB [house]> select time_format(from_unixtime(statDate), '%H:%i') as statDate, avg(statValue) as statValue, SensorID as Room from stats where date(from_unixtime(statDate)) = '2012-11-24' group by time_format(from_unixtime(statDate), '%H:%i'), SensorID limit 10; 
+----------+-----------+------+
| statDate | statValue | Room |
+----------+-----------+------+
| 00:00    |     72.16 |    1 |
| 00:00    |    64.005 |    2 |
| 00:00    |     75.09 |    3 |
| 00:01    |     72.16 |    1 |
| 00:01    |     63.95 |    2 |
| 00:01    |    75.145 |    3 |

kylejohnson

select  time_format(from_unixtime(statDate), '%H') as Date , avg( case when SensorID = 1 then StatValue ELSE 0 END) as 'Room 1' , avg( case when SensorID = 2 then StatValue ELSE 0 END) as 'Room 2' , avg( case when SensorID = 3 then StatValue ELSE 0 END) as 'Room 3' from stats where date(from_unixtime(statDate)) = curdate() group by time_format(from_unixtime(statDate), '%H') order by StatDate ASC;

kylejohnson

<?php

 $dbhost="localhost";
    $dblogin="house";
    $dbpwd='password';
    $dbname="house";
    $db =  mysql_connect($dbhost,$dblogin,$dbpwd);
    mysql_select_db($dbname);

kylejohnson

kjohnson@zoneminder:~/ZoneMinder/web$ git branch
  develop
  master
* modern
kjohnson@zoneminder:~/ZoneMinder/web$ git status
# On branch modern
nothing to commit (working directory clean)
kjohnson@zoneminder:~/ZoneMinder/web$ ls
app  index.php  lib  plugins  README.md  vendors
kjohnson@zoneminder:~/ZoneMinder/web$ git push origin modern