NYCPHP Meetup

[Jayesh Sheth]

Hi Matt,

In response to your question about getting the last id inserted into a
table (on a per connection basis), these documents may be relevant:

http://archives.postgresql.org/pgsql-general/2003-08/msg01699.php
http://www.postgresql.org/docs/7.3/static/functions-sequence.html

I have not used PostgreSQL myself yet, but as far as I can tell, these
documents may help. More specifically, it is the "currval" part you should
be looking at.

An excerpt (from the first link above):

"
Basicly, currval() gives the last id for that sequence in that session. So
other sessions does not break anything.

> The only safe methode would be to do a "select nextval(whatever)", and
> aply this number by "hand" to the insert, but that remove the
> possibility to make general code even more, but it will be safe.

It's not needed. The following works fine (if the tables exists of course)
and has no problems with concurrency:

INSERT INTO foo(id,x) VALUES (DEFAULT, 'value');
INSERT INTO bar(id,foo_ref) VALUES (DEFAULT, currval('foo_id_seq'));
"

As I found out from the new MySQL book from Apress, you can find out the
last inserted id for a particular connection only using the following
technique:

"
When a new AUTO_INCREMENT value has been generated, you can also obtain it
by executing a SELECT LAST_INSERT_ID() statement mysql_query() and
retrieving the value from the result set returned by the statement.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the
server on a per-connection basis. It will not be changed by another
client. It will not even be changed if you update another AUTO_INCREMENT
column with a non-magic value (that is, a value that is not NULL and not
0).

If you want to use the ID that was generated for one table and insert it
into a second table, you can use SQL statements like this:

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');              # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table
"
(More info on this at:
http://dev.mysql.com/doc/mysql/en/Getting_unique_ID.html
 )

Hope all that helps and is what you were looking for.

- Jay