LAST_INSERT_ID() is changed while using foreign key

Question

I have 5 tables (A,B,C,D,E) in my project.

A table has PK sysNum which is int(255) NOT NULL AUTO_INCREMENT.

B has PK sheetNum which is int(255) NOT NULL AUTO_INCREMENT, And B has FK sysNum REFERENCES A ON DELETE CASCADE ON UPDATE CASCADE.

C and D have same FK sheetNum REFERENCES B ON DELETE CASCADE ON UPDATE CASCADE.

E has FK sysNum REFERENCES A ON DELETE CASCADE ON UPDATE CASCADE.

I hava much data be inserted into these tables, so i chosed transaction.Here is my PHP code:

  $conn->beginTransaction();  
     ......
      $query="insert into A( ) VALUES ();";
      $stmt=$conn->query($query);
      $stmt->closeCursor();

      $query="insert into B (...,sysNum) VALUES(...,LAST_INSERT_ID());
      insert into C (...,sheetNum) VALUES(...,LAST_INSERT_ID());                  
      insert into D (...,sheetNum) VALUES(...,LAST_INSERT_ID());";
      $stmt=$conn->query($query);
      $stmt->closeCursor();

      for($i=5;$i<$eRN;$i++)
     {
      ......
      $query="insert into C (...,sheetNum) VALUES (...,LAST_INSERT_ID());";
      $stmt=$conn->query($query);
      $stmt->closeCursor();      
     }

     for($i=1;$i<$dRN;$i++)
     {
      ......
      $query="insert into D (...,sheetNum) VALUES (...,LAST_INSERT_ID());";
      $stmt=$conn->query($query);
      $stmt->closeCursor();      
     }
     ...
     $query="insert into E (sysNum,...) VALUES (LAST_INSERT_ID(),...);";
     $stmt=$conn->query($query);
     $stmt->closeCursor();       

     $query="insert into E (sysNum,...) values (LAST_INSERT_ID(),...);";
     $stmt=$conn->query($query);
     $stmt->closeCursor();
     $conn->commit();

After insert into A, the sysNum created by auto_increment is 20. And after insert into B, the sheetNum is 10 as the same way of A. So sheetNum of C and D are both 10. But the sysNum of E is 10, not 20.

Note: these 5 tables are works fine , before i added column to A. I set foreign_key_checks=0, and added a new column to A. And then set foreign_key_checks=1 back, but unfortunately E tables works fail.

I have no idea about it, Who can help me ?

Answer 1

When using LAST_INSERT_ID() this way, you will always get the value from the latest SQL statement, which is your key from inserting into B.
I think what you want is the last SQL statement to have the value from an earlier statement. You would be better to store this value when you've done the insert of A. You can use (for PDO)

$query="insert into A( ) VALUES ();";
$stmt=$conn->query($query);
$id = Sconn->lastInsertId();

For mysqli...

$query="insert into A( ) VALUES ();";
$stmt=$conn->query($query);
$id = Sconn->insert_id;

And then use this value for your insert in table E.