PHP PDO Transactions?

Question

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.

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.

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

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. 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. Then get the uid as I need it for the other tables

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.

Answer 1

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.

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