PHP中将多个相关数组批量插入数据库的策略

<input type="text" name="type[]" value="VALUE1">
<input type="text" name="color[]" value="#TEST1">
<!-- ... 更多字段 ... -->

<input type="text" name="type[]" value="VALUE2">
<input type="text" name="color[]" value="#TEST2">
<!-- ... 更多字段 ... -->

array(3) {
  ["type"]=> array(3) { [0]=> string(21) "VALUE1", [1]=> string(13) "VALUE2", [2]=> string(21) "VALUE3" }
  ["color"]=> array(3) { [0]=> string(9) "#TEST1", [1]=> string(9) "#TEST2", [2]=> string(9) "#TEST3" }
  ["thicknes"]=> array(3) { [0]=> string(2) "40", [1]=> string(2) "30", [2]=> string(2) "30" }
  ["fill"]=> array(3) { [0]=> string(3) "55", [1]=> string(3) "66", [2]=> string(3) "77" }
  ["thickness_2"]=> array(3) { [0]=> string(3) "0.4", [1]=> string(3) "0.3", [2]=> string(3) "0.3" }
  ["quantity"]=> array(3) { [0]=> string(3) "500", [1]=> string(3) "555", [2]=> string(3) "999" }
  ["email"]=> string(32) "<a class=\"__cf_email__\" ...></a>" // 注意：email通常是单个值
}

foreach($type as $t){
   foreach($color as $c){
     // ... 更多嵌套
        // query
     // ...
  }
}

<?php

// 模拟 $_POST 数据，实际应用中通过 $_POST 获取
$_POST = [
    'type' => ["VALUE1", "VALUE2", "VALUE3"],
    'color' => ["#TEST1", "#TEST2", "#TEST3"],
    'thicknes' => ["40", "30", "30"],
    'fill' => ["55", "66", "77"],
    'thickness_2' => ["0.4", "0.3", "0.3"],
    'quantity' => ["500", "555", "999"],
    'email' => "<a class=\"__cf_email__\" data-cfemail=\"7b0f1e080f3b0f1e080f55181416\" href=\"/cdn-cgi/l/email-protection\">[email protected]</a>"
];

if (isset($_POST['submit']) || !empty($_POST['type'])) { // 实际应用中判断submit按钮或至少一个数组非空
    // 从 $_POST 获取数据
    $types = $_POST['type'] ?? [];
    $colors = $_POST['color'] ?? [];
    $thicknesses = $_POST['thicknes'] ?? []; // 注意变量名拼写
    $fills = $_POST['fill'] ?? [];
    $thickness_2s = $_POST['thickness_2'] ?? [];
    $quantities = $_POST['quantity'] ?? [];
    $email_raw = $_POST['email'] ?? ''; // email通常是单个值

    // 假设 email 是一个固定的值，需要从原始HTML中提取
    // 这里简单地去除HTML标签，实际应用中可能需要更复杂的解析或直接从隐藏字段获取纯文本email
    $email = filter_var($email_raw, FILTER_SANITIZE_EMAIL);
    if (empty($email)) {
        // 如果 email 提取失败，可以设置默认值或抛出错误
        $email = 'unknown@example.com';
    }

    // 数据库连接配置 (请替换为您的实际配置)
    $dbHost = 'localhost';
    $dbName = 'your_database_name';
    $dbUser = 'your_username';
    $dbPass = 'your_password';

    try {
        $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // 禁用模拟预处理，提高安全性

        // 准备 SQL 插入语句
        // 确保数据库表结构与此匹配：ID | TYPE | COLOR | FILL | THICNKES_2 | QUANTITY | EMAIL
        $sql = "INSERT INTO your_table_name (TYPE, COLOR, FILL, THICNKES_2, QUANTITY, EMAIL)
                VALUES (:type, :color, :fill, :thickness_2, :quantity, :email)";
        $stmt = $pdo->prepare($sql);

        $insertedRows = 0;
        // 遍历其中一个数组，使用其键来访问其他数组
        foreach ($types as $key => $type_value) {
            // 数据验证和类型转换
            $current_color = $colors[$key] ?? null;
            $current_thickness = intval($thicknesses[$key] ?? 0); // 转换为整数
            $current_fill = intval($fills[$key] ?? 0); // 转换为整数
            $current_thickness_2 = floatval($thickness_2s[$key] ?? 0.0); // 转换为浮点数
            $current_quantity = intval($quantities[$key] ?? 0); // 转换为整数

            // 绑定参数
            $stmt->bindParam(':type', $type_value, PDO::PARAM_STR);
            $stmt->bindParam(':color', $current_color, PDO::PARAM_STR);
            $stmt->bindParam(':fill', $current_fill, PDO::PARAM_INT);
            $stmt->bindParam(':thickness_2', $current_thickness_2, PDO::PARAM_STR); // PDO::PARAM_STR for float
            $stmt->bindParam(':quantity', $current_quantity, PDO::PARAM_INT);
            $stmt->bindParam(':email', $email, PDO::PARAM_STR);

            // 执行插入
            $stmt->execute();
            $insertedRows++;
        }

        echo "成功插入 {$insertedRows} 条记录。<br>";

    } catch (PDOException $e) {
        echo "数据库操作失败: " . $e->getMessage() . "<br>";
        // 实际应用中应记录错误而非直接输出
    } catch (Exception $e) {
        echo "发生错误: " . $e->getMessage() . "<br>";
    }

} else {
    echo "没有提交数据或数据为空。<br>";
}

?>