I need to check which plant are online so I'm thinking to compare last date time update to the current time if difference is less than 15 min the plant is online otherwise is offline
I have write this in php to make a query but all row change to online or offline what I'm doing wrong?
it seams that the update is done on all row of the database not only in the selected by the first query
$sql = "select * from PlantData where lastUpdate > DATE_SUB(NOW(), INTERVAL 15 MINUTE)";
$resultt = mysqli_query($connection, $sql);
//echo($resultt);
while ($roww = $resultt->fetch_array()) {
$rowws[] = $roww;
}
foreach($rowws as &$roww) {
$sqlupdate = "update PlantData set statusConnection = 'online'";
mysqli_query($connection, $sqlupdate);
}
$sql2 = "select * from PlantData where lastUpdate < DATE_SUB(NOW(), INTERVAL 15 MINUTE)";
$resultt2 = mysqli_query($connection, $sql2);
//echo($resultt);
while ($roww2 = $resultt2->fetch_array()) {
$rowws2[] = $roww2;
}
foreach($rowws2 as &$roww2) {
$sqlupdate2 = "update PlantData set statusConnection = 'offline'";
mysqli_query($connection, $sqlupdate2);
}
thanks for support
My Answer:
"SELECT * FROM plants";
$result = $conn->query($sql);
$current_time = date('Y-m-d H:i:s');
while($row = $result->fetch_assoc()) {
$last_update = $row['last_update'];
$diff = strtotime($current_time) - strtotime($last_update);
if($diff < 900) { // 15 minutes in seconds
$status = "online";
} else {
$status = "offline";
}
$update_sql = "UPDATE plants SET status='$status' WHERE id=" . $row['id'];
$conn->query($update_sql);
}
$conn->close();
Rate this post
3 of 5 based on 8365 votesComments