mysql inserting multiple tables and autoincrement

[garry27]

Is it possible to update multiple tables in a single query which all have autoincrement set in their the primary key field and are all connected  by their keys. The way I've done it in the past is update a table at a time then in between do a row count to find the value of the primary key field of the row that I've just inserted so i can add as a the foreign key value for the next table. This can get quite tiring when you have a bunch of tables that need updating. Is there a better way to do this?

[Psycho]

No, you cannot INSERT multiple records into separate tables with a single query. However, you can look into using stored procedures, which is beyond the ability to cover in a forum post. Also, you should never rely upon the number of records in a table to get the last insert ID. There is a built in function for this: mysql_insert_id(). Which will return the ID of the last record that was inserted.

[gizmola]

I'm not quite sure I follow what you are asking for.  If I have a dependent relationship (in data modelling parlance that would be called making the relationship "identifying") where TableB's primary key should be the same as TableA's then I"m not going to have an auto_increment defined for tableB.  That wouldn't make any sense.

 

With that said, the way to get the last auto_increment id allocated on a thread is to call mysql_insert_id.  So you frequently will see code that references this and uses the returned value to create child rows, setting either all or part of the pk, or foreign key values for subsequent inserts.