Filling foreign key values in a MySQL transaction

2ygb4cmqetsjhx

Hi Guys. Just looking for some tips and pointers for a small project I am doing. I have some ideas but I am not sure if they are the best practice. I am using mysql and php.

I have a table called nomsing in the database.

It has a primary key called row id which is an integer.

Then I have about 8 other tables referencing this table.
That are called nomplu, accsing,accplu, datsing, datplu for instance.
Each has a column that references the primary key of nomsing.

Withing my php code I have all the information to insert into the tables except one thing , the row id primary key of the nomsing table. So that php generates a series of inserts like the following.

INSERT INTO nomsing(word,postress,gender) VALUES (''велосипед","8","mask").
INSERT INTO nomplu(word,postress,NOMSING?REFERENCE) VALUES (''велосипеды","2",@the reference to the id of the first insert@).

There are more inserts but this one gets the point across. The second insert should reference the auto generated id for the first insert. I was this to work as a transaction so all inserts should complete or none.

One idea I have is to not auto generate the id and generate it myself in php. That way would know the id given before the transaction but then I would have to check if the id was already in the db.

Another idea I have is to do the first insert and then query for the row id of that insert in php and then make the second insert. I mean both should work but they don't seem like an optimal solution. I am not too familiar with the database transactional features but what would be the best approach to do in this case. I don't like the idea of inserting then querying for the id and then running the rest of the queries. Just seems very inefficient or perhaps I am wrong.

Procasinator

MySQL has a last_insert_id function, that you could use in the SQL or call it from PHP (it is exposed in mysqli, PDO, etc).

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

http://php.net/manual/en/function.mysql-insert-id.php
http://www.php.net/manual/en/mysqli.insert-id.php
http://www.php.net/manual/en/pdo.lastinsertid.php

2ygb4cmqetsjhx

Just another question. If I have two php processes running doing this at the same time will the PDO cater for it.

I mean if PDO gets the last index but before it gets a chacne another thread does an insert so it will get the wrong value. Is this possible. Should I add locks to the table. I will test it outmyself later but just thought you might know. I am at work you see and cannot experiment yet?

Procasinator

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html wrote:

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

If you are using persistent connections, you might need to take more care.