New Primary Keys MySQL/PHP

[stlewis]

I need to insert new parent and child records in two tables in MySQL. So I will need to retrieve the primary key of the parent so I can put it in the children as the foreign key.

Now the clever way would be if MySQL fed back the incremented number from the SQL Insert. Please can anyone confirm that it *can't* do this?

Assuming it can't, one solution is to generate a random number and include this in the Insert. Then do a Select for the parent, and retrieve the new key. One book I have, has an example where the Select is on name, address, zip etc. Is that really as naff as it looks?!

Any views? Thanks.

www.thelondonhouse.co.uk

[stlewis]

I need to insert new parent and child records in two tables in MySQL. So I will need to retrieve the primary key of the parent so I can put it in the children as the foreign key.

Now the clever way would be if MySQL fed back the incremented number from the SQL Insert. Please can anyone confirm that it *can't* do this?

Assuming it can't, one solution is to generate a random number and include this in the Insert. Then do a Select for the parent, and retrieve the new key. One book I have, has an example where the Select is on name, address, zip etc. Is that really as naff as it looks?!

Any views? Thanks.

www.thelondonhouse.co.uk

[williama]

You can get the used AUTO_INCREMENT key with the LAST_INSERT_ID() SQL function or the mysql_insert_id() API function.

MySQL Documentation Page

[stlewis]

Thanks, William. I just hope it is tied to the calling user - and not to the latest Insert

'Teach Yourself...' has it, but 'PHP and MySQL Development' doesn't.

I'll give it a try. Thanks again.

www.thelondonhouse.co.uk