如何通过<select>下拉框输出并编辑关联表与多对多表字段

我有三个数据表：filial_work、department_work、contact_work。目前仅能输出已分配的id_filial(id_department)，但需要显示所有选项并支持修改。

表结构 #

filial_work表 #

id_filial | filial

department_work表 #

id_department | department | department_email

contact_work表 #

id_contact | id_filial | id_department | full_name | email_user | work_number | tel_number

当前通过ID从contact_work表获取记录的脚本 #

<?php
include "../src/php/connect_db_tel-sprav-contact.php";
$id_contact = $_GET['id'];
$result = mysqli_query($induction, "SELECT * FROM `contact_work` WHERE `id_contact` = '$id_contact'");
$result = mysqli_fetch_assoc($result); 
?>
<!DOCTYPE html>
<html lang="ru">
<head>
  <meta charset="UTF-8">
  <link rel="stylesheet" href="/src/css/Create_contact_style.css">
  <title>编辑联系人</title></head>
<body>
<header><img src="/src/img/01-logo.webp" alt="Centrospas-Yugoria logo">
  <h1 class="logo">编辑联系人</h1></header>
<nav></nav>
<main></main>
<div class="visual_create_new_user">
  <form action="/src/php/script_change_contact.php " method="post">
    <input type="hidden" name="id_contact" value="<?= $result['id_contact'] ?>">
    <p>
      <button onclick="location.href='/src/TEST_Admin_Contact.php'">返回联系人列表</button>
    </p>
    <p>修改分支机构: <select>
        <option><?= $result['id_filial'] ?></option>
      </select></p>
    <p>修改部门: <select>
        <option><?= $result['id_department'] ?></option>
      </select></p>
    <p>修改职位: <input type="text" name="_post" value="<?= $result['post'] ?>"></p>
    <p>修改全名: <input required type="text" name="full_name" value="<?= $result['full_name'] ?>"></p>
    <p>修改邮箱: <input type="text" name="email_user" value="<?= $result['email_user'] ?>"></p>
    <p>修改工作电话: <input type="text" name="work_number" value="<?= $result['work_number'] ?>"></p>
    <p>修改私人电话: <input type="text" name="tel_number" value="<?= $result['tel_number'] ?>"></p>
    <p>
      <button type="submit">保存修改</button>
    </p>
  </form>
</div>
</body>
</html>

修改方案 #

要显示所有选项并保留当前选中状态，需先查询filial_work和department_work的全量数据，循环生成<option>标签时判断当前联系人的关联ID是否匹配，匹配则设置selected属性。

完整修改后的脚本 #

<?php
include "../src/php/connect_db_tel-sprav-contact.php";
$id_contact = $_GET['id'];

// 安全获取当前联系人信息（预处理语句防SQL注入）
$stmt = mysqli_prepare($induction, "SELECT * FROM `contact_work` WHERE `id_contact` = ?");
mysqli_stmt_bind_param($stmt, "i", $id_contact);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$result = mysqli_fetch_assoc($result);

// 获取所有分支机构数据
$filial_stmt = mysqli_prepare($induction, "SELECT * FROM `filial_work`");
mysqli_stmt_execute($filial_stmt);
$filial_result = mysqli_stmt_get_result($filial_stmt);
$filial_options = mysqli_fetch_all($filial_result, MYSQLI_ASSOC);

// 获取所有部门数据
$dept_stmt = mysqli_prepare($induction, "SELECT * FROM `department_work`");
mysqli_stmt_execute($dept_stmt);
$dept_result = mysqli_stmt_get_result($dept_stmt);
$department_options = mysqli_fetch_all($dept_result, MYSQLI_ASSOC);
?>
<!DOCTYPE html>
<html lang="ru">
<head>
  <meta charset="UTF-8">
  <link rel="stylesheet" href="/src/css/Create_contact_style.css">
  <title>编辑联系人</title></head>
<body>
<header><img src="/src/img/01-logo.webp" alt="Centrospas-Yugoria logo">
  <h1 class="logo">编辑联系人</h1></header>
<nav></nav>
<main></main>
<div class="visual_create_new_user">
  <form action="/src/php/script_change_contact.php " method="post">
    <input type="hidden" name="id_contact" value="<?= $result['id_contact'] ?>">
    <p>
      <button onclick="location.href='/src/TEST_Admin_Contact.php'">返回联系人列表</button>
    </p>
    <p>修改分支机构: 
      <select name="id_filial">
        <?php foreach($filial_options as $filial): ?>
          <option value="<?= $filial['id_filial'] ?>" <?= $filial['id_filial'] == $result['id_filial'] ? 'selected' : '' ?>>
            <?= $filial['filial'] ?>
          </option>
        <?php endforeach; ?>
      </select>
    </p>
    <p>修改部门: 
      <select name="id_department">
        <?php foreach($department_options as $dept): ?>
          <option value="<?= $dept['id_department'] ?>" <?= $dept['id_department'] == $result['id_department'] ? 'selected' : '' ?>>
            <?= $dept['department'] ?>
          </option>
        <?php endforeach; ?>
      </select>
    </p>
    <p>修改职位: <input type="text" name="_post" value="<?= $result['post'] ?>"></p>
    <p>修改全名: <input required type="text" name="full_name" value="<?= $result['full_name'] ?>"></p>
    <p>修改邮箱: <input type="text" name="email_user" value="<?= $result['email_user'] ?>"></p>
    <p>修改工作电话: <input type="text" name="work_number" value="<?= $result['work_number'] ?>"></p>
    <p>修改私人电话: <input type="text" name="tel_number" value="<?= $result['tel_number'] ?>"></p>
    <p>
      <button type="submit">保存修改</button>
    </p>
  </form>
</div>
</body>
</html>

关键说明 #

1. 获取全量选项数据：查询filial_work和department_work表，拿到所有分支机构和部门的完整列表。
2. 循环生成选项：用foreach遍历查询结果，生成<option>标签时显示名称而非ID，提升用户体验。
3. 保留选中状态：判断当前联系人的id_filial/id_department是否与选项ID一致，一致则添加selected属性，确保编辑页面打开时显示当前关联的选项。
4. SQL注入防护：替换原代码直接拼接参数的写法，使用预处理语句避免安全风险。

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