Using mysql_insert_id to insert a lot of data

Question

I want to use PHP and MySQL to insert data into a data table and its associated many-to-many table. The auto-incrementing primary ID of the data table is used in the many-to-many table. So, I need to know the ID's of the data table to insert into the many-to-many table.

My current thinking is that the best way to approach this is with a loop, which would first insert into the data table then use mysql_insert_id() to insert into the many-to-many table. Is that the best way to go about this?

Additionally, do I need to be concerned about getting the wrong id with that function? Many workers would be using this data script at the same time, so my concern is that an ID from one workers query would end up being returned in the call to mysql_insert_id() of another. Is that a valid concern and if so how can I deal with it?

Answer 1

Calling mysql_insert_id() on a table with an auto_increment id is exactly the right approach.

As long as you call mysql_insert_id() straight after you call mysql_query() to insert the row, you don't need to worry about getting the wrong id. PHP and MySQL look after all that for you.

Answer 2

If you can just lock the table.

LOCK TABLE myTable FOR WRITE;
SELECT MAX(id) FROM myTable;
INSERT....
INSERT....
INSERT....
SELECT MAX(id) FROM myTable;
UNLOCK TABLES;

This will prevent anyone else from doing anything with myTable, but you will then have the min/max range for your ids.

-daniel