PHP PDO 事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10155946/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP PDO Transactions?
提问by PHPLOVER
I have a signup page and basically I need data inserted into 4 tables. I'm new to PDO and am confused over something.
我有一个注册页面，基本上我需要将数据插入到 4 个表中。我是 PDO 的新手，对某些事情感到困惑。
Basically if any of the inserts fail I don't want anything added to the database, that seems simple enough.
基本上，如果任何插入失败，我不希望向数据库添加任何内容，这似乎很简单。
My confusion is, I need to first insert the users username, email, password etc in my userstable so I can get (not sure how) using PDO the uid MySQL has given my user (auto incremented by mysql). I need the user uid MySQL gave my user for the other tables as the other tables needs the uid so everything is linked properly together. My tables are InnoDB and I have foreign keys going from users_profiles(user_uid), users_status(user_uid), users_roles(user_uid) to the users.user_uid so they are all linked together.
我的困惑是，我需要首先在我的users表中插入用户的用户名、电子邮件、密码等，以便我可以（不确定如何）使用 uid MySQL 为我的用户提供的 PDO（由 mysql 自动增加）。我需要用户 uid MySQL 给我的用户用于其他表，因为其他表需要 uid，因此所有内容都正确链接在一起。我的表是 InnoDB，我有从 users_profiles(user_uid)、users_status(user_uid)、users_roles(user_uid) 到 users.user_uid 的外键，所以它们都链接在一起。
But at the same time I want to ensure that if for example after data is inserted in the userstable (so I can get the uid MySQL gave user) that if any of the other inserts fail that it removes the data that was inserted into the userstable.
但同时我想确保，如果例如在users表中插入数据后（这样我就可以获得 MySQL 给用户的 uid），如果任何其他插入失败，它会删除插入到users表中的数据.
I thinks it's best I show my code; I have commented out the code and have explained in the code which may make it easier to understand.
我认为最好展示我的代码；我已经注释掉了代码并在代码中进行了解释，这可能会使它更容易理解。
// Begin our transaction, we need to insert data into 4 tables:
// users, users_status, users_roles, users_profiles
// connect to database
$dbh = sql_con();
// begin transaction
$dbh->beginTransaction();
try {
// this query inserts data into the `users` table
$stmt = $dbh->prepare('
INSERT INTO `users`
(users_status, user_login, user_pass, user_email, user_registered)
VALUES
(?, ?, ?, ?, NOW())');
$stmt->bindParam(1, $userstatus, PDO::PARAM_STR);
$stmt->bindParam(2, $username, PDO::PARAM_STR);
$stmt->bindParam(3, $HashedPassword, PDO::PARAM_STR);
$stmt->bindParam(4, $email, PDO::PARAM_STR);
$stmt->execute();
// get user_uid from insert for use in other tables below
$lastInsertID = $dbh->lastInsertId();
// this query inserts data into the `users_status` table
$stmt = $dbh->prepare('
INSERT INTO `users_status`
(user_uid, user_activation_key)
VALUES
(?, ?)');
$stmt->bindParam(1, $lastInsertID, PDO::PARAM_STR);
$stmt->bindParam(2, $activationkey, PDO::PARAM_STR);
$stmt->execute();
// this query inserts data into the `users_roles` table
$stmt = $dbh->prepare('
INSERT INTO `users_roles`
(user_uid, user_role)
VALUES
(?, ?)');
$stmt->bindParam(1, $lastInsertID, PDO::PARAM_STR);
$stmt->bindParam(2, SUBSCRIBER_ROLE, PDO::PARAM_STR);
$stmt->execute();
// this query inserts data into the `users_profiles` table
$stmt = $dbh->prepare('
INSERT INTO `users_profiles`
(user_uid)
VALUES
(?)');
$stmt->bindParam(1, $lastInsertID, PDO::PARAM_STR);
$stmt->execute();
// commit transaction
$dbh->commit();
} // any errors from the above database queries will be catched
catch (PDOException $e) {
// roll back transaction
$dbh->rollback();
// log any errors to file
ExceptionErrorHandler($e);
require_once($footer_inc);
exit;
}
I'm new to PDO and there maybe errors or problems above I have yet to notice because I can't test yet until I figure out my problem.
我是 PDO 的新手，上面可能存在我尚未注意到的错误或问题，因为在找出问题之前我还无法进行测试。
1) I need to know how I can insert the users data in the users table first so i can get the uid MySQL gave my user
1）我需要知道如何首先将用户数据插入到用户表中，这样我才能获得 MySQL 给我的用户的 uid
2) Then get the uid as I need it for the other tables
2）然后获取其他表所需的uid
3) But at the same time if a query fails for whatever reason after inserting into users table that the data is also deleted from the users table aswell.
3）但同时，如果在插入用户表后查询因任何原因失败，数据也会从用户表中删除。
UPDATE:
更新：
I updated code above to reflect changes that was offered by helpful members.
我更新了上面的代码以反映有用成员提供的更改。
采纳答案by Vladislav Rastrusny
This function returns primary key of just inserted record: PDO::lastInsertIdYou will need it for NEED_USERS_UID_FOR_HEREparameter. Use it just after INSERT statement.
此函数返回刚插入记录的主键：PDO::lastInsertId您将需要它作为NEED_USERS_UID_FOR_HERE参数。在 INSERT 语句之后使用它。
Since you started a transaction, data will not be inserted into any table if any error occures provided you use InnoDB engine for your MySQL tables (MyISAM doesn't support transactions).
由于您启动了一个事务，如果您对 MySQL 表使用 InnoDB 引擎（MyISAM 不支持事务），如果发生任何错误，数据将不会插入任何表中。
