在两个表中插入MySQL数据。

huangapple go评论71阅读模式
英文:

mysql insert in two tables

问题

我正尝试创建一个注册表单,您可以在其中同时注册您的组织和帐户。但是目前我不知道如何正确插入数据。因为如果要将用户插入表中,您还不知道组织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         |
+---------+------------------+----------+-----------------+

以下是您的PHP代码片段,我已删除无关内容并保留了翻译的部分:

// 检查数据库中是否存在相同的电子邮件地址
$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 ($row['email'] == $user_email) {
    $_SESSION["exists"] = "email";
    header('Location: ../register');
}

// 检查数据库中是否存在相同的组织名称
$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 ($row['organisation_name'] == $organisation_name) {
    $_SESSION["exists"] = "organisation";
    header('Location: ../register');
}

// 插入组织
$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);

if ($post_organisation_stmt->execute()) {
    $organisation_id = mysqli_insert_id($conn);
    $post_organisation_stmt->close();
    // 插入用户
    $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);
    
    if ($post_user_stmt->execute()) {
        $_SESSION["newaccount"] = "success";
        header('Location: ../login');
    } else {
        echo "Error updating record: " . mysqli_error($conn);
        $_SESSION["exists"] = "error";
        header('Location: ../register');
    }
} 

请注意,我已将mysql_insert_id更正为mysqli_insert_id以使其与mysqli函数库匹配。

英文:

I am trying to make a register form where you can register your organisation and account at the same time.
But at the moment I do not know how to insert the data correctly.
Because if you want to insert the user in the table you do not know the organisation_id yet...

I have tried using mysqli_insert_id($conn) to get the last inserted id but that is not really practical.

I also came to the problem that if the email of the user already existed that it would still register the organisation.

At this moment I am completely lost...

Here is a sample of my table structure with some dummy data:


organisations table

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

users table

+---------+------------------+----------+-----------------+
| 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');
            }
        } 

答案1

得分: 0

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

  1. 将数据插入到组织表中。
  2. 获取插入的组织ID。
  3. 将数据插入到用户表中。
  4. 如果电子邮件已存在,则不创建用户,但组织仍然存在于数据库中。

可以通过使用事务来解决此问题,详见 https://www.php.net/manual/en/mysqli.begin-transaction.php 如果使用mysqli。

当您在事务中运行这两个查询时,当出现异常,比如用户电子邮件的唯一约束冲突时,可以回滚所有更改。

英文:

It seems that the problem you are describing is this:

  1. INSERT INTO organisation
  2. Get inserted organisation id
  3. INSERT INTO user
  4. If email already exists, then no user is created but the organisation remains in the database.

This can be resolved by using a transaction, see https://www.php.net/manual/en/mysqli.begin-transaction.php if using mysqli.

When you run both queries in a transaction, then all changes can be rolled back when an exception occurs such as a unique constraint violation for the user email.

huangapple
  • 本文由 发表于 2020年1月6日 18:39:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/59610603.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定