如何在PHP表单中同时传递城市ID与名称至结果页？

我正在开发一个美国城市间旅行网站，用户在表单中选择两个城市后，结果页需要显示所选城市名称及对应旅行选项信息。数据库包含Cities、Route（桥接表）、Options三张表，目前表单已能通过POST传递城市ID到result.php用于查询路线，但无法同时获取城市名称——我需要ID来查询桥接表数据，同时要在页面显示城市名称。现有代码如下：

表单页代码 #

<form method="post" action="result.php">
  <select name="departOption">
  <?php
  $query = "SELECT * FROM cities;"; 
  $result = mysqli_query($conn, $query); 
  if (mysqli_num_rows($result) > 0) { 
      while($row = mysqli_fetch_assoc($result)) { 
        $CityNumber = $row["No"];
        $CityName = $row["City"];
        ?>
          <option value="<?php echo $CityNumber ?>"><?php echo $CityName ?> </option>
          <?php
      } 
  } else { 
      echo "-"; 
  } ?>
  </select>
  <select name="arriveOption">
  <?php
  $query = "SELECT * FROM cities;"; 
  $result = mysqli_query($conn, $query);
  if (mysqli_num_rows($result) > 0) { 
      while($row = mysqli_fetch_assoc($result)) { 
        $CityNumber = $row["No"];
        $CityName = $row["City"];
        ?>
          <option value="<?php echo $CityNumber ?>"><?php echo $CityName ?> </option>
          <?php
      } 
  } else { 
      echo "-"; 
  } ?>
  </select>
  <input type="submit" value="Submit the form"/>
</form>

result.php代码 #

<?php
  $option1 = isset($_POST['departOption']) ? $_POST['departOption'] : false;
  if ($option1) {
      echo "<p>".($_POST['departOption']) ."</p>";
  } else {
    echo "Depart Option is required";
    exit; 
  }
  $option2 = isset($_POST['arriveOption']) ? $_POST['arriveOption'] : false;
  if ($option2) {
      echo "<p>".($_POST['arriveOption']) ."</p>";
  } else {
    echo "Arrive Option is required";
    exit; 
  }
$query = "SELECT * FROM `route`,options  WHERE route.Depart_City = " . $option1 . " AND route.Arrive_City = " . $option2 . " AND route.Information = options.No";
$result = mysqli_query($conn, $query); 
if (mysqli_num_rows($result) > 0) { 
    while($row = mysqli_fetch_assoc($result)) { 
      $Option_Description = $row["Desc"];
      ?>
        <p> <?php echo $Option_Description ?></p>
      <?php
    } 
} else { 
    echo "No options available."; 
} ?>

方案1：通过ID反向查询城市名称（推荐） #

利用提交的城市ID，在result.php中查询Cities表获取对应名称，这种方式符合数据库设计规范，且避免前端传递数据被篡改的风险。

修改后的result.php代码：

<?php
// 获取提交的城市ID
$departId = isset($_POST['departOption']) ? $_POST['departOption'] : false;
$arriveId = isset($_POST['arriveOption']) ? $_POST['arriveOption'] : false;

// 校验必填项
if (!$departId) {
    echo "Depart Option is required";
    exit;
}
if (!$arriveId) {
    echo "Arrive Option is required";
    exit;
}

// 查询出发城市名称（使用预处理语句防SQL注入）
$cityStmt = mysqli_prepare($conn, "SELECT City FROM cities WHERE No = ?");
mysqli_stmt_bind_param($cityStmt, "i", $departId);
mysqli_stmt_execute($cityStmt);
$departRow = mysqli_fetch_assoc(mysqli_stmt_get_result($cityStmt));
$departCity = $departRow['City'];

// 查询到达城市名称
mysqli_stmt_bind_param($cityStmt, "i", $arriveId);
mysqli_stmt_execute($cityStmt);
$arriveRow = mysqli_fetch_assoc(mysqli_stmt_get_result($cityStmt));
$arriveCity = $arriveRow['City'];

// 显示城市名称
echo "<p>出发城市：{$departCity}</p>";
echo "<p>到达城市：{$arriveCity}</p>";

// 查询路线及选项信息（改用JOIN语法+预处理语句）
$routeStmt = mysqli_prepare($conn, "
    SELECT options.Desc 
    FROM `route` 
    JOIN options ON route.Information = options.No 
    WHERE route.Depart_City = ? AND route.Arrive_City = ?
");
mysqli_stmt_bind_param($routeStmt, "ii", $departId, $arriveId);
mysqli_stmt_execute($routeStmt);
$result = mysqli_stmt_get_result($routeStmt);

if (mysqli_num_rows($result) > 0) { 
    while($row = mysqli_fetch_assoc($result)) { 
        echo "<p>{$row['Desc']}</p>";
    } 
} else { 
    echo "No options available."; 
} 

// 关闭语句连接
mysqli_stmt_close($cityStmt);
mysqli_stmt_close($routeStmt);
?>

方案2：在表单选项中传递ID+名称组合 #

如果不想额外查询数据库，可以将option的value设置为ID与名称的组合（用分隔符拆分），在result.php中拆分后使用。

修改表单页的option代码 #

<option value="<?php echo $CityNumber . ',' . urlencode($CityName); ?>"><?php echo $CityName; ?></option>

（使用urlencode处理城市名称，避免特殊字符导致拆分错误）

修改后的result.php代码 #

<?php
$departValue = isset($_POST['departOption']) ? $_POST['departOption'] : false;
$arriveValue = isset($_POST['arriveOption']) ? $_POST['arriveOption'] : false;

if (!$departValue) {
    echo "Depart Option is required";
    exit;
}
if (!$arriveValue) {
    echo "Arrive Option is required";
    exit;
}

// 拆分出发城市ID和名称
list($departId, $departCity) = explode(',', $departValue);
$departCity = urldecode($departCity);

// 拆分到达城市ID和名称
list($arriveId, $arriveCity) = explode(',', $arriveValue);
$arriveCity = urldecode($arriveCity);

// 显示城市名称
echo "<p>出发城市：{$departCity}</p>";
echo "<p>到达城市：{$arriveCity}</p>";

// 查询路线信息（使用预处理语句防注入）
$stmt = mysqli_prepare($conn, "
    SELECT options.Desc 
    FROM `route` 
    JOIN options ON route.Information = options.No 
    WHERE route.Depart_City = ? AND route.Arrive_City = ?
");
mysqli_stmt_bind_param($stmt, "ii", $departId, $arriveId);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

if (mysqli_num_rows($result) > 0) { 
    while($row = mysqli_fetch_assoc($result)) { 
        echo "<p>{$row['Desc']}</p>";
    } 
} else { 
    echo "No options available."; 
} 
?>

原代码直接将POST参数拼接进SQL语句，存在严重SQL注入风险，上述两种方案均使用**预处理语句（prepared statements）**修复了该漏洞，这是PHP操作数据库的必备安全规范。

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