PHP:如何将数据从 HTML 表单插入到具有多对多关系的表中？

Question

例如我有以下3张表:

person        address        person_address
=========     ===========    =================
pid  (PK)     aid    (PK)    pid   (FK)
name          street         aid   (FK)
tel           city
...           ...

我建立了一个网站来创建新的person，您可以在其中添加此人的地址信息(通过HTML-Form)。因为一个person可以有多个address，一个address可以属于多个person，所以这是一个多对-许多关系。

现在目标是输入人的信息(包括地址信息)，人的个人信息和地址信息会自动存储到各自的表中(person和address ), 而 person_address 自动将 pid 与 aid 配对并存储它们。

我想这样做

<?php
  require_once("../conn.php");

  if($_SERVER["REQUEST_METHOD"] == "POST"){
  $name = $_POST["name"];
  $tel = $_POST["tel"];
  $street = $_POST["street"];
  $city = $_POST["city"];

  $sql1 = "INSERT INTO person (name, tel)
           VALUES ($_POST["name"], $_POST["tel"])";

  $sql2 = "INSERT INTO address (street, city)
           VALUES ($_POST["street"], $_POST["city"])";

  $sql3 = "INSERT INTO person_address /*Something follows...*/"

?>
<!--HTML...-->
<form action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);  ?>" 
method="post">
  <input type="text" name="name">
  <br>
  <input type="text" name="tel">
  <br>
  <input type="text" name="street">
  <br>
  <input type="text" name="city">
  <br>
  <input type="submit" value="Create">
</form>
<!--footer-->

我无法弄清楚如何将 pid 与 aid 配对...也许我是 tunnelvisioning。你们能分享一些见解吗？

编辑我想出了以下代码:

    $sql1 = "INSERT INTO person (first_name, last_name, media_type, magazine_sub, newsletter_sub, birthday, priority, remark)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

    if ($stmt1 = mysqli_prepare($conn, $sql1)) {
      mysqli_stmt_bind_param($stmt1, 'sssiisss', $param_first_name, $param_last_name, $param_media_type, $param_magazine_sub, $param_newsletter_sub, $param_birthday, $param_priority, $param_remark);

      $param_first_name = $first_name;
      $param_last_name = $last_name;
      $param_media_type = $media_type;
      $param_magazine_sub = $magazine_sub;
      $param_newsletter_sub = $newsletter_sub;
      $param_birthday = $birthday;
      $param_priority = $priority;
      $param_remark = $remark;

      $result1 = mysqli_query($conn, $sql1);
      while ($row=mysqli_fetch_row($result1)){
        $person_id = $row[0];
      }
    }



    $sql2 = "INSERT INTO address (type, street, city, state, zip, country)
                 VALUES (?, ?, ?, ?, ?, ?)";
    if ($stmt2 = mysqli_prepare($conn, $sql2)) {
    mysqli_stmt_bind_param($stmt2, 'ssssss', $param_address_type, $param_street, $param_city, $param_state, $param_zip, $param_country);

    $param_address_type = $address_type;
    $param_street = $street;
    $param_city = $city;
    $param_state = $state;
    $param_zip = $zip;
    $param_country = $country;

    $result2 = mysqli_query($conn, $sql2);
    while ($row=mysqli_fetch_row($result2)){
      $address_id = $row[0];
    }

    }
    mysqli_stmt_close($stmt2);


  $sql3 = "INSERT INTO person_address (person_id, address_id)
       VALUES ($person_id, $address_id)";
  mysqli_query($conn, $sql3);

  header("location: person.php");

  }
  mysqli_close($conn);

}

但是现在我在数据库中没有输入了。错误在哪里？我可能搞砸了 mysqli_stmt 语法...

Answer 1

最简单的方法是:

$sql1 = "INSERT INTO person (name, tel) VALUES ($_POST["name"], $_POST["tel"])";
$result=mysqli_query($db, $sql1);

现在您已经完成了插入，只需选择 pid。

$query="select pid from person where name='$name' and tel='$tel'";
$result=mysqli_query($db, $query); 
while($row=mysqli_fetch_row($result)) {
   $pid=$row[0];
} 

$sql2 = "INSERT INTO ...";
$result=mysqli_query($db, $sql2);

现在您已完成插入，只需选择辅助工具即可。

$query="select aid from address where name=...";
$result=mysqli_query($db, $query); 
while($row=mysqli_fetch_row($result)) {
   $aid=$row[0];
} 

 $sql3 = "INSERT INTO person_address $aid, $pid..." 

虽然值得注意，但您应该确实清理用户输入。这就是您如何成为 SQLi 和 XSS 的受害者。

首先在输入上使用 strip_tags。

isset ( $_REQUEST['name'] ) ? $name = strip_tags($_REQUEST['name']) : $name = "";

然后使用准备好的语句并对字符串进行转义。

 $name=mysqli_real_escape_string($db, $name);   
 if ($stmt = mysqli_prepare($db, "insert into person set pid='', name=?, tel=?")) {  
     mysqli_stmt_bind_param($stmt, "ss", $name, $tel);
     mysqli_stmt_execute($stmt); 
     mysqli_stmt_close($stmt);
 }