title: mysql transaction - mixing insert & select to attain last_insert_id?
body: <p>I'm performing a transaction (using PDO), however I need to grab the insert id of the first element in the transaction, for example:</p>
  
  <pre><code>BEGIN
  INSERT INTO user (field1,field2) values (value1,value2)
  INSERT INTO user_option (user_id,field2) values (LAST_INSERT_ID(),value2);
  COMMIT;
  </code></pre>
  
  <p>Then do the pdo stuff:</p>
  
  <pre><code>[...]
  $pdo-&gt;execute();
  $foo = $pdo-&gt;lastInsertId(); // This needs to be the id from the FIRST insert
  </code></pre>
  
  <p>Is there a way to get the last insert id from the first element in a transaction? Perhaps using something like the following:</p>
  
  <pre><code>BEGIN
  INSERT INTO user (field1,field2) values (value1,value2)
  SELECT id AS user_id FROM user WHERE id=LAST_INSERT_ID()
  INSERT INTO user_option (user_id,field2) values (LAST_INSERT_ID(),value2);
  COMMIT;
  
  $pdo-&gt;execute();
  $fooArray = $pdo-&gt;fetchAll();
  $lastId = $fooArray[0]['user_id'];
  </code></pre>
  
  <p>Am I completely out to lunch with ^ ? Is there a better way to do this?</p>
  
  <p><strong>EDIT 1</strong> </p>
  
  <p>Based on suggestion, i've updated the query to use variables... however, i don't know how to retrieve the variable values using PDO. Using $stmt->fetchAll() just returns an empty array;</p>
  
  <pre><code>BEGIN
  DECLARE User_ID int
  DECLARE Option_ID int
  INSERT INTO user (field1,field2) values (value1,value2);
  set User_ID = select LAST_INSERT_ID();
  INSERT INTO user_option (user_id,field2) values (LAST_INSERT_ID(),value2);
  set Option_ID = select LAST_INSERT_ID();
  select User_ID, Option_ID
  COMMIT;
  </code></pre>