Finding a newly-created key

[La_Parka]

Let's say that I have a script that inserts data into two tables in the same sequence. The first table adds an auto-incremented key, and the second table needs to somehow get the key number of the new row in table one.

In other words, key '54' was created in table one, and table two needs to get this number.

What's the best way to accomplish this?

[jaret]

using SQL? try..

After your insert statement for table1:

SELECT pri_key FROM table1 ORDER BY pri_key DESC LIMIT 1;

the returned pri_key will be the last recored entered into the system.

[jkurrle]

mysql_insert_id()

[johanafm]

And wrap it all up in a transaction. You don't want to risk having the following senario:
You insert into table1: primary key = 5
Someone else inserts into table1: primary key = 6
You retrieve key from table1: 6
You insert data into table2 with foreign key: 6

begin transaction;
insert data in table1
select primary key value as shown above
insert data in table2

if you encounter an error along the way:
rollback

otherwise:
commit

result with above scenario:
begin transaction
You insert into table1: primary key = 5
(this is not in your transaction and so you won't be affected by it until you commit. you retain the same database state as you had upon begin transaction, except for the changes you make yourself) Someone else inserts into table1: primary key = 6
You retrieve key from table1: 5
You insert data into table2 with foreign key: 5