I'm working on a data monitor for solar panels. Now i'm working on limited space, and getting a lot of data which is being logged on the database. To downsize my space i was trying to find a way to make the datatables count everything every 15 minutes, and store it into a new table, and deleting the old tables.
I tried to do this with a cronjob, and later on tried to make a php script which would be handling it.
Now that code did not come close to what i was planning to have, and i'm stuck at this problem, and i know that there are probably people who do know the answer to this question. I came across similar problems with searching through the site, but did not come across a "Count and Delete" question. This to limit the space it uses.
Simply said, i'm trying to find a way with php to make it count and store the data records from "inverters" to "inverters_day", and deleting the excisting records from "inverters".
The Datatables are as following:
| timestamp | timestamp | No | CURRENT_TIMESTAMP
| inverter | int(11) | No |
| wh | int(11) | No |
| dcp | int(11) | No |
| dcc | float | No |
| efficiency | float | No |
| acf | int(11) | No |
| acv | float | No |
| temp | float | No |
| status | int(11) | No |
Example of data:
|2016-01-08 08:34:24|110134878|889901|0|0.05|0|49|55|2|1
|2016-01-08 08:34:59|110134878|889901|0|0.05|0|49|55|2|1
|2016-01-08 08:35:23|110048316|643076|0|0.05|0|49|55|1|1
Inverter_day is a duplication of the one above, structure it the same.
sorry, i forgot to add the code i tried.
if ($sql = mysqli_query ($conn, "SELECT COUNT (*) FROM logs WHERE timestamp < NOW() - INTERVAL 15 MINUTES")) {
$row_slt = mysqli_num_rows($sql);
if ($row_slt > 0 ) {
$sql = mysqli_query ($conn, "INSERT INTO inverter_day (timestamp, inverter, wh, dcp, dcc, efficiency, acf, acv, temp, status) SELECT timestamp, inverter, wh, dcp, dcc, efficiency, acf, acv, temp, status FROM logs WHERE timestamp NOT IN (select timestamp from inverter_day)");
} else if ($row_slt == 0) {
echo "<br> The Tables are up to date <br>";
} else {
echo "<br> Oops something went wrong. Please try again";
}
}
As i haven't had any further help with this, i just went on and left this part behind, untill i came across the INSERT SELECT feature.
The solution to the problem was the use of:
$query = "INSERT INTO enecsys_day (id, wh, dcpower, dccurrent, efficiency, acfreq, acvolt, temp, state) SELECT id, SUM(wh), SUM(dcpower), SUM(dccurrent), SUM(efficiency), SUM(acfreq), SUM(acvolt), SUM(temp), SUM(state) FROM enecsys GROUP BY id HAVING COUNT(id) > 1";
The full code i used:
<?php
mysql_connect($servername,$username,$password);
mysql_select_db($database);
$query = "INSERT INTO enecsys_day (id, wh, dcpower, dccurrent, efficiency, acfreq, acvolt, temp, state) SELECT id, SUM(wh), SUM(dcpower), SUM(dccurrent), SUM(efficiency), SUM(acfreq), SUM(acvolt), SUM(temp), SUM(state) FROM enecsys GROUP BY id HAVING COUNT(id) > 1";
$resultaat = mysql_query($query);
while ($row = mysql_fetch_array($resultaat))
{
?>
<tr>
<td><?php $row["id"]; ?></td>
<td><?php $row["SUM(wh)"]; ?></td>
</tr>
<br />
<?php
}
$delete = "DELETE FROM enecsys";
$dresultaat = mysql_query($delete);
?>