|
P: n/a
|
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
| |
Share this Question
|
P: n/a
|
On Jul 26, 2:35*pm, KDawg44 <KDaw...@gmail.comwrote:
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 | |
|
P: n/a
|
On Jul 26, 2:47*pm, KDawg44 <KDaw...@gmail.comwrote:
On Jul 26, 2:35*pm, KDawg44 <KDaw...@gmail.comwrote:
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 | |
|
P: n/a
|
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 | |
|
P: n/a
|
KDawg44 wrote:
On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail.comwrote:
>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. | |
|
P: n/a
|
KDawg44 wrote:
On Jul 26, 2:47 pm, KDawg44 <KDaw...@gmail.comwrote:
>On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail.comwrote:
>>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.
*********************************** | |
|
P: n/a
|
..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 | |
|
P: n/a
|
On Jul 26, 7:47 pm, KDawg44 <KDaw...@gmail.comwrote:
On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail.comwrote:
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. | |
|
P: n/a
|
Hello,
Gordon wrote:
On Jul 26, 7:47 pm, KDawg44 <KDaw...@gmail.comwrote:
>On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail.comwrote:
>>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 | |
|
P: n/a
|
Gordon wrote:
On Jul 26, 7:47 pm, KDawg44 <KDaw...@gmail.comwrote:
>On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail.comwrote:
>>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
================== | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 1420
- replies: 9
- date asked: Jul 26 '08
|
