PHP PDO交易? [英] PHP PDO Transactions?

问题描述

我有一个注册页面，基本上我需要将数据插入4个表中.我是PDO的新手，对某些事情感到困惑.

I have a signup page and basically I need data inserted into 4 tables. I'm new to PDO and am confused over something.

基本上，如果任何插入操作失败，我不希望将任何内容添加到数据库中，这似乎很简单.

Basically if any of the inserts fail I don't want anything added to the database, that seems simple enough.

我的困惑是，我需要先在users表中插入用户的用户名，电子邮件，密码等，以便可以使用(不确定如何)uid MySQL给我的用户使用PDO(由MySQL自动递增) .我需要用户uid MySQL为其他表提供了我的用户，因为其他表需要uid，因此所有内容都正确地链接在一起.我的表是InnoDB，我的外键从users_profiles(user_uid)，users_status(user_uid)，users_roles(user_uid)到users.user_uid，因此它们都链接在一起.

My confusion is, I need to first insert the users username, email, password etc in my users table 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表中之后(这样我就可以得到MySQL给用户的uid)，如果其他任何插入操作失败，它将删除数据插入到users表中.

But at the same time I want to ensure that if for example after data is inserted in the users table (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 users table.

我认为最好显示我的代码；我已经注释掉了代码，并在代码中进行了解释，这可能使它更易于理解.

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;
}

我是PDO的新手，可能上面的错误或问题我还没有注意到，因为在找到问题之前我还无法进行测试.

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.

1)我需要知道如何首先将用户数据插入用户表中，以便获得MySQL给我的用户的uid

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

2)然后在其他表中获取所需的uid

2) Then get the uid as I need it for the other tables

3)但同时，如果在插入用户表后由于某种原因查询失败，则该数据也会从用户表中删除.

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.

更新:

我更新了上面的代码以反映有用的成员提供的更改.

I updated code above to reflect changes that was offered by helpful members.

推荐答案

此函数返回刚刚插入的记录的主键: PDO :: lastInsertId NEED_USERS_UID_FOR_HERE参数将需要它.在INSERT语句之后使用它.

This function returns primary key of just inserted record: PDO::lastInsertId You will need it for NEED_USERS_UID_FOR_HERE parameter. Use it just after INSERT statement.

自从开始事务以来，如果您为MySQL表使用InnoDB引擎(MyISAM不支持事务)，如果发生任何错误，数据将不会插入任何表中.

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).

这篇关于PHP PDO交易?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！