php - 在 PHP 中将多选发送到 MySQL

Question

我正在尝试将两组多重选择上传到 MariaDB 数据库(多对多关系中间表)。所选选项已正确上传，但代码也在两个选择的最后一个条目中发送。换句话说，如果我从包含 10 个选项的第一个选择中选择选项 1 和 4，并从包含 15 个选项的第二个选择中选择选项 5 和 12，而不是填充 4 个条目 (1 和 5, 1 和12、4 和 5、4 和 12) 它填充了 9 个选项(应该存在的四个加上 1 和 15、4 和 15、10 和 15 等) 。每个选择都是从两个表中填充的。下面是代码。

<?php
include ("connect_movieDB.php");
connectDB();
    $display_block = "<h1>Populate Movie-Actor table</h1></br>
    <p>Select a movie and select the actors</p>";

    $get_movie_sql = "SELECT id as movie_id, movie_title, movie_releasedate FROM movies ORDER BY movie_title asc";

    $get_movie_results = mysqli_query($mysqli, $get_movie_sql) or die(mysqli_error($mysqli));

    $get_actor_sql = "SELECT id as actor_id, CONCAT_WS(' ', f_name, l_name) AS display_name FROM actors ORDER BY l_name asc, f_name asc";

    $get_actor_results = mysqli_query($mysqli, $get_actor_sql) or die(mysqli_error($mysqli));

    if ((mysqli_num_rows($get_movie_results) < 1) || (mysqli_num_rows($get_actor_results) < 1)) {
        $display_block .= "<p><em>Did we forget to populate the database?</em></p>";
    } else {
    $display_block .= "
  <form method=\"post\" action=\"".$_SERVER['PHP_SELF']."\">
  <p><label for=\"sel_movie\">Select a movie:</label></br>
  <select id=\"sel_movie\" size=\"10\" name=\"sel_movie[]\" required=\"required\" multiple=\"multiple\">
  <option value=\"\">--Select a Movie--</option>";

  while ($movies = mysqli_fetch_array($get_movie_results)) {
     $movieid = $movies['movie_id'];

     $display_movie_title = stripslashes($movies['movie_title']);

     $display_moviedate = stripslashes($movies['movie_releasedate']);

     $display_block .="<option value=\"".$movieid."\">".$display_movie_title." - (".$display_moviedate.")</option>";
  }

    $display_block .= "
    </select></br>
    <p><label for \"sel_actor\">Select the actors in the movie:</label></br>
    <select id=\"sel_actor\" size=\"10\" name=\"sel_actor[]\" required=\"required\" multiple=\"multiple\">
    <option value=\"\">--Select actor(s)--</option>";

    while ($actors = mysqli_fetch_array($get_actor_results)) {
        $actorid = $actors['actor_id'];

        $display_actor_name = stripslashes($actors['display_name']);

        $display_block .="<option value=\"".$actorid."\">".$display_actor_name."</option>";
    }
  $display_block .="
  </select></p>
    <input type=\"hidden\" name=\"sel_movie[]\" value='".$movieid."'/>
    <input type=\"hidden\" name=\"sel_actor[]\" value='".$actorid."'/>
    <button = type=\"submit\" name=\"submit\" value=\"addtotable\">Add Relationship</button>
    <p><a href=\"\\movieDBmenu.html\">Return to the main menu</a></p>
  </form>";
    }
if ($_POST) {
    if ((isset($_POST['sel_movie'])=="") || (isset($_POST['sel_actor'])=="")){
        header("location: addto_ma_table.php");
        exit;
    }
    connectDB();

    $movie_array = $_POST['sel_movie'];

    $actor_array = $_POST['sel_actor'];

    foreach($movie_array as $m) {
        foreach($actor_array as $a) {
            $check_sql = "SELECT movie_id, actor_id FROM movie_actor WHERE movie_id ='".$m."' AND actor_id ='".$a."'";

            $check_results = mysqli_query($mysqli, $check_sql) or die(mysqli_error($mysqli));

            $add_info_sql = "INSERT INTO movie_actor (movie_id, actor_id) VALUES ('".$m."', '".$a."')";

            $add_info_results = mysqli_query($mysqli, $add_info_sql) or die(mysqli_error($mysqli));

            header("Location: addto_ma_table.php");

        }
    }        mysqli_close($mysqli);
}
?>

感谢您的帮助

Answer 1

关闭第二个选择后，有两个隐藏输入。他们将查询中的最后一个值添加到 post 数组中。删除这些，它应该会工作得更好。