Friday, January 25, 2013

Getting AutoIncrement value in PHP-MySQL


It is more often than not a requirement for developers to get the value of the 'id' field back after the insert operation. PHP-MySQL has a couple of ways to do this.

The first way: use "mysql_insert_id()"
Example:

$query = "INSERT INTO tableA (id,user_id,field2, field3) VALUES (NULL, '$user_id','$field2_id','$field3_id')";
$result = mysql_query($query);
$insert_id = mysql_insert_id();

The second way: use "LAST_INSERT_ID()"
This is more useful when you have to insert the data into some other table in the same session.
Example:
INSERT INTO TABLEA (ID, FIELD1) VALUES(NULL, 'some value');
 
INSERT INTO TABLEB (ID, NAME, TABLEA_ID)
VALUES (NULL, 'Pramod Rao', LAST_INSERT_ID());

Hope this helps.

N.B: Both cases are unique for the session, so there should not be any issues. But if you have multiple threads, then you might have concurrency issues.

No comments :