I have a web page that controls and inputs switch states into a database.

I confirm this works.

There is a php program to read the switch states from the database via a Get command and send this data in a json format to a remote microprocessor.

As I sit behind a Nat an ESP32 periodically calls in to Get those switch states and changes processor pin states accordingly for control.

From experience interrogating the Json data at microprocessor level and changing the pin status also works.

The problem: I can call the php directly via a web interface (Chrome, firefox) and get the pin state readings accurately in real time.
However when making the same inquiry via the Esp the initial readings upon start up are accurate but subsequent changes are erratic sometimes received but generally repeating the initial state. This has been confirmed by printed responses at microprocessor level.

This will clear after varying times whereupon that readings at that time will become the default.

Switching off and rebooting the microprocessor makes no difference – strangely it defaults back to the last originating state.!

It is as though the states via an Esp call are cached somewhere and repeated as against a direct web inquiry through the php which does show the changes.

Is this a script problem ? and any ideas on how to correct it.

Code:

    <?php
    // board = 7
    // Database connection parameters
    $servername = "localhost";
    $dbname = "xxxxxxx";  // Database name
    $username = "yyyyyyy";  // User
    $password = "xxxx";  // Password

    $gpio = $state = $data = $row = "";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }

    // Set the board number for the query
    $board_number = 7;

    // Prepare and execute the SELECT query
    $sql = "SELECT gpio, state FROM switch_outputs WHERE board = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $board_number);
    $stmt->execute();
    $result = $stmt->get_result();

    // Fetch data and encode it as JSON
    $data = array();
    while ($row = $result->fetch_assoc()) {
    $data[] = $row;
    }

    // Update the database with the current time of the last request
    $update_sql = "UPDATE switch_outputs SET last_request = NOW() WHERE board = ?";
    $update_stmt = $conn->prepare($update_sql);
    $update_stmt->bind_param("i", $board_number);
    $update_stmt->execute();

    // Set content type to JSON and output the data
    header('Content-Type: application/json');
    echo json_encode($data);

    // Close statements and connection
    $stmt->close();
    $update_stmt->close();
    $conn->close();
    ?>

The Hosting service (Blue Host) claim it is a scripting problem. Server caching has been cleared without changing the resposne. I am unable to to check the database loggings as it is a shared hosting acount.
My concern is consistency of a timely resposne. Sometimes it works – sometimes it just locks up.

The following are extracts from the ESP code should it have any impact

   #include <HTTPClient.h>
   #include <ArduinoJson.h>
   
   //Other usual libraries
   
   // Define GPIO pins (adjust these to match the configuration)
   const int gpioPins[] = { 12, 13, 14, 17, 18, 19, 26, 27, 32, 33 }; // GPIO pins, adjust as needed
   const int numPins = sizeof(gpioPins) / sizeof(gpioPins[0]);
   
   TaskHandle_t Task1;   // program Heartbeat
   TaskHandle_t Task2;   // Read Switch State
   TaskHandle_t Task3;   // wifi watchdog
   TaskHandle_t Task4;   // email engine & pump status
   
   //---------------------------------
   
   // Task 2 - Pins Status
   
   //Get Data from Switch_State Table
   
       void fetchData() {
     if (WiFi.status() == WL_CONNECTED) {
       HTTPClient http;
       http.begin(url);
       int httpCode = http.GET();
       
       if (httpCode == HTTP_CODE_OK) {
         String payload = http.getString();
         Serial.println("Received payload:");
         Serial.println(payload);
         
         // Parse JSON
         DynamicJsonDocument doc(1024);
         deserializeJson(doc, payload);
   
         for (JsonObject elem : doc.as<JsonArray>()) {
           int gpio = elem["gpio"];
           int state = elem["state"];
           
           // Find the pin and set its state
           for (int i = 0; i < numPins; i++) {
             if (gpio == gpioPins[i]) {
               pinMode(gpioPins[i], OUTPUT);
               digitalWrite(gpioPins[i], state);
               Serial.printf("Set GPIO %d to state %dn", gpioPins[i], state);
               break;
             }
           }
         }
       } else {
         Serial.printf("Failed to fetch data, HTTP code: %dn", httpCode);
       }
       
       http.end();
     } else {
       Serial.println("WiFi not connected");
     }
   }
   
   
   // Check Pin State
   
   void Sstate(void * pvParameters) {
     for (;;) {
   
       TickType_t xLastWakeTime;
       const TickType_t xFrequency = 30000; // TESTING check every 30 sec (/ portTICK_PERIOD_MS ?)
       //Initialise the xLastWakeTime variable with the current time.
       xLastWakeTime = xTaskGetTickCount ();
       for ( ;; )
       {
         //Wait for the next cycle.
         vTaskDelayUntil( &xLastWakeTime, xFrequency );
   
         Serial.print("Pin check running on core ");
         Serial.println(xPortGetCoreID());
         Serial.println ();    
   
          fetchData () ;  
     }
       
     }
   }