推迟HTTP GET响应，直到用户更改数据库中的值

Question

I have a NodeMCU (ESP8266) board that sends HTTP GET requests to a shared hosting database. To avoid port forwarding on my router I came up with a solution where my NodeMCU board periodically sends (every 5 seconds) a HTTP GET request to the database to see if the device status is changed by the user.

void loop() 
{
   HTTPClient http; //Declare object of class HTTPClient
   String getData, Link;

   //HTTP GET REQUEST

   getData = "?device="+ deviceName ;
   Link = "http://.../getStatus.php" + getData;

   http.begin(Link); 

   int httpCode = http.GET(); //Send the request

   String payload = http.getString(); //Get the response from database

   if(payload=="ON")
   {
      digitalWrite(LEDPin, HIGH);//change pin status
   }
   else if(payload=="OFF")
   {
      digitalWrite(LEDPin, LOW);//change pin status
   }

   http.end(); 

   delay(5000);//Send GET request every 5 seconds
}  

The user changes the device status over a website (with a button click) and the device status is then saved in the database. When the device sends the HTTP GET request to the server the getStatus.php file queries the database for the current device status and sends the response back to the device.

<?php

    $db = mysqli_connect(...);

    if(!empty($_GET['device']))
    {
        $device_name = $_GET['device'];

        $query_status = "SELECT status FROM devices WHERE device = ?";

        $stmt = mysqli_prepare($db, $query_status);
        if ($stmt === false) {
            error_log(mysqli_error($db));
            die("Sorry, there has been a software error");
        }
        $ok = mysqli_stmt_bind_param($stmt, "s", $device_name);
        if ($ok === false) {
            error_log(mysqli_stmt_error($db));
            die("Sorry, there has been a software error");
        }
        $ok = mysqli_stmt_execute($stmt);
        if ($ok === false) {
            error_log(mysqli_stmt_error($db));
            die("Sorry, there has been a software error");
        }

        $result = $stmt->get_result();

        if ($result->num_rows > 0) 
        {
            while($row = $result->fetch_assoc()) 
            {
                echo $row["status"];
            }
        } 
    }
?>

I am trying to find a solution where I don't have to query the database every 5 second, but instead only when the user changes the device status.

So my question is how postpone the HTTP GET response until the moment that the user changes the device status with a button click?

Answer 1

You need a live connection between your device and server. Socket is a good solution i think. you can see this article :

https://blog.tommyku.com/blog/communication-between-cpp-and-php-program-using-socket/

to get help.

But personally i always prefer easy and fast ways with less change in my codes.

So :

instead get involved with complex socket programming you can use in-memory data structure store like https://redis.io/ which response very faster than Mysql without any serious side-effect in your server (Php script) and then you don't need to change NodeMCU(ESP8266) codes but Php code to use Redis.