Tutorial :Whats the best way to populate two tables with records that need to reference each other?


What is the best way to populate records in two tables that each need a reference to the primary key of the other?

My thoughts are either having a "link" table between them which is populated once both have been written to the db or by the following complex series of commands

Insert1  get identity1  Insert2   get identity2  update 1  

How would you do this in PHP whilst connected to a mySQL Database and maintain integrity?

Would you need to use transactions, if so how would you go about doing this?


Can I ask why the tables need to reference each other. If it is a straight 1:1 then I would suggest just putting the foreign key in one of the tables. the lookup is just as easy. And your foreign key can be properly enforced. otherwise you run into a chicken/egg scenario where the tables both need to reference each other. but one needs to be created first. This means at a certain point your database will be in a non consistent state.


If you really must do it , then definitely use transactions - to avoid orphan records

mysql_query("START TRANSACTION");  if(!mysql_query($query1))  {   $errmsg=mysql_error();      mysql_query("ROLLBACK");  }  else  {  $id1=mysql_insert_id();     $query2="insert into.....$id1...");     if(!mysql_query($query2))     {   $errmsg=mysql_error();         mysql_query("ROLLBACK");     }     $id2=mysql_insert_id();     if(!mysql_query("update tabel1 set my_key=$id2 where key=$id1"))     {   $errmsg=mysql_error();         mysql_query("ROLLBACK");     }    }  mysql_query("COMMIT");  


You need to use transactions. MySql supports this in newer versions, if you use the InnoDb storage engine (But not for MyIsam).

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »