Getting an Auto_Incremented ID from MySQL

[KDawg44]

Hi,

I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.

Thanks.

Kevin

[KDawg44]

On Jul 26, 2:35*pm, KDawg44 <[e-mail]:

Hi,

I have PHP function that adds a record to the database. *The table has
an ID that is AUTO_INCREMENT. *Is there anyway to get that ID back
when I do any kind of insert? *That ID is a foreign key that I need to
do an insert on afterwards.

Thanks.

Kevin

I found this documentation at http://dev.mysql.com/doc/refman/5.0/...unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo

Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2

So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.

So what are the chances I can have the problem described above if I
did something like:

INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID()
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID

Thanks.

Kevin

[KDawg44]

On Jul 26, 2:47*pm, KDawg44 <[e-mail]:

On Jul 26, 2:35*pm, KDawg44 <[e-mail]:

Hi,

I have PHP function that adds a record to the database. *The table has
an ID that is AUTO_INCREMENT. *Is there anyway to get that ID back
when I do any kind of insert? *That ID is a foreign key that I need to
do an insert on afterwards.

Thanks.

Kevin

I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

Table 1
ItemID *ItemName ItemDesc
1 * * * *Foo * * *Bar
2 * * * *Bar * * *Foo

Table 2
ID * * *ValueName * * Value * Table1ID
1 * * * * *A * * * * * *5 * * * * 1
2 * * * * *B * * * * * *5 * * * * 1
3 * * * * *A * * * * * *5 * * * * 2
4 * * * * *C * * * * * *5 * * * * 1
5 * * * * *B * * * * * *5 * * * * 2

So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). *It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.

So what are the chances I can have the problem described above if I
did something like:

INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID()
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID

Thanks.

Kevin

Well, I see there is a mysql_insert_id() PHP function that returns
what I need and I am assuming that since i doing this immediately
after the INSERT statement that chances of another insert occurring in
between (especially since this is the only function inserting into
these two tables) is pretty slim.

Anyone seeing please faults in my logic, please let me know.

Thanks.

Kevin

[Geoff Berrow]

Message-ID:
<c2**********************************@r66g2000hsg. googlegroups.comfrom
KDawg44 contained the following:

>I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.

If it's MySql there is this http://uk.php.net/mysql_insert_id
--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011
http://slipperyhill.co.uk

[Michael Austin]

KDawg44 wrote:

On Jul 26, 2:35 pm, KDawg44 <[e-mail]:

>Hi,

I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.

Thanks.

Kevin

I found this documentation at http://dev.mysql.com/doc/refman/5.0/...unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo

Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2

So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.

So what are the chances I can have the problem described above if I
did something like:

INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID()
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID

Thanks.

Kevin

If I need to do multiple inserts into multiple tables using a web
connection, I tend to incorporate all of the respective inserts for the
logical transaction into a stored procedure where I guarantee that the
connection being used for the subsequent inserts are the same as the
connection for the initial connection and therefore guarantee that
last_id is truly the last_id this connection executed. I do this to
ensure that the complete transaction is autonomous - and if the complete
transaction fails - it all gets rolled back.

There are many schools of thought on this, this is just how I prefer to
execute this type of transaction. YMMV.

[Chuck Anderson]

KDawg44 wrote:

On Jul 26, 2:47 pm, KDawg44 <[e-mail]:

>On Jul 26, 2:35 pm, KDawg44 <[e-mail]:

>>Hi,

I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.

Thanks.

Kevin

I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo

Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2

So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.

So what are the chances I can have the problem described above if I
did something like:

INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID()
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID

Thanks.

Kevin

Well, I see there is a mysql_insert_id() PHP function that returns
what I need and I am assuming that since i doing this immediately
after the INSERT statement that chances of another insert occurring in
between (especially since this is the only function inserting into
these two tables) is pretty slim.

Anyone seeing please faults in my logic, please let me know.

Thanks.

Kevin

Read the notes here:
http://dev.mysql.com/doc/refman/5.0/...last-insert-id

"The ID that was generated is maintained in the server on a
/per-connection basis/. ....."

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

[Michael Fesser]

..oO(KDawg44)

>I found this documentation at http://dev.mysql.com/doc/refman/5.0/...unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

LAST_INSERT_ID() is connection-specific. It will return the correct
value, even if multiple users insert records at the same time.

You might however get wrong results if you would do it like this:

INSERT INTO foo ... -- insert new record
SELECT MAX(id) FROM foo -- get highest ID

In this case you have a nice race condition and might run into trouble
with multiple parallel inserts. But if you do it the right way with
LAST_INSERT_ID() or its PHP counterpart you'll be safe.

Micha

[Gordon]

On Jul 26, 7:47 pm, KDawg44 <[e-mail]:

On Jul 26, 2:35 pm, KDawg44 <[e-mail]:

Hi,

I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.

Thanks.

Kevin

I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo

Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2

So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.

So what are the chances I can have the problem described above if I
did something like:

INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID()
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID

Thanks.

Kevin

This is a classic programming problem, known as a race condition. In
the database world, the mechanism for working around race conditions
is the ACID transaction. Unfortunately, the default MySQL table type
(myISAM) doesn't support them. If you are using InnoDB tables, then
you can use BEGIN TRANSACTION at the beginning of your operation and
COMMIT at the end to make absolutely sure that you get the correct
insert ID back.

[Jensen Somers]

Hello,

Gordon wrote:

On Jul 26, 7:47 pm, KDawg44 <[e-mail]:

>On Jul 26, 2:35 pm, KDawg44 <[e-mail]:

>>Hi,
I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.
Thanks.
Kevin

Skipping the other comments that were made I assume you only need the
PHP/MySQL function "mysql_insert_id()". It retrieves the ID generated
for an AUTO_INCREMENT column by the previous INSERT query. Also check
out the notes manual page as they might have an answer to your race
condition problem.

>I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo

Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2

So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.

So what are the chances I can have the problem described above if I
did something like:

INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID()
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID

Thanks.

Kevin

This is a classic programming problem, known as a race condition. In
the database world, the mechanism for working around race conditions
is the ACID transaction. Unfortunately, the default MySQL table type
(myISAM) doesn't support them. If you are using InnoDB tables, then
you can use BEGIN TRANSACTION at the beginning of your operation and
COMMIT at the end to make absolutely sure that you get the correct
insert ID back.

- Jensen

[Jerry Stuckle]

Gordon wrote:

On Jul 26, 7:47 pm, KDawg44 <[e-mail]:

>On Jul 26, 2:35 pm, KDawg44 <[e-mail]:

>>Hi,
I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.
Thanks.
Kevin

I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

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

but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:

USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID()

What happens is that the first table creates a record and the second
table records various values for that record.

Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo

Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2

So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.

So what are the chances I can have the problem described above if I
did something like:

INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID()
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID

Thanks.

Kevin

This is a classic programming problem, known as a race condition. In
the database world, the mechanism for working around race conditions
is the ACID transaction. Unfortunately, the default MySQL table type
(myISAM) doesn't support them. If you are using InnoDB tables, then
you can use BEGIN TRANSACTION at the beginning of your operation and
COMMIT at the end to make absolutely sure that you get the correct
insert ID back.

Not at all. mysql_last_insert_id() or LAST_INSERT_ID() is connection
specific. It will never get the id of an insert from another
connection. Transactions are not necessary to get the correct insert id.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================