Most Efficient way to monitor a value in SQL Database?

[mayankbudhwani]

Greetings..
So I am trying to build a remote control project.
I want to switch a light on/off using a relay when a value on SQL Database changes.
(I am using a NodeMCU)
Currently I am using code which monitors the value in every iteration, which works, but is very inefficient as this sends WAY too many requests to my server.
Here is my code:

Code: [Select]


#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>

const char *ssid =  "SSID";     // replace with your wifi ssid and wpa2 key
const char *pass =  "PASSWORD";

WiFiClient client;
 
void setup()
{
 
       Serial.begin(9600);
       delay(10);
       pinMode(LED_BUILTIN,OUTPUT);   
       pinMode(D0,OUTPUT);   
       Serial.println("Connecting to ");
       Serial.println(ssid);
 
       WiFi.begin(ssid, pass);
       while (WiFi.status() != WL_CONNECTED)
          {
            delay(500);
            Serial.print(".");
          }
      Serial.println("");
      Serial.println("WiFi connected");
}
 
void loop()
{

  if (WiFi.status() == WL_CONNECTED) {
 
    HTTPClient http;
 
    http.begin("http://SERVERADDRESS/updateDB/view.php?id=1"); 
    int httpCode = http.GET();                                                                 
 
    if (httpCode > 0) { //Check the returning code
 
      String payload = http.getString();   //Get the request response payload
      Serial.println(payload);
      if(payload == "0"){
        digitalWrite(LED_BUILTIN,HIGH);
        digitalWrite(D0,HIGH);
      }
      else if(payload == "1"){
        digitalWrite(LED_BUILTIN,LOW);
        digitalWrite(D0,LOW);
      }
      //Print the response payload
 
    }
 
    http.end();   //Close connection
 
  }

}

 

I was wondering if instead of this, is there any way the server could NOTIFY the nodeMCU when the value changes so that it does not have to check value every time?

I realize this is possible if I host a local server using the NodeMCU but I want to be able to control the device from outside the local network.

Any suggestions would be appreciated.

[steve_mcdonald]

I use several options depending on things like priority and importance.  My MySQL database receives many inputs from sensors and devices around the property.  Then I use one of two methods to have the server respond. 

One is to have a cron job which looks at changes to the database and responds accordingly. Another  is a trigger within MySQL. I also use a text messenger service to achieve a similar result. 

Before we go further, which of those seems the most appropriate?
It depends on whether you need urgent indication (an alarm condition) or just advice something happened, or interesting but not urgent.

For my perimeter control (say people opening my gate) I use text to my phone.  For non urgent indicators, I use cron jobs that check in every so often, for information only (temperature, wind etc, I use a web page updated from the database.
 
It depends on what you need to do.

[ohmnomnom]

You could look into WebSocket for your ESP3266 (see https://tttapa.github.io/ESP8266/Chap14%20-%20WebSocket.html) as long as you have control over the app that is writing to your database. Set up the app to send to your ESP3266 each time you open a connection to your SQL db and close. WebSocket is much more efficient than polling constantly.

If you do not need ESP3266 to see the data immediately, you can do as Steve suggested and set up a cron job. It would probably be much easier to set up and depending on how frequently you need to poll, even more efficient.