PHP导入CSV数据至MySQL：有效处理空字段的策略

foreach($gymarr as $row){
    $day = $row[0];
    $routine= $row[1];
    $time= $row[2];
    $type= $row[3];
    $run= $row[4];
    $weights= $row[5];
    $tally= $row[6];

    // 原始SQL拼接，当$row[N]为空时可能导致问题
    $sqlinsert = "INSERT INTO Gym (day, routine, time, type, run, weights, tally) 
                  VALUES ('$day', '$routine', $time, '$type', '$run', '$weights', tally)";

    $result = $conn->query($sqlinsert);
    // 错误处理通常在这里进行，但由于空值问题，可能导致整个插入失败
}

foreach($gymarr as $row){
    // 检查并处理每个字段的空值
    $day     = ($row[0] !== "") ? $row[0] : "N/A"; // 字符串类型，默认"N/A"
    $routine = ($row[1] !== "") ? $row[1] : "N/A"; // 字符串类型
    $time    = ($row[2] !== "") ? $row[2] : "0";   // 整型或浮点型，默认"0"
    $type    = ($row[3] !== "") ? $row[3] : "N/A"; // 字符串类型
    $run     = ($row[4] !== "") ? $row[4] : "0";   // 整型或浮点型
    $weights = ($row[5] !== "") ? $row[5] : "0";   // 整型或浮点型
    $tally   = ($row[6] !== "") ? $row[6] : "0";   // 整型或浮点型

    // 构建SQL插入语句
    // 注意：此处仍使用字符串拼接，下一节将介绍更安全的预处理语句
    $sqlinsert = "INSERT INTO Gym (day, routine, time, type, run, weights, tally) 
                  VALUES ('$day', '$routine', $time, '$type', '$run', '$weights', $tally)";

    $result = $conn->query($sqlinsert);

    if ($result === FALSE) {
        echo "Error inserting data: " . $conn->error . "\n";
    }
}

// 假设 $conn 已经是一个 MySQLi 连接对象

// 准备SQL语句，使用问号作为占位符
$stmt = $conn->prepare("INSERT INTO Gym (day, routine, time, type, run, weights, tally) 
                        VALUES (?, ?, ?, ?, ?, ?, ?)");

// 检查准备是否成功
if ($stmt === FALSE) {
    die("Prepare failed: " . $conn->error);
}

// 绑定参数：'s'表示字符串，'i'表示整数，'d'表示浮点数
// 根据实际数据类型调整类型字符串
$stmt->bind_param("ssisssi", $day, $routine, $time, $type, $run, $weights, $tally);

foreach($gymarr as $row){
    // 检查并处理每个字段的空值
    $day     = ($row[0] !== "") ? $row[0] : "N/A"; 
    $routine = ($row[1] !== "") ? $row[1] : "N/A"; 
    $time    = ($row[2] !== "") ? (int)$row[2] : 0; // 转换为整数
    $type    = ($row[3] !== "") ? $row[3] : "N/A"; 
    $run     = ($row[4] !== "") ? $row[4] : "0";   // 保持字符串形式，绑定时再转换
    $weights = ($row[5] !== "") ? $row[5] : "0";   // 保持字符串形式
    $tally   = ($row[6] !== "") ? (int)$row[6] : 0; // 转换为整数

    // 执行预处理语句
    if (!$stmt->execute()) {
        echo "Error inserting data: " . $stmt->error . "\n";
    }
}

// 关闭语句
$stmt->close();

$field_map = [
    'day'     => ['index' => 0, 'default' => 'N/A', 'type' => 's'],
    'routine' => ['index' => 1, 'default' => 'N/A', 'type' => 's'],
    'time'    => ['index' => 2, 'default' => 0,     'type' => 'i'],
    'type'    => ['index' => 3, 'default' => 'N/A', 'type' => 's'],
    'run'     => ['index' => 4, 'default' => 0,     'type' => 'i'],
    'weights' => ['index' => 5, 'default' => 0,     'type' => 'i'],
    'tally'   => ['index' => 6, 'default' => 0,     'type' => 'i'],
];

$columns = implode(', ', array_keys($field_map));
$placeholders = implode(', ', array_fill(0, count($field_map), '?'));
$sql = "INSERT INTO Gym ({$columns}) VALUES ({$placeholders})";

$stmt = $conn->prepare($sql);
if ($stmt === FALSE) {
    die("Prepare failed: " . $conn->error);
}

foreach($gymarr as $row_data){
    $params = [];
    $types = '';
    foreach ($field_map as $field_name => $config) {
        $value = $row_data[$config['index']];
        if ($value === "") {
            $processed_value = $config['default'];
        } else {
            // 根据类型进行强制转换
            switch ($config['type']) {
                case 'i':
                    $processed_value = (int)$value;
                    break;
                case 'd':
                    $processed_value = (float)$value;
                    break;
                default: // 's' 或其他
                    $processed_value = $value;
                    break;
            }
        }
        $params[] = $processed_value;
        $types .= $config['type'];
    }

    // 动态绑定参数
    $stmt->bind_param($types, ...$params);

    if (!$stmt->execute()) {
        echo "Error inserting data: " . $stmt->error . "\n";
    }
}
$stmt->close();