Retrieving an Inserted Value on an Insert Command?

Celeritas

I'm sorry if there is already a thread asking this question, I searched but wasn't really sure of what keywords to use. Anyway, this is my question.

What I need to do is somehow retrieve an automatically incremented primary key from a row that I just inserted. So something like, if a user just posted a comment, the PHP script could tell them that, "Your comment's ID is $comment_id" (Made up situation).

Id there a way to do this? I've gone through multiple ideas, first of all counting the number of existing rows, but if I had deleted a row for whatever reason it would return the false id, and then I'd have to make a "deleted" field, which would just make things mre complicated than I need. The other one was to randomly generate a code for the inserted row, and then do another query which would retrieve the ID based on that code, but that just make another necessary query, and seems like a horrible way to build code.

So there must be some way to return that variable. Help?

Weedpacket

Assuming MySQL (due to the description of the primary key)....[man]mysql_insert_id[/man].

Inkeye

Please tell us what database you are using. There are a couple of ways I can think of to do it, depending on which DB.

There is also a cool way to do it with MSSQL.

Sxooter

In PostgreSQL you have returning:

insert into table abc (a,b) values (1,'gabbagoo') returning a,c;

which can return fields that have been updated by a trigger, and can return sets. I.e. this works:

smarlowe=# create table mytable (a int, b int);
CREATE TABLE
smarlowe=# insert into mytable values (1,2),(3,4),(40,50) returning a,b;
a | b

----+----
1 | 2
3 | 4
40 | 50
(3 rows)

tada!

Just one of the many many reasons I use pgsql.

Niyati_1984

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>

Sxooter

FYI, if you insert multiple records, MySQL returns the id of the first, not last record. So, if something like

insert into table (field2, field3) values (1,2),(3,4);

returns an insert if of 4, that would be the first pair of values inserted, the second would have 5.