Re: Getting an Auto_Incremented ID from MySQL

KDawg44 wrote:
On Jul 26, 2:35 pm, KDawg44 <KDaw...@xxxxxxxxx> wrote:
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/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


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.
.

Relevant Pages

  • Re: Getting an Auto_Incremented ID from MySQL
    ... I have PHP function that adds a record to the database. ... loops through the values array, ... is the ACID transaction. ...
    (comp.lang.php)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Setting Locks on Rows in MSSQL Database (Currenlty Unsolvable Problem and Trying)
    ... I am beginning a transaction from PHP and setting a lock on a record ... I then commit the transaction I started when the user clicked ... There is no way in PHP where I can start a persistent ms sql connection ...
    (alt.php)
  • TransactionScope Transactions not commiting
    ... be) using the same SQL Connection. ... and dtermines the Connection String to use to create the connection to ... MyClass obj = new MyClass; ... not involved in a Transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, I need to install the path sqljdbc_xa.dll and xa_install.sql before using the XA driver. ... I created a TX aware datasource using com.microsoft.sqlserver.jdbc.SQLServerDriver and tried to get the connection from the TX aware data source. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)