未收全预期数据时如何自动向数据库插入N/A？

当未收到某列的全部预期数据点时，能否自动向数据库插入"N/A"？如何实现：未收全数据时保留已接收的数量，剩余部分填充为"N/A"？比如Value1列预期29个数据点，仅收到25个，剩余4个自动填"N/A"，是否可行？

ESP32 代码 #

void sendDataToServer(String data) {
  HTTPClient http;

  String valuekey;
  if (i<= 29) {
    valuekey = "Value1";
  } else if(i>=30 && i<=59) {
    valuekey ="Value2";
  } else if (i>=60 && i<= 139) {
    valuekey ="Value3";
  }

  String url = "http://" + String(serverName)+ "/index.php?" + valuekey + "=" + data;
  http.begin(url);

  int httpResponseCode = http.GET();

  if (httpResponseCode == HTTP_CODE_OK) {
    Serial.println("Server response: " + http.getString());
    Serial.println("Data inserted: " + data);
    i++; 
  } else {
    Serial.println("HTTP request failed with error code: " + String(httpResponseCode));
  }

  http.end();  
}

PHP 代码 #

$value1Data = array();
$value2Data = array();
$value3Data = array();

if (isset($_GET['Value1'])) {
    $value1 = $_GET['Value1'];
    $value1Data[] = $value1;
}
if (isset($_GET['Value2'])) {
    $value2 = $_GET['Value2'];
    $value2Data[] = $value2;
}
if (isset($_GET['Value3'])) {
    $value3 = $_GET['Value3'];
    $value3Data[] = $value3;
}

foreach ($value1Data as $value1) {
    $sqlValue1 = "INSERT INTO dbo.ESP (Value1) VALUES (?)";
    $paramsValue1 = array($value1);
    $resultValue1 = sqlsrv_query($conn, $sqlValue1, $paramsValue1);

    if ($resultValue1 === false) {
        echo "Error inserting Value1 data: " . print_r(sqlsrv_errors(), true);
    } else {
        echo "Data inserted: Value1: " . $value1 . "<br>";
    }
}

foreach ($value2Data as $value2) {
    // Update Value2 column
    $updateQuery = "UPDATE TOP(1) dbo.ESP SET Value2 = ? WHERE Value2 IS NULL";
    $updateParams = array($value2);
    $updateResult = sqlsrv_query($conn, $updateQuery, $updateParams);

    if ($updateResult === false || $resultValue2 === false) {
        echo "Error updating or inserting Value2 data: " . print_r(sqlsrv_errors(), true);
    } else {
        echo "Data inserted and updated: Value2: " . $value2 . "<br>";
    }
}

foreach ($value3Data as $value3) {
    // Update Value3 column
    $updateQuery = "UPDATE TOP(1) dbo.ESP SET Value3 = ? WHERE Value3 IS NULL";
    $updateParams = array($value3);
    $updateResult = sqlsrv_query($conn, $updateQuery, $updateParams);

    if ($updateResult === false || $resultValue3 === false) {
        echo "Error updating or inserting Value3 data: " . print_r(sqlsrv_errors(), true);
    } else {
        echo "Data inserted and updated: Value3: " . $value3 . "<br>";
    }
}

实现方案 #

完全可行，核心思路是在服务器端（PHP）记录各列已接收的数据量，当数据传输周期结束后自动补充缺失的"N/A"条目，具体步骤如下：

1. 明确各列预期数据量 #

先定义每列的总数据点：

• Value1: 29个
• Value2: 30个（30-59区间）
• Value3: 80个（60-139区间）

2. 新增数据计数机制 #

在PHP中用会话（单设备场景）或数据库表（多设备场景）记录各列已接收的数据量。示例用Session实现（需在PHP开头添加session_start();）：

// 初始化计数
if (!isset($_SESSION['value1_count'])) $_SESSION['value1_count'] = 0;
if (!isset($_SESSION['value2_count'])) $_SESSION['value2_count'] = 0;
if (!isset($_SESSION['value3_count'])) $_SESSION['value3_count'] = 0;

// 接收数据时更新计数
if (isset($_GET['Value1'])) {
    $value1 = $_GET['Value1'];
    $value1Data[] = $value1;
    $_SESSION['value1_count']++;
}
// Value2、Value3同理，对应更新各自的session计数

3. 触发数据补充逻辑 #

ESP32完成一轮数据传输后（i>139），发送一个结束标记通知PHP开始补充缺失数据：

if(i > 139) {
  HTTPClient http;
  String url = "http://" + String(serverName)+ "/index.php?action=finish";
  http.begin(url);
  int httpResponseCode = http.GET();
  if(httpResponseCode == HTTP_CODE_OK) {
    Serial.println("Finish signal sent");
    i = 0; // 重置计数准备下一轮
  }
  http.end();
}

4. 补充缺失的"N/A"数据 #

PHP收到结束标记后，计算各列缺失数量并批量插入/更新：

if(isset($_GET['action']) && $_GET['action'] == 'finish') {
    $expected_counts = [
        'Value1' => 29,
        'Value2' => 30,
        'Value3' => 80
    ];

    // 处理Value1（插入新行填充N/A）
    $missing = $expected_counts['Value1'] - $_SESSION['value1_count'];
    if($missing > 0) {
        $sql = "INSERT INTO dbo.ESP (Value1) VALUES (?)";
        $params = array("N/A");
        for($j=0; $j<$missing; $j++) {
            $result = sqlsrv_query($conn, $sql, $params);
            if($result === false) {
                echo "Error inserting Value1 N/A: " . print_r(sqlsrv_errors(), true);
            }
        }
        echo "Added " . $missing . " N/A for Value1<br>";
    }

    // 处理Value2（更新空行填充N/A）
    $query = "SELECT COUNT(*) AS empty_count FROM dbo.ESP WHERE Value2 IS NULL";
    $result = sqlsrv_query($conn, $query);
    $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
    $missing_value2 = $expected_counts['Value2'] - $_SESSION['value2_count'];
    if($missing_value2 > 0 && $row['empty_count'] >= $missing_value2) {
        $sql = "UPDATE TOP(?) dbo.ESP SET Value2 = 'N/A' WHERE Value2 IS NULL";
        $params = array($missing_value2);
        $result = sqlsrv_query($conn, $sql, $params);
        if($result === false) {
            echo "Error updating Value2 N/A: " . print_r(sqlsrv_errors(), true);
        } else {
            echo "Updated " . $missing_value2 . " rows with N/A for Value2<br>";
        }
    }

    // Value3处理逻辑与Value2一致，替换字段名即可

    // 重置计数，准备下一轮传输
    $_SESSION['value1_count'] = 0;
    $_SESSION['value2_count'] = 0;
    $_SESSION['value3_count'] = 0;
}

注意事项 #

• 若ESP32可能中途重启，建议将计数存储在数据库而非Session，避免数据丢失；
• 确保数据库字段支持存储字符串（如将Value1设为VARCHAR类型）；
• 批量操作时可优化SQL语句，减少数据库请求次数（如使用批量INSERT）。

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