1

I want to insert two tables

A(id,firstName, lastName) and B(id, id from A, xyz).

How can I insert the two tables simultaneously using transaction?

And also if B is not inserted then rollback the A also. Could you please help me.

ngrashia
  • 9,869
  • 5
  • 43
  • 58
Sara
  • 325
  • 2
  • 3
  • 13
  • 2
    You can search for myssql Transaction on Google. Transaction can have multiple statements covering multiple tables. If transaction is rollback then all steps performed in that transaction will be reverted back, no extra efforts required. – AK47 Aug 13 '14 at 08:36
  • 1
    Even the most trivial search on google or in the [MySQL docs](http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-transactions.html) will tell you this. If you can't be bothered to even try to do this yourself, you'll find others can't be bothered to help you either. – Synchro Aug 13 '14 at 08:39

1 Answers1

1

Use mysql_insert_id() if you're going down that path.

<?
mysql_query("START TRANSACTION");

$q1 = mysql_query("INSERT INTO table A (id, firstName, lastName) VALUES (?, ?, ?)");

// This is your baby. The id of the last record inserted
$last_inserted_id = mysql_insert_id();

$q2 = mysql_query("INSERT INTO table b (id, id from A, xyz) VALUES (?, ?, ?)");

// If query1 and query2 succeeded, commit changes to your database
// Creates both records
if ($q1 && $q2) {
    mysql_query("COMMIT");
}
else {        
    // Else initiate a rollback, and no records are committed.
    mysql_query("ROLLBACK");
}

?>
rurouni88
  • 1,165
  • 5
  • 10
  • Thanks. Actually I want this in node js. I will try to implement this in nodejs.Thanks a lot – Sara Aug 13 '14 at 08:42