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

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

CodePudding user response：

Run the query in only once

UPDATE PlantData 
SET 
    statusConnection = CASE
        WHEN lastUpdate >= NOW() - INTERVAL 15 MINUTE THEN 'online'
        WHEN lastUpdate < NOW() - INTERVAL 15 MINUTE THEN 'offline'
    END;