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?
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.
"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.
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.