Select Last_insert_id query...

[jim]

Hi,

I have a site with various parts which allow users to enter info via forms
which gets entered into various tables in a MySQL db (I'm using ASP, rather
than PHP).

I have an ecards bit, and when users send a card, their data is entered into
the db via something like:

INSERT vars into ecard_tbl....

I just read that I can use this to get the most recent id from the
autoincrement field in the table:

SELECT LAST_INSERT_ID

However, it doesn't seem to need a table name for that. What if exactly at
the same time another users enters something into my guestbook. That will
submit data to the same MySQL db, so won't the 'SELECT LAST_INSERT_ID' give
me that ID, and not the ID from the card submission?

Am I missing something very obvious here? I checked the MySQL docs but can't
see anything obvious.

Thanks

Jim

[Aggro]

jim wrote:

However, it doesn't seem to need a table name for that. What if exactly at
the same time another users enters something into my guestbook. That will
submit data to the same MySQL db, so won't the 'SELECT LAST_INSERT_ID' give
me that ID, and not the ID from the card submission?

Am I missing something very obvious here? I checked the MySQL docs but can't
see anything obvious.

"The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients."

http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html

[Rich R]

"Aggro" <sp**********@yahoo.com> wrote in message
news:fT**************@read3.inet.fi...

jim wrote:

However, it doesn't seem to need a table name for that. What if exactly at the same time another users enters something into my guestbook. That will submit data to the same MySQL db, so won't the 'SELECT LAST_INSERT_ID' give me that ID, and not the ID from the card submission?

Am I missing something very obvious here? I checked the MySQL docs but can't see anything obvious.

"The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients."

http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html

This is why you might want to SELECT max(ID) FROM table to get the lastest
one.

Rich

[Bill Karwin]

Rich R wrote:

This is why you might want to SELECT max(ID) FROM table to get the lastest
one.

I interpret jim's issue that he wants to fetch the last insert ID within
the context of his current connection, and ignore potential other ID's
inserted within other connections.

The LAST_INSERT_ID() function works correctly in this case, because it
is limited to the scope of the current connection. The function returns
the same value when you call it soon after your last insert or a long
time after your last insert, even if other connections insert more
records in the meantime.

Regards,
Bill K.

[Rich R]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cq*********@enews2.newsguy.com...

Rich R wrote:

This is why you might want to SELECT max(ID) FROM table to get the lastest one.

I interpret jim's issue that he wants to fetch the last insert ID within
the context of his current connection, and ignore potential other ID's
inserted within other connections.

The LAST_INSERT_ID() function works correctly in this case, because it
is limited to the scope of the current connection. The function returns
the same value when you call it soon after your last insert or a long
time after your last insert, even if other connections insert more
records in the meantime.

Regards,
Bill K.

Then maybe I am mistaken, too. I created two tables each having an
auto_increment columm. I did an insert into the first and LAST_INSERT_ID() =
1. I do an insert into the second table and the LAST_INSERT_ID() = 1. I do
another insert into the first table and LAST_INSERT_ID() = 2. So I've lost
the last inserted value from the second table, which is still 1. These
inserts were done on the same connection, one right after another. . So the
only way I can get the last inserted value from the second table is to do a
max() on the column. Am I out in the weeds on this one?

Best regards,
Rich

[Bill Karwin]

Rich R wrote:

Then maybe I am mistaken, too. I created two tables each having an
auto_increment columm. I did an insert into the first and LAST_INSERT_ID() =
1. I do an insert into the second table and the LAST_INSERT_ID() = 1. I do
another insert into the first table and LAST_INSERT_ID() = 2. So I've lost
the last inserted value from the second table, which is still 1. These
inserts were done on the same connection, one right after another. . So the
only way I can get the last inserted value from the second table is to do a
max() on the column. Am I out in the weeds on this one?

Yes, that is the way LAST_INSERT_ID() functions. It returns the last ID
that was auto-generated for any table within the current database
connection. If you do another insert to a table (either to the same
table or to a different table), then LAST_INSERT_ID() doesn't return the
previously generated ID value, it returns the more recent one.

However, that is not the issue that the original poster was asking
about. He was asking about the case where a second connection to the
database does its own insert, and causes another ID to be generated.
LAST_INSERT_ID() returns different values in this case, regardless of
the sequence of inserts. In each connection, the function returns the
most recent ID auto-generated by an insert within that respective
connection.

LAST_INSERT_ID() handles the case that jim was posting about. Your two
posts so far have described different issues, unrelated to jim's posting.

Regards,
Bill K.

[Rich R]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cq*********@enews4.newsguy.com...

Rich R wrote:

Then maybe I am mistaken, too. I created two tables each having an
auto_increment columm. I did an insert into the first and LAST_INSERT_ID() = 1. I do an insert into the second table and the LAST_INSERT_ID() = 1. I do another insert into the first table and LAST_INSERT_ID() = 2. So I've lost the last inserted value from the second table, which is still 1. These
inserts were done on the same connection, one right after another. . So the only way I can get the last inserted value from the second table is to do a max() on the column. Am I out in the weeds on this one?

Yes, that is the way LAST_INSERT_ID() functions. It returns the last ID
that was auto-generated for any table within the current database
connection. If you do another insert to a table (either to the same
table or to a different table), then LAST_INSERT_ID() doesn't return the
previously generated ID value, it returns the more recent one.

However, that is not the issue that the original poster was asking
about. He was asking about the case where a second connection to the
database does its own insert, and causes another ID to be generated.
LAST_INSERT_ID() returns different values in this case, regardless of
the sequence of inserts. In each connection, the function returns the
most recent ID auto-generated by an insert within that respective
connection.

LAST_INSERT_ID() handles the case that jim was posting about. Your two
posts so far have described different issues, unrelated to jim's posting.

Regards,
Bill K.

Hi Bill,

I think I addressed the post correctly. Doesn't matter if it's one
connection or many. If you want the last ID assigned you must do a Max(ID).
Within 1 connection, auto_increment will change not based on tables, simply
adding 1 to the last one. So if I want the last auto_incremented EmployeeID
and I have done some inserts to Projects with an ID ->auto_increment, all
bets are off, even safer, there are no bets.

If the OP did this all inside a transaction with InnoDB, then I still
wouldn't trust it.

Regards,

Rich

Regards,
Rich