John Davidson

php - query mysql date time with compare from now time and update a field on mysql

0 comments
Message:


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

5 of 5 based on 6536 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us