[RESOLVED] Get the PK of a auto increment value you just inserted

[Zeratulsdomain]

right now my program inserts a new record and a PK of int is automatically created. But I also need a second query to insert data into another table that has a 1:1 relationship.

Is it possible to somehow get the auto increment value that was created from my insert statement? Or should I simple do another select query to get the latest entry inserted?

I guess for now ill use

SELECT max(ID) FROM tableName

[si_the_geek]

There are a few methods explained in the article How can I get the AutoNumber/Identity value for the record just added? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[DigiRev]

I've always gotten it before the query.

$r = mysql_query('SHOW TABLE STATUS LIKE "table_name"') or die(mysql_error());
$d = mysql_fetch_array($r, MYSQL_ASSOC);
$next_id = $d['Auto_increment'];
unset($d);
mysql_free_result($r); 

I'm by no means an expert at PHP so take this with a grain of salt...

[penagate]

Use the mysql_insert_id function to retrieve the auto_increment ID generated by the last INSERT operation.
Because this function is connection-specific, there are no concurrency issues.


The SELECT MAX() and SHOW TABLE STATUS methods are both fallible: both methods require two queries and are thus susceptible to concurrency issues. (What if someone inserts a row while you're busy working out the next ID?)

Further, the maximum value of a column is not necessarily the next auto_increment value. MAX() might return one value, but you could set another by using ALTER TABLE table_name AUTO_INCREMENT=x.


If you need to insert into multiple tables at once, I would use a stored procedure. This minimises traffic between the web and the database server. For a non-trivial project, I would also suggest ditching the standard php_mysql library and using a more advanced data access library such as PDO (PHP 5, abstracted) or MDB2 (PHP 4/5, abstracted) or mysqli (PHP 4/5, non-abstracted). These support useful features such as parameters and prepared statements.

[Zeratulsdomain]

Use the mysql_insert_id function to retrieve the auto_increment ID generated by the last INSERT operation.
Because this function is connection-specific, there are no concurrency issues.


The SELECT MAX() and SHOW TABLE STATUS methods are both fallible: both methods require two queries and are thus susceptible to concurrency issues. (What if someone inserts a row while you're busy working out the next ID?)

Further, the maximum value of a column is not necessarily the next auto_increment value. MAX() might return one value, but you could set another by using ALTER TABLE table_name AUTO_INCREMENT=x.


If you need to insert into multiple tables at once, I would use a stored procedure. This minimises traffic between the web and the database server. For a non-trivial project, I would also suggest ditching the standard php_mysql library and using a more advanced data access library such as PDO (PHP 5, abstracted) or MDB2 (PHP 4/5, abstracted) or mysqli (PHP 4/5, non-abstracted). These support useful features such as parameters and prepared statements.

mysql_insert_id works, ill use that, tnx.

As for my last method, I am aware of the potential problem hence why I made this thread. I figured there was about a < 0.001% chance of it happening in this situation but I also wanted to know the proper way of doing it.