473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting an Auto_Incremented ID from MySQL

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
Jul 26 '08 #1
9 1728
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
Jul 26 '08 #2
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
Jul 26 '08 #3
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
Jul 26 '08 #4
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.
Jul 26 '08 #5
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.
***********************************

Jul 27 '08 #6
..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
Jul 27 '08 #7
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.
Jul 28 '08 #8
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
Jul 28 '08 #9
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
==================

Jul 28 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: das dsf | last post by:
Hi there! I have both PHP 4.0( RPM) installed and MySQL 4.0.20 installed with the mysqld daemon up and running. But when I look at the output of phpinfo() , I do not see it there, which is...
2
by: Jason Reljac | last post by:
Howdy, I'm looking for a little help. I had a W2K server with Apache/PHP/mySQL installed and working. I had a specific page that ran a mySQL SELECT INTO OUTFILE and dumped the outfile into a...
6
by: Jeff Harbin | last post by:
I've spent the last 2 days trying to begin using mysql. I've purchased 3 books and read huge chunks of the documentation provided with the mysql zip file. I've downloaded...
0
by: Jason Reljac | last post by:
Howdy, I'm looking for a little help. I had a W2K server with Apache/PHP/mySQL installed and working. I had a specific page that ran a mySQL SELECT INTO OUTFILE and dumped the outfile into a...
4
by: Chefry | last post by:
I'm trying to set up an off the shelf script and keep getting an error. My host set up the mysql on my site and I changed the variables I had to in the settings.php file but I keep getting the...
6
by: Erik H. | last post by:
Trying to connect to MySQL db on localhost, and populate datagrid from a dataset using code inline method. Getting the following compile error: Error Message: "CS0246: The type or namespace...
3
by: Greg Noss | last post by:
I'm trying to install MySQL. During the security settings portion fo the setup. I keep getting a Connection Error. Error Nr. 1045 Access denied for user'root'@'localhost'(using password:YES) ...
3
by: h4xPace | last post by:
I am building a MySQL query application, and I have run into a small snag. MySQL has released a set of classes that extend the .NET framework base data classes (command, connection, etc), and I am...
3
by: ist | last post by:
Hi, I am trying to get (and transfer over ASP.NET) some encrypted data from some MySQL fields. Since the data contains many unicode characters, I tried to get the data as a series of ASCII...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.