单表单多表数据上传中数字型外键关联方案咨询

问题背景 #

现有多字段表单用于上传成员数据并回显，拆分了member_profile、member_qualifications等多张数据表，当前用member_name作为外键关联，但存在同名成员重复上传导致数据关联错误的风险，希望改用自增数字主键作为外键，确保数据关联准确。

具体实现步骤 #

1. 调整数据表结构 #

• 主表member_profile：添加自增整型主键member_id，设为PRIMARY KEY AUTO_INCREMENT；保留原有字段不变。
• 关联表（如member_qualifications、member_careers等）：
  • 添加member_id字段（类型与主表member_id一致，如INT）
  • 将member_id设置为外键，关联member_profile.member_id，可选择设置ON DELETE CASCADE（删除主表数据时自动删除关联数据）
  • 完成数据迁移后，可删除原有的member_name字段

2. 修改PHP上传处理脚本 #

核心变化是：插入member_profile后获取自动生成的member_id，后续插入关联表时用该member_id替代member_name作为关联字段，同时修复原代码的SQL注入风险。

修改后的完整代码如下：

<?php
    include "../partials/dbconnect.php";
?>

<?php
    if ($_SERVER['REQUEST_METHOD']=='POST') {
        $upload_name = $_POST['name'];
        $upload_pob = $_POST['pob'];
        $upload_dob = $_POST['dob'];
        $upload_father = $_POST['father'];
        $upload_mother = $_POST['mother'];
        date_default_timezone_set('Asia/Kolkata');
        $date = date('Y-m-d H:i:s');
        $member_id = 0; // 初始化成员ID

        // File upload handling
        if (isset($_FILES['image']) && $_FILES['image']['error'] === UPLOAD_ERR_OK) {
            $fileTmpPath = $_FILES['image']['tmp_name'];
            $fileName = $_FILES['image']['name'];
            $fileNameCmps = explode(".", $fileName);
            $fileExtension = strtolower(end($fileNameCmps));

            // Allowed file extensions
            $allowedfileExtensions = array('jpg', 'jpeg', 'png', 'avif', 'webp');

            if (in_array($fileExtension, $allowedfileExtensions)) {
                // Directory in which the file will be saved
                $uploadFileDir = '../member_images/';
                $dest_path = $uploadFileDir.$fileName;

                if (move_uploaded_file($fileTmpPath, $dest_path)) {
                    // 插入主表，使用预处理语句防止SQL注入
                    $sql_member_profile = "INSERT INTO `member_profile` (`member_image`, `member_name`, `birth_place`, `birthday`, `father`, `mother`) VALUES (?, ?, ?, ?, ?, ?)";
                    $stmt_profile = $conn->prepare($sql_member_profile);
                    $stmt_profile->bind_param("ssssss", $fileName, $upload_name, $upload_pob, $upload_dob, $upload_father, $upload_mother);
                    $result_member_profile = $stmt_profile->execute();

                    if ($result_member_profile) {
                        // 获取刚插入的自增member_id
                        $member_id = mysqli_insert_id($conn);
                        echo 'Image upload successfully';
                    }
                    $stmt_profile->close();

                    // Qualification start
                    $quas = $_POST['qua'];
                    $sqlQua = "INSERT INTO member_qualifications (member_id, member_qualification, date_upload) VALUES (?, ?, ?)";
                    $stmt1 = $conn->prepare($sqlQua);
                    for ($i = 0; $i < count($quas); $i++) {
                        $stmt1->bind_param("iss", $member_id, $quas[$i], $date);
                        $stmt1->execute();
                    }
                    $stmt1->close();
                    // Qualification end

                    // Career start
                    $cars = $_POST['car'];
                    $dates = $_POST['date'];
                    $sqlCar = "INSERT INTO member_careers (member_id, member_career_year, member_career_context, date_upload) VALUES (?, ?, ?, ?)";
                    $stmt2 = $conn->prepare($sqlCar);
                    for ($i = 0; $i < count($cars); $i++) {
                        $stmt2->bind_param("isss", $member_id, $dates[$i], $cars[$i], $date);
                        $stmt2->execute();
                    }
                    $stmt2->close();
                    // Career end

                    // Achievements start
                    $achis = $_POST['achi'];
                    $sqlAchi = "INSERT INTO member_achievements (member_id, member_achievement, date_upload) VALUES (?, ?, ?)";
                    $stmt3 = $conn->prepare($sqlAchi);
                    for ($i = 0; $i < count($achis); $i++) {
                        $stmt3->bind_param("iss", $member_id, $achis[$i], $date);
                        $stmt3->execute();
                    }
                    $stmt3->close();
                    // Achievements end

                    // Issues start
                    $isss = $_POST['iss'];
                    $sqlIss = "INSERT INTO member_issues (member_id, member_issue, date_upload) VALUES (?, ?, ?)";
                    $stmt4 = $conn->prepare($sqlIss);
                    for ($i = 0; $i < count($isss); $i++) {
                        $stmt4->bind_param("iss", $member_id, $isss[$i], $date);
                        $stmt4->execute();
                    }
                    $stmt4->close();
                    // Issues end

                    echo "File is successfully uploaded.";
                }else {
                    echo "There was some error moving the file to upload directory.";
                }
            }else {
                echo "Upload failed. Allowed file types: " . implode(',', $allowedfileExtensions);
            }
        }else {
            if (isset($_FILES['image']) && $_FILES['image']['error'] == UPLOAD_ERR_NO_FILE) {
                // 无文件上传时插入主表
                $sql_member_profile = "INSERT INTO `member_profile` (`member_image`, `member_name`, `birth_place`, `birthday`, `father`, `mother`) VALUES ('user.png', ?, ?, ?, ?, ?)";
                $stmt_profile = $conn->prepare($sql_member_profile);
                $stmt_profile->bind_param("sssss", $upload_name, $upload_pob, $upload_dob, $upload_father, $upload_mother);
                $result_member_profile = $stmt_profile->execute();

                if ($result_member_profile) {
                    $member_id = mysqli_insert_id($conn);
                    echo 'Image upload successfully';
                }
                $stmt_profile->close();

                // Qualification start
                $quas = $_POST['qua'];
                $sqlQua = "INSERT INTO member_qualifications (member_id, member_qualification, date_upload) VALUES (?, ?, ?)";
                $stmt1 = $conn->prepare($sqlQua);
                for ($i = 0; $i < count($quas); $i++) {
                    $stmt1->bind_param("iss", $member_id, $quas[$i], $date);
                    $stmt1->execute();
                }
                $stmt1->close();
                // Qualification end

                // Career start
                $cars = $_POST['car'];
                $dates = $_POST['date'];
                $sqlCar = "INSERT INTO member_careers (member_id, member_career_year, member_career_context, date_upload) VALUES (?, ?, ?, ?)";
                $stmt2 = $conn->prepare($sqlCar);
                for ($i = 0; $i < count($cars); $i++) {
                    $stmt2->bind_param("isss", $member_id, $dates[$i], $cars[$i], $date);
                    $stmt2->execute();
                }
                $stmt2->close();
                // Career end

                // Achievements start
                $achis = $_POST['achi'];
                $sqlAchi = "INSERT INTO member_achievements (member_id, member_achievement, date_upload) VALUES (?, ?, ?)";
                $stmt3 = $conn->prepare($sqlAchi);
                for ($i = 0; $i < count($achis); $i++) {
                    $stmt3->bind_param("iss", $member_id, $achis[$i], $date);
                    $stmt3->execute();
                }
                $stmt3->close();
                // Achievements end

                // Issues start
                $isss = $_POST['iss'];
                $sqlIss = "INSERT INTO member_issues (member_id, member_issue, date_upload) VALUES (?, ?, ?)";
                $stmt4 = $conn->prepare($sqlIss);
                for ($i = 0; $i < count($isss); $i++) {
                    $stmt4->bind_param("iss", $member_id, $isss[$i], $date);
                    $stmt4->execute();
                }
                $stmt4->close();
                // Issues end

                echo "File is successfully uploaded.";
            }else {
                echo "There was an error with the file upload. Error code: " . $_FILES['image']['error'];
            }
        }
    }
?>

3. 旧数据迁移（针对已有数据的情况） #

如果数据库中已有旧数据，需要将原有的member_name关联转换为member_id关联：

1. 给所有关联表添加member_id字段（INT类型，允许为空）
2. 执行更新语句匹配关联关系：

   -- 学历表迁移
   UPDATE member_qualifications q
   JOIN member_profile p ON q.member_name = p.member_name
   SET q.member_id = p.member_id;
   
   -- 职业表迁移
   UPDATE member_careers c
   JOIN member_profile p ON c.member_name = p.member_name
   SET c.member_id = p.member_id;
   
   -- 成就表、问题表执行类似语句
   

3. 验证数据关联正确后，删除关联表的member_name字段，并将member_id设置为外键

额外注意事项 #

• 全程使用预处理语句避免SQL注入风险，原代码直接拼接变量的写法存在安全隐患
• 外键约束可确保关联数据的一致性，防止出现孤立的无效关联数据

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