如何使用php MySQL在多对多关系上插入数据？

提问者：小点点

如何使用php MySQL在多对多关系上插入数据？

我想同时插入教师桌和教师教室桌。 ie:我想在教师表中插入一个教师，并用每个表的标识符自动插入连接教师和教室两个表的第三个表中。第三个表包含教师表和教室表的外键。

表:teacher:idteacher（主键）clasnom:idclasnom（主键）teacherclasnom:idteacher（外键）和idclasnom（外键）$sql=“插入到teacher（teachername，teacheremail）值('$name'，'$email')”；

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "univ";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection(vérifier la connexion)
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$name = $_POST['teachername'];
$email = $_POST['teacheremail'];
$sql = "INSERT INTO teacher (teachername, teacheremail) VALUES('$name', '$email')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
 <html>
    <head>
        <meta charset="utf-8">
    </head>
<body >
<form action="http://localhost/univ/teacherclassroom.php" method="post">
    <br>
     <h1 > Adding a new teacher</h1>
                   <div>
                        <label>TeacherName</label>
                        <input type="text" name="teachername"  required value="" />
                    </div>
                    <br><br>
                    <div >
                        <label>TeacherEmail</label>
                        <input type="text" name="teacheremail"  required value="" />
                    </div>
                    <br><br>
                    <div >
                        <label>Classroom</label>
                        <select class="classroom" >
                            <option>Select Classroom</option>
                            <?php
                            $conn = new mysqli("localhost", "root", "", "univ");
                            mysqli_set_charset($conn,"utf8");
                           // Check connection
                              if ($conn->connect_error) {
                            die("Connection failed: " . $conn->connect_error);
                                  }
                            $query = "SELECT * FROM classroom where 1 ";
                            $do = mysqli_query($conn,$query);
                            while ($row = mysqli_fetch_array($do)){
                                echo'<option value="'.$row['idclassroom'].'">'.$row['classroomname'].'</option>';
                            }
                            ?>
                        </select>
                    </div>
                    <br><br>
    <button name="ADD" value="ADD" type="submit" style="background-color:yellowgreen;"> <b> ADD </b> </button>
</form>
</body>
</html>

共1个答案

匿名用户

您可以有一个可以用call执行的过程。或者使用触发器。

使用事务并同时完成所有必要的插入。

您可以获得上次插入id，在执行插入时，您不想指定PK列。

START TRANSACTION;
INSERT INTO table1 (a,b,c,d) VALUES (1,2,3,4);
INSERT INTO table2 (fkId) VALUES (LAST_INSERT_ID());
COMMIT

https://www.mysqltutorial.org/mysql-last_insert_id.aspx/

https://www.mysqltutorial.org/mysql-transaction.aspx/

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html