PHP无法向MySQL的job和company表插入多条记录问题求助

咱们一步步拆解你遇到的「首次插入正常，第二次及以后无法向job和company表插入数据」的问题，从代码和数据库结构里能找到几个关键错误：

1. 致命的参数绑定错误（直接导致company插入失败） #

在company表的插入代码段里，你犯了一个低级但致命的错误：

$sql3 = "INSERT INTO company (name_de) VALUES (?)";
if ($stmt3 = mysqli_prepare($conn, $sql3) or die(mysqli_error($conn))){
    // 这里错用了$stmt4，应该是$stmt3！
    mysqli_stmt_bind_param($stmt4, 's', $param_name_de); 
    $param_name_de = $name_de;
}

你准备的语句对象是$stmt3，但绑定参数时却用了还未定义的$stmt4，这会导致参数绑定失败，执行$stmt3时没有传入name_de的值，自然插入失败。

2. Company表缺少必填字段插入 #

看你提供的company表DDL，以下字段都是NOT NULL约束且没有默认值：

• c_tel
• service_region
• member_type
• industry

但你的插入语句只给了name_de：

INSERT INTO company (name_de) VALUES (?)

数据库会因为违反非空约束拒绝插入请求。第一次能成功可能是你测试时表结构还没添加这些必填字段，或者当时有默认值，现在结构更新后没同步代码。

3. Job表插入存在SQL注入风险且绑定不规范 #

你直接把$person_id和$company_id拼接到SQL语句中：

$sql4 = "INSERT INTO job (person_id, company_id, department, position) VALUES ($person_id, $company_id, ?, ?)";

这不仅有SQL注入风险，还可能因为数据类型问题引发错误。正确的做法是用参数绑定所有变量：

$sql4 = "INSERT INTO job (person_id, company_id, department, position) VALUES (?, ?, ?, ?)";
if ($stmt4 = mysqli_prepare($conn, $sql4) or die(mysqli_error($conn))){
    // 注意类型符：i代表int，s代表string
    mysqli_stmt_bind_param($stmt4, 'iiss', $person_id, $company_id, $param_dep, $param_pos);
    $param_dep = $department;
    $param_pos = $position;
}

4. 事务处理逻辑混乱 #

• 你把mysqli_commit($conn);放在了验证成功的if块外面，即使输入验证失败，也会执行commit，这完全违背了事务的初衷。
• header("location: person.php");之后没有加exit;，导致后面的commit、close语句仍然会执行，可能引发意外的事务状态。
• 缺少错误回滚机制：如果任何一步插入失败，你没有回滚事务，会导致部分数据插入到数据库中。

修复后的完整代码片段 #

<?php
require_once '../conn.php';
// 定义变量...

if($_SERVER["REQUEST_METHOD"] == "POST"){
    // 输入验证，填充$err_xxx变量...

    if(empty($err_first_name) && empty($err_last_name) && empty($err_media_type) && empty($err_priority)){
        mysqli_autocommit($conn, FALSE);
        $transaction_success = true;

        // 插入person表
        $sql1 = "INSERT INTO person (first_name, last_name, media_type, magazine_sub, newsletter_sub, birthday, priority, remark, tel, mobile, fax, email, wechat) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        if ($stmt1 = mysqli_prepare($conn, $sql1)) {
            mysqli_stmt_bind_param($stmt1, 'sssiissssssss', $param_first_name, $param_last_name, $param_media_type, $param_magazine_sub, $param_newsletter_sub, $param_birthday, $param_priority, $param_remark, $param_tel, $param_mobile, $param_fax, $param_email, $param_wechat);
            $param_first_name = $first_name;
            $param_last_name = $last_name;
            $param_media_type = $media_type;
            $param_magazine_sub = $magazine_sub;
            $param_newsletter_sub = $newsletter_sub;
            $param_birthday = $birthday;
            $param_priority = $priority;
            $param_remark = $remark;
            $param_tel = $tel;
            $param_mobile = $mobile;
            $param_fax = $fax;
            $param_email = $email;
            $param_wechat = $wechat;

            if(!mysqli_stmt_execute($stmt1)){
                echo "Person插入失败: " . mysqli_stmt_error($stmt1);
                $transaction_success = false;
            } else {
                $person_id = mysqli_insert_id($conn);
            }
            mysqli_stmt_close($stmt1);
        } else {
            echo "Person语句准备失败: " . mysqli_error($conn);
            $transaction_success = false;
        }

        // 插入address表
        if($transaction_success){
            $sql2 = "INSERT INTO address (type, street, city, state, zip, country) VALUES (?, ?, ?, ?, ?, ?)";
            if ($stmt2 = mysqli_prepare($conn, $sql2)) {
                mysqli_stmt_bind_param($stmt2, 'ssssss', $param_address_type, $param_street, $param_city, $param_state, $param_zip, $param_country);
                $param_address_type = $address_type;
                $param_street = $street;
                $param_city = $city;
                $param_state = $state;
                $param_zip = $zip;
                $param_country = $country;

                if(!mysqli_stmt_execute($stmt2)){
                    echo "Address插入失败: " . mysqli_stmt_error($stmt2);
                    $transaction_success = false;
                } else {
                    $address_id = mysqli_insert_id($conn);
                }
                mysqli_stmt_close($stmt2);
            } else {
                echo "Address语句准备失败: " . mysqli_error($conn);
                $transaction_success = false;
            }
        }

        // 插入company表 - 补充所有必填字段
        if($transaction_success){
            // 注意：这里要根据你的表单补充c_tel、service_region等必填字段的变量
            $sql3 = "INSERT INTO company (name_de, c_tel, service_region, member_type, industry) VALUES (?, ?, ?, ?, ?)";
            if ($stmt3 = mysqli_prepare($conn, $sql3)) {
                mysqli_stmt_bind_param($stmt3, 'sssss', $param_name_de, $param_c_tel, $param_service_region, $param_member_type, $param_industry);
                $param_name_de = $name_de;
                $param_c_tel = $c_tel; // 你需要从表单获取这个值
                $param_service_region = $service_region; // 从表单获取
                $param_member_type = $member_type; // 从表单获取
                $param_industry = $industry; // 从表单获取

                if(!mysqli_stmt_execute($stmt3)){
                    echo "Company插入失败: " . mysqli_stmt_error($stmt3);
                    $transaction_success = false;
                } else {
                    $company_id = mysqli_insert_id($conn);
                }
                mysqli_stmt_close($stmt3);
            } else {
                echo "Company语句准备失败: " . mysqli_error($conn);
                $transaction_success = false;
            }
        }

        // 插入job表 - 全参数绑定
        if($transaction_success){
            $sql4 = "INSERT INTO job (person_id, company_id, department, position) VALUES (?, ?, ?, ?)";
            if ($stmt4 = mysqli_prepare($conn, $sql4)) {
                mysqli_stmt_bind_param($stmt4, 'iiss', $person_id, $company_id, $param_dep, $param_pos);
                $param_dep = $department;
                $param_pos = $position;

                if(!mysqli_stmt_execute($stmt4)){
                    echo "Job插入失败: " . mysqli_stmt_error($stmt4);
                    $transaction_success = false;
                }
                mysqli_stmt_close($stmt4);
            } else {
                echo "Job语句准备失败: " . mysqli_error($conn);
                $transaction_success = false;
            }
        }

        // 插入person_address表
        if($transaction_success){
            $sql5 = "INSERT INTO person_address (person_id, address_id) VALUES (?, ?)";
            if ($stmt5 = mysqli_prepare($conn, $sql5)) {
                mysqli_stmt_bind_param($stmt5, 'ii', $person_id, $address_id);
                if(!mysqli_stmt_execute($stmt5)){
                    echo "Person_Address插入失败: " . mysqli_stmt_error($stmt5);
                    $transaction_success = false;
                }
                mysqli_stmt_close($stmt5);
            } else {
                echo "Person_Address语句准备失败: " . mysqli_error($conn);
                $transaction_success = false;
            }
        }

        // 事务提交或回滚
        if($transaction_success){
            mysqli_commit($conn);
            header("location: person.php");
            exit; // 必须添加，防止后续代码执行
        } else {
            mysqli_rollback($conn);
            echo "事务执行失败，已回滚";
        }
    }
    mysqli_close($conn);
}
?>

额外建议 #

• 开启PHP错误提示（开发环境），能帮你快速发现变量未定义、语句错误等问题。
• 每次执行mysqli_stmt_execute后都检查结果，输出具体错误信息，这是排查数据库问题的关键。
• 对于company表的name_de、c_tel等UNIQUE字段，要在前端或后端添加重复值检查，避免插入时触发唯一约束错误。

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