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 intial readings upon start up are acurate but subsequent changes are erratic sometimes received but generally repeating the initial state. This has been confirmed by printed responses at microprpocessor 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.