Best way to get key from prior INSERT

glowball

Hi all, I couldn't find this in the forum though I'm sure it's been asked before. I need to get a primary key (ID) from one INSERT to use in the next INSERT. What's the best and most efficient way to do this?

For example:

INSERT INTO one_table (one_field) VALUES ($one_value);

INSERT INTO two_table (two_field, two_field_str) VALUES (????, $two_field_str);

The ???? needs to be the primary key (auto_increment) from the INSERT before into one_table.

I'm currently adding it, then doing a SELECT to get the value based on a particular field, in DESC order. I'm guessing there is a better way. Help?

Anon

Try:

SELECT LAST_INSERT_ID() FROM one_table;

Anon

Hi Glowball,

The best way to do it is that :

e.g

INSERT INTO one_table (one_field) VALUES ($one_value);

// Store the auto generated id using mysql_insert_id() function, its a very cool function which return the id generated form the insert statement used above it.

$id = mysql_insert_id();

INSERT INTO two_table (two_field, two_field_str) VALUES ($id, $two_field_str);

Hope it will work for you. Enjoy the code.

glowball

That is seriously cool -- thanks!

Anon

Yep, mysql_insert_id() is cool. But it's also in the manual, right there with all the others in the PHP manual. People really ought to browse that cover sheet (http://www.php.net/manual/en/ref.mysql.php) whenever they have a mysql-related question.