英文:
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
似乎您描述的问题是这样的:
- 将数据插入到组织表中。
- 获取插入的组织ID。
- 将数据插入到用户表中。
- 如果电子邮件已存在,则不创建用户,但组织仍然存在于数据库中。
可以通过使用事务来解决此问题,详见 https://www.php.net/manual/en/mysqli.begin-transaction.php 如果使用mysqli。
当您在事务中运行这两个查询时,当出现异常,比如用户电子邮件的唯一约束冲突时,可以回滚所有更改。
英文:
It seems that the problem you are describing is this:
- INSERT INTO organisation
- Get inserted organisation id
- INSERT INTO user
- 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论