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

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