title: PHP PDO Transactions?
body: <p>I have a signup page and basically I need data inserted into 4 tables. I'm new to PDO and am confused over something.</p>
  <p>Basically if any of the inserts fail I don't want anything added to the database, that seems simple enough.</p>
  <p>My confusion is, I need to first insert the users username, email, password etc in my <code>users</code> 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.</p>
  <p>But at the same time I want to ensure that if for example after data is inserted in the <code>users</code> 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 <code>users</code> table.</p>
  <p>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.</p>
  <pre><code>// 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-&gt;beginTransaction();
  
  try {
  
      // this query inserts data into the `users` table
      $stmt = $dbh-&gt;prepare('
                          INSERT INTO `users`
                          (users_status, user_login, user_pass, user_email, user_registered)
                          VALUES
                          (?, ?, ?, ?, NOW())');
  
      $stmt-&gt;bindParam(1, $userstatus,     PDO::PARAM_STR);
      $stmt-&gt;bindParam(2, $username,       PDO::PARAM_STR);
      $stmt-&gt;bindParam(3, $HashedPassword, PDO::PARAM_STR);
      $stmt-&gt;bindParam(4, $email,          PDO::PARAM_STR);
      $stmt-&gt;execute();
  
      // get user_uid from insert for use in other tables below
      $lastInsertID = $dbh-&gt;lastInsertId();
  
      // this query inserts data into the `users_status` table
      $stmt = $dbh-&gt;prepare('
                          INSERT INTO `users_status`
                          (user_uid, user_activation_key)
                          VALUES
                          (?, ?)');
  
      $stmt-&gt;bindParam(1, $lastInsertID,     PDO::PARAM_STR);
      $stmt-&gt;bindParam(2, $activationkey,    PDO::PARAM_STR);
      $stmt-&gt;execute();
  
      // this query inserts data into the `users_roles` table
      $stmt = $dbh-&gt;prepare('
                          INSERT INTO `users_roles`
                          (user_uid, user_role)
                          VALUES
                          (?, ?)');
  
      $stmt-&gt;bindParam(1, $lastInsertID,      PDO::PARAM_STR);
      $stmt-&gt;bindParam(2, SUBSCRIBER_ROLE,    PDO::PARAM_STR);
      $stmt-&gt;execute();
  
      // this query inserts data into the `users_profiles` table
      $stmt = $dbh-&gt;prepare('
                          INSERT INTO `users_profiles`
                          (user_uid)
                          VALUES
                          (?)');
  
      $stmt-&gt;bindParam(1, $lastInsertID,      PDO::PARAM_STR);
      $stmt-&gt;execute();
  
      // commit transaction
      $dbh-&gt;commit();
  
  } // any errors from the above database queries will be catched
  catch (PDOException $e) {
      // roll back transaction
      $dbh-&gt;rollback();
      // log any errors to file
      ExceptionErrorHandler($e);
      require_once($footer_inc);
      exit;
  }
  </code></pre>
  <p>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.</p>
  <ol>
  <li><p>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</p>
  </li>
  <li><p>Then get the uid as I need it for the other tables</p>
  </li>
  <li><p>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.</p>
  </li>
  </ol>