时间戳

首页 > 解决方案 > PHP：如何将 HTML 表单中的数据插入到具有多对多关系的表中？

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

问题描述

例如，我有以下 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也许我在做隧道观察。大家可以分享一些见解吗？

编辑 我想出了以下代码：

    $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语法...

标签： phpmysqlrelational-database

解决方案

最简单的方法是：

$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);
 }

推荐阅读