ESP32解析PHP服务器JSON数据遇HTTP 500错误，请求排查

按下下单按钮后，PHP服务器会向orders_pending表插入包含id及其他参数的记录，但ESP32发送GET请求获取数据时，始终返回HTTP响应码500，解析后locker_id为0。请求排查JSON发送与解析逻辑是否正确。

PHP服务器代码 #

if ($_SERVER["REQUEST_METHOD"] == "GET") {
    $api_key = test_input($_GET["api_key"]);
    if($api_key == $api_key_value) { 
        // Create connection
        $conn = new mysqli($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 
    
        
        $sql = "SELECT locker_id,quantity_ordered,quantity,payload FROM orders_pending WHERE order_id=(SELECT MIN(order_id) FROM orders_pending WHERE locker_id!=404)";
        $data=mysqli_query($conn,$sql);
        while($row = mysqli_fetch_assoc($data))
        $test = $row["locker_id"]; 
        if($test==NULL)
        {
            $row["locker_id"]="404";
        }
        ob_clean();
        header_remove();
        
        header('Content-Type: application/json; charset=utf-8');
        echo json_encode($row);
    
        $conn->close();
    }
    else {
        echo "Wrong API Key provided.";
    }

}
else {
    echo "No data posted with HTTP POST.";
}

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

ESP32代码 #

String checkPendingOrder()
{
  int lockerid = 0;
  String payload;
  

   //Check WiFi connection status
  if(WiFi.status() == WL_CONNECTED)
  {
    WiFiClient *client = new WiFiClient;
    //client->setInsecure(); //don't use SSL certificate
    HTTPClient http;
 
    // Your Domain name with URL path or IP address with path
    String serverPath = serverName + "?api_key=tPmAT5Ab3j7F9";
    http.useHTTP10(true);
    http.setTimeout(10000);
    http.begin(serverPath.c_str());
 
    // Specify content-type header
    http.addHeader("Content-Type", "application/x-www-form-urlencoded");
 
    // Send HTTP GET request
    int httpResponseCode = http.GET();
    //payload => variable which contains info about the lockers in DB
    String payload = http.getString();

if (httpResponseCode > 0) 
    {
      Serial.print("HTTP Response code check: ");
      Serial.println(httpResponseCode);
    }
    else 
    {
      Serial.print("Error check code check: ");
      Serial.println(httpResponseCode);
    }
    http.end();
  }
  else 
  {
    Serial.println("WiFi Disconnected");
  }
  
    return payload;}

void loop() 
{
  
  //inputs for locker feedback
  /*
  int l1 = digitalRead(lock1);
  int l2 = digitalRead(lock2);
  */

  //the locker id from the DB is assigned to the int variable lockerid
  //JsonDocument data= checkPendingOrder();
  //int lockerid=data["locker_id"].as<int>();
  //serializeJsonPretty(data,Serial);
 // scale.power_up();
  String data =checkPendingOrder();
  JsonDocument doc;
  deserializeJson(doc,data);
  int lockerid=doc["locker_id"];}

输出信息 #

HTTP Response code check: 500
0

排查与修复方案 #

1. PHP端导致HTTP 500的核心问题 #

• 查询无结果时的变量未初始化：当SQL查询无返回行时，$row未定义，执行$test = $row["locker_id"]会直接抛出致命错误，触发HTTP 500。
• 循环语法错误：while语句后无大括号，仅会执行下一行代码一次，无法正确遍历结果集。
• 空结果处理逻辑失效：无查询结果时$row不存在，直接修改$row["locker_id"]会报错。

修复后的PHP代码：

if ($_SERVER["REQUEST_METHOD"] == "GET") {
    $api_key = test_input($_GET["api_key"]);
    if($api_key == $api_key_value) { 
        $conn = new mysqli($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 
    
        $sql = "SELECT locker_id,quantity_ordered,quantity,payload FROM orders_pending WHERE order_id=(SELECT MIN(order_id) FROM orders_pending WHERE locker_id!=404)";
        $data=mysqli_query($conn,$sql);
        // 初始化默认结果
        $row = [
            "locker_id" => "404",
            "quantity_ordered" => 0,
            "quantity" => 0,
            "payload" => ""
        ];
        // 处理有效查询结果
        if ($fetchedRow = mysqli_fetch_assoc($data)) {
            $row = $fetchedRow;
            if(is_null($row["locker_id"])) {
                $row["locker_id"] = "404";
            }
        }
        ob_clean();
        header_remove();
        
        header('Content-Type: application/json; charset=utf-8');
        echo json_encode($row);
    
        $conn->close();
    }
    else {
        echo "Wrong API Key provided.";
    }

}
else {
    echo "No data posted with HTTP POST.";
}

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

2. PHP端额外排查建议 #

• 开启错误输出：在代码开头添加error_reporting(E_ALL); ini_set('display_errors', 1);，可直接看到服务器端的具体错误信息，快速定位问题。
• 确认$api_key_value变量已正确定义，避免API校验失败。

3. ESP32端问题与优化 #

• 移除多余请求头：GET请求无需设置Content-Type头，删除http.addHeader("Content-Type", "application/x-www-form-urlencoded");。
• 增加响应码判断：仅当响应码为200时才解析JSON，否则返回默认值，避免解析错误内容导致locker_id为0。
• 优化变量作用域：函数内避免重复声明payload变量，统一初始化逻辑。

修复后的ESP32代码片段：

String checkPendingOrder()
{
  String payload = "{\"locker_id\":404}";

  if(WiFi.status() == WL_CONNECTED)
  {
    WiFiClient client;
    HTTPClient http;

    String serverPath = serverName + "?api_key=tPmAT5Ab3j7F9";
    http.useHTTP10(true);
    http.setTimeout(10000);
    http.begin(client, serverPath.c_str());

    int httpResponseCode = http.GET();

    if (httpResponseCode == HTTP_CODE_OK) 
    {
      Serial.print("HTTP Response code check: ");
      Serial.println(httpResponseCode);
      payload = http.getString();
    }
    else 
    {
      Serial.print("Error check code: ");
      Serial.println(httpResponseCode);
    }
    http.end();
  }
  else 
  {
    Serial.println("WiFi Disconnected");
  }
  
  return payload;
}

void loop() 
{
  String data = checkPendingOrder();
  JsonDocument doc;
  DeserializationError error = deserializeJson(doc, data);
  
  if (error) {
    Serial.print("JSON解析失败: ");
    Serial.println(error.c_str());
    int lockerid = 404;
    Serial.println(lockerid);
  } else {
    int lockerid = doc["locker_id"].as<int>();
    Serial.println(lockerid);
  }
}

内容的提问来源于stack exchange，提问作者eu Andrei