MySQL多对多关系场景下 如何用PHP将多选分类插入电影关联表

1. 调整前端多选控件配置 #

多选控件的name属性需要添加[]后缀，才能让PHP接收所有选中的分类ID数组，修改后的代码如下：

<div class="col-12 col-md-6">
        <div class="form-group">
            <label for="genre">Categories*</label>
            <div class="inner-addon right-addon">
                <i class="glyphicon fas fa-theater-masks"></i>
                <select class="selectpicker" name="data[genId][]" type="number" id="genId"
                        placeholder="Choose categories..." required multiple aria-label="Categories">
                    <option value="1">Action</option>
                    <option value="2">Comedy</option>
                    <option value="3">Drama</option>
                    <option value="4">Fantasy</option>
                    <option value="5">Horror</option>
                    <option value="6">Mystery</option>
                    <option value="7">Romance</option>
                    <option value="8">Sci-Fi</option>
                    <option value="9">Thriller</option>
                </select>
            </div>
        </div>
    </div>

2. 修改后端PHP处理逻辑 #

在电影数据插入完成后，先获取新插入记录的movId，再遍历选中的分类ID插入关联表即可，修改后的完整PHP代码如下：

<?php
require "settings/init.php";

if (!empty($_POST["data"])) {
    $data = $_POST["data"];
    $file = $_FILES;

    if (!empty($file["movImg"]["tmp_name"])){
        move_uploaded_file($file["movImg"]["tmp_name"], "uploads/" . basename($file["movImg"]["name"]));
    }

    // 插入电影基础数据
    $sql = "INSERT INTO movie (movName, movDesc, movRelDate, movRating, movActors, movProducers, movType, movReview, movImg) VALUES (:movName, :movDesc, :movRelDate, :movRating, :movActors, :movProducers, :movType, :movReview, :movImg)";
    $bind = [
        ":movName" => $data["movName"],
        ":movDesc" => $data["movDesc"],
        ":movRelDate" => $data["movRelDate"],
        ":movRating" => $data["movRating"],
        ":movActors" => $data["movActors"],
        ":movProducers" => $data["movProducers"],
        ":movType" => $data["movType"],
        ":movReview" => $data["movReview"],
        ":movImg" => (!empty($file["movImg"]["tmp_name"])) ? $file["movImg"]["name"] : NULL,
    ];
    $db->sql($sql, $bind, false);

    // 新增：获取刚插入的电影ID
    $movId = $db->lastInsertId();
    
    // 新增：处理多选分类插入关联表
    if (!empty($data['genId']) && is_array($data['genId'])) {
        foreach ($data['genId'] as $genId) {
            $relSql = "INSERT INTO genreconmovie (movId, genId) VALUES (:movId, :genId)";
            $relBind = [
                ':movId' => $movId,
                ':genId' => $genId
            ];
            $db->sql($relSql, $relBind, false);
        }
    }

    echo include 'includes/success.php';
    exit;
}
?>

注：如果你的数据库封装类获取自增ID的方法不是lastInsertId()，请替换为对应类提供的获取最后插入ID的方法即可。

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