时间戳

首页 > 解决方案 > mysql 在两个表中插入

php - mysql 在两个表中插入

问题描述

我正在尝试制作一个注册表单，您可以在其中同时注册您的组织和帐户。但目前我不知道如何正确插入数据。因为如果你想在表中插入用户，你还不知道 organization_id ......

我曾尝试使用mysqli_insert_id($conn)来获取最后插入的 id，但这并不实用。

我还遇到了一个问题，如果用户的电子邮件已经存在，它仍然会注册该组织。

这一刻，我彻底迷失了……

这是我的表结构示例，其中包含一些虚拟数据：

组织表

+-----------------+--------------------+
| organisation_id | organisation_name  |
+-----------------+--------------------+
|               1 | Google             |
|               2 | Facebook           |
+-----------------+--------------------+

用户表

+---------+------------------+----------+-----------------+
| user_id |      email       | password | organisation_id |
+---------+------------------+----------+-----------------+
|       1 | test@gmail.com   | *****    |       1         | 
|       2 | test@outlook.com | ******   |       2         |
+---------+------------------+----------+-----------------+

         //check for existing emails for user in database
        $get_email_stmt = $conn->prepare('SELECT `email` FROM `users` WHERE email = ?');
        $get_email_stmt->bind_param('s', $user_email);
        $get_email_stmt->execute();
        $get_email_result = $get_email_stmt->get_result();
        $row = $get_email_result->fetch_assoc();
        //if email does not exists execute query
        if ($row['email'] == $user_email) {
           //give error email exists
           $_SESSION["exists"] = "email";
           header('Location: ../register');
        }

        //check for existing organisation name for user in database
        $get_organisation_stmt = $conn->prepare('SELECT `organisation_name` FROM `organisations` WHERE organisation_name = ?');
        $get_organisation_stmt->bind_param('s', $organisation_name);
        $get_organisation_stmt->execute();
        $get_organisation_result = $get_organisation_stmt->get_result();
        $row = $get_organisation_result->fetch_assoc();
        //if email does not exists execute query
        if ($row['organisation_name'] == $organisation_name) {
           //give error email exists
           $_SESSION["exists"] = "organisation";
           header('Location: ../register');
        }

        //insert organisation
        $post_organisation_stmt = $conn->prepare('INSERT INTO `organisations` (organisation_name, zipcode, cityname, country, organisation_phonenumber, organisation_email, organisation_type, organisation_url, organisation_vat, agreed_to_avg) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);');
        $post_organisation_stmt->bind_param('sssssssssi', $organisation_name, $zipcode, $cityname, $country, $organisation_phonenumber, $organisation_email, $request, $organisation_url, $organisation_vat, $agreed_to_avg);

        //execute query
        if ($post_organisation_stmt->execute()) {
            $organistation_id = mysql_insert_id($conn);
            $post_organisation_stmt->close();
            //insert user
            $post_user_stmt = $conn->prepare('INSERT INTO `users` (firstname, lastname, email, phonenumber, organisation_id, hpassword, permission, agreed_to_avg) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?);');
            $post_user_stmt->bind_param('sssssssi', $firstname, $lastname, $user_email, $user_phonenumber, $organisation_id, $hPassword, $permission, $agreed_to_avg);
            //execute query
            if ($post_user_stmt->execute()) {
                //succes
                $post_answer_stmt->close();
                $_SESSION["newaccount"] = "success";
                header('Location: ../login');
            } else {
                //error
                echo "Error updating record: " . mysqli_error($conn);
                $post_answer_stmt->close();
                $_SESSION["exists"] = "error";
                header('Location: ../register');
            }
        }

标签： phpmysql

解决方案

您描述的问题似乎是这样的：

  1. 插入组织
  2. 获取插入的组织 ID
  3. 插入用户
  4. 如果电子邮件已存在，则不会创建用户，但组织仍保留在数据库中。

这可以通过使用事务来解决，如果使用 mysqli ，请参阅https://www.php.net/manual/en/mysqli.begin-transaction.php 。

当您在事务中运行这两个查询时，所有更改都可以在发生异常时回滚，例如用户电子邮件的唯一约束违规。

推荐阅读