title: Mysql transactions, using LAST_INSERT_ID() in both the 2nd and 3rd query?
body: <p>I am using this query to insert data into two different tables:</p>
  
  <pre><code>    $query1="INSERT INTO node (cid, title) VALUES('$cid','$title')";
      $query2="INSERT INTO location (nid, street, city, state) VALUES(LAST_INSERT_ID(),'$address', '$city','$state')";
      $result = FALSE;
      if (mysql_query('BEGIN')) {
          if (mysql_query($query1) &amp;&amp;
          mysql_query($query2))
          $result = mysql_query('COMMIT'); // both queries looked OK, save
          else
          mysql_query('ROLLBACK'); // problems with queries, no changes
      }
  </code></pre>
  
  <p>basically, the first query throws the data into the node table which in turn increments a NID field.  This NID is used in the second query (my location table).</p>
  
  <p>Now I have changed my website a bit and I also want to insert that same NID into a 3rd table.  The problem is the second query (location table) also has an ID column (LID) that auto_increments as well, so if I try to use last_insert_ID it will just give me the LID and not the NID.</p>
  
  <p>I was thinking I could do a 3rd query outside of the transaction where I would query the database and grab the correct nid, but I want to keep it inside of a transaction to prevent any mixups.  Any ideas?</p>