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

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?

douhuan1497
douhuan1497 请问如何打开websocket服务器端:)。你看过socket.io了吗?
9 个月之前 回复
doumi6685
doumi6685 感谢小费,但我已经尝试过,我很难在互联网上找到一个有效的例子。我知道如何在NodeMCU板上编写websocket客户端代码,但是我很难在共享主机服务器上编写websocket服务器,如果可能的话。我也有一些替代品,如MQTT,但我不能在共享主机网站上使用它们。我试图找到一个低成本的解决方案(大约7?,/月),对于MQTT我必须使用VPS服务器(大约25?,/月)
9 个月之前 回复
dsutuyxe088689
dsutuyxe088689 在设备和服务器之间打开websocket。更新设备状态后,使用打开的websocket向设备发送消息。
9 个月之前 回复

3个回答

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.

展开翻译

译文



您需要在设备和服务器之间建立实时连接。
Socket是我认为的一个很好的解决方案。
你可以看到这篇文章:< / p>

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

获取帮助。</ p>

但我个人总是喜欢简单快捷的方法,而且我的代码变化较少。</ p>

所以:</ p>

参与复杂的套接字编程
你可以使用内存数据结构存储,如 https://redis.io/ 哪个响应比Mysql快得多,没有任何严重的副作用在您的服务器(Php脚本),然后您不需要更改
NodeMCU(ESP8266)代码,但Php代码使用Redis。 </ p>
</ div>

"It's not a bug, it's a feature."

PHP was designed specifically to avoid these kind of notifications, and with good reason: They're an extremely vulnerable attack vector. If you're doing this in a production environment, I'd strongly suggest against this particular data-flow. In essence, what you're needing to do is turn the client into a server that accepts a connection from another server... a client server that doesn't have the reliable layers of security that your server (probably) has.

Now, that is annoying for those of us who actually want to do it. However, there is a recommended work-around without sacrificing your security. PHP is pretty much, hands down, the most secure language for the web. And it's not giving up that anytime soon. And this kind of situation where the standard solution is "use another language."

What you do is set up what's known as a push notification server. It's whole goal is a secure receiver go-between for less secure data transmissions. You have your database server communicate with the push server (which is likely nodejs or similar with a simple RESTapi for your database server). And you have a javascript your client uses that listens ONLY for a hash from that push server that corresponds with a hash supplied for their session for notifications. Then, when your push server gets pinged, it sends the information-less hex hash (which is purged of any non-hex information for security) at which point your front-end client knows "Hey, I should poll the server for additional information." At which point it does, and then you can let the client know what has updated.

展开翻译

译文



“这不是一个错误,它是一个功能。”</ p>

PHP专门设计用于避免 这些通知,并有充分的理由:它们是一个极其脆弱的攻击媒介。 如果你在生产环境中这样做,我强烈建议不要使用这个特定的数据流。 从本质上讲,您需要做的是将客户端变成一个服务器,该服务器接受来自另一个服务器的连接......一个客户端服务器,它没有服务器(可能)具有的可靠安全层。 </ p>

现在,对于我们这些真正想要这样做的人来说,这很烦人。 但是,建议的解决方法是在不牺牲安全性的情况下进行。 PHP非常适用于Web,它是最安全的语言。 它不会很快放弃它。 在这种情况下,标准解决方案是“使用其他语言”。</ p>

您所做的是设置所谓的推送通知服务器。 它的全部目标是为安全性较低的数据传输提供安全的接收器。 您的数据库服务器与推送服务器(可能是nodejs或类似的数据库服务器的简单RESTapi)进行通信。 并且您有一个客户端使用的javascript,它仅侦听来自该推送服务器的散列​​,该散列与为其通知会话提供的散列相对应。 然后,当您的推送服务器被ping时,它会发送无信息的十六进制哈希(为了安全性而清除任何非十六进制信息),此时您的前端客户端会知道“嘿,我应该轮询服务器以获取更多信息 “。 它确实在那一点上,然后你可以让客户知道更新了什么。</ p>
</ div>

I'd like to suggest that your current solution is fine.

You have a device that needs to know a value that's store in a remote database. As far as the device is concerned, the value could change at any time, and the device needs to update its own state when the remote value changes.

Having it act as a Host that receives connections or notifications of any kind from the remote server would probably be problematic. You mentioned that you'd have to set up port-forwarding. You'd also be adding the security concerns of a Host server to this device, and you'd need to worry about maintaining a consistent IP address (or something).

The ideal solution will depend on what you're trying to accomplish. Why don't you want to poll the server every five seconds?

Of course the actual question you asked was

how [to] postpone the HTTP GET response until the moment that the user changes the device status with a button click?

Postponing the response is hardly a normal thing to do, and you're going to run into all kinds of problems with timeouts, which I'm not going to address here (for brevity), and it'll be a struggle to get your system to do anything else at the same time, but it can be done:

client device:

static DateTime lastUpdated = now();//or whatever

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

  //HTTP GET REQUEST
  getData = "?device=" + deviceName + "&lastUpdated=" + lastUpdated.asEpochInt(); //or whatever
  Link = "http://.../getStatus.php" + getData;

  try
  { 
    http.begin(Link); 
    int httpCode = http.GET(); //Send the request
    payload = http.getString(); //Get the response from database
    succeed = true;
  }
  catch(TimeoutError te) //or whatever
  {
    succeeded = false;
  }

  if(succeeded)
  {
    lastUpdated = now()

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

  http.end(); 

  //delay(5000); //send the next one right away.
}  

host server

<?php

$db = mysqli_connect(...);

if(!empty($_GET['device']))
{
  $device_name = $_GET['device'];
  $threshold = DateTimeImmutable::createFromFormat('U', $_GET['lastUpdated'])

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

  $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)
    && mysqli_stmt_bind_param($stmt, "s", $threshold->format('Y-m-d H:i:s'));
  if ($ok === false) {
    error_log(mysqli_stmt_error($db));
    die("Sorry, there has been a software error");
  }

  $waiting = TRUE;
  while($waiting)
  {
    $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) 
    {
      $waiting = FALSE;
      while($row = $result->fetch_assoc()) 
      {
        echo $row["status"];
      }
    }
    else {
      usleep(100);
    }
  }
}//or else what?
?>

And we could take this even farther (make it even worse) by pushing our while loop down into the MySQL, but that would require learning the details of MySQL Stored Procedures, and no amount of internet points is worth that.

Just because you can, doesn't mean you should.

douye2572
douye2572 我喜欢你的电子邮件创意,我必须看看它是否可行
9 个月之前 回复
dqq3623
dqq3623 抱歉,我没有使用websockets我只是向服务器发送HTTP请求。 我宁愿使用websockets,但我不知道如何在共享主机网站上实现websocket服务器。
9 个月之前 回复
douqian8238
douqian8238 而且更符合我原来的诙谐回答:你能给设备发送电子邮件吗?
9 个月之前 回复
douchen4534
douchen4534 不知何故,你必须从主机到客户端获得异​​步消息。 轮询是正常的方式。 你已经在尝试使用websockets,这很好,但我怀疑你在Host服务器中也需要某种操作系统管道(从处理按钮点击POST的进程获取消息到有 websocket)。 如果你真的想要没有一个民意调查风格的流程(我仍然怀疑民意调查是一个很好的解决方案),那么看看Redis是否会起作用(归功于Majid Yousefi!)。
9 个月之前 回复
douji9734
douji9734 感谢您的回答,您的示例推迟了HTTP响应,但它也在不断查询while循环中的数据库。 所以我没有得到任何性能提升,因为你现在每隔100ms查询一次数据库。 我的主要问题是设备每5秒查询一次数据库,因此我想推迟响应,以便仅在用户更改设备状态时才查询数据库。
9 个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问

相似问题

6
AFNetworking的异步get怎么传出数据?
6
怎么将数据库的字段查出来后相加
0
使用MongoDB的C驱动,将数据以分片(Gridfs)的方式存储到MongoDB数据库中?
5
微信小程序 如何读取数据库中当前登录用户对应的数据,并列表显示
4
为什么我单独get list中的值可以取到,但是输出整个list就没有这个值
4
微信小程序云数据库取值问题,我不知如何把取到的值传递出来
2
Jquery调用asp.net core web api开发的restful,post类型响应的却是get方法
1
php的相关问题连接数据库判断条件出现按钮 按下按钮实现页面跳转
9
http请求方式get跟post的疑问
1
flask从数据库提取数据有Null值时,页面中会显示为None,请问怎么样才能在页面中显示为空白
2
Spring中使用@Autowired省略get和set方法后如何获取实体对象的单个值
2
微信小程序云开发中对读取到的数据库某记录修改后再更新记录,怎么实现?
1
使用jpa按精度为天的时间分组,可是数据库的时间精度到秒,怎么办?谢谢
0
mariadb数据库乱码,如何解决。
5
插入数据库中的表时,如何让表的主键ID自动生成并递增?
3
怎么可以拿到axios里get的数据?
1
hibernate 的load()加载方式的性能体现在哪?
1
为什么django在注册邮箱时填写的数据和录入数据库的数据不一致?
1
在火狐上可以看到有响应,但是为何requests的get得到的text为空
3
python爬虫爬取数据存储进数据库的问题