Until sometime back, I was unaware of how I could get the value of an auto-incremented field after an Insert Statement. But recently I needed it for two of my projects simultaneously, so I explored the possibilities. As one of my project is in Java and other is in PHP, we will go through both of them.
So, here is the problem. In general, the PRIMARY KEY field is the AUTO_INCREMENT field. Now wen you insert a row, a new key is generated and you can’t get it through usual way as this row can be accessed only using the primary key.
So here is how it can be done:
In JAVA:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /*Insert a row into the desired table that generates an auto increment field*/ stmt.executeUpdate("Insert into tableName (col1, col2) values (val1, val2)", Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys(); int autoIncValue = -1; if(rs.next()) { autoIncValue = rs.getInt(1); /*You can get more generated keys if they are generated in your code*/ } |
In PHP:
The mysql_insert_id() function in PHP returns the AUTO_INCREMENT ID generated from the previous INSERT operation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <?php $con = mysql_connect("hostname", "username", "password"); if (!$con) { die('Error while connecting: ' . mysql_error()); } $db_selected = mysql_select_db(database_name,$con); $sql = "INSERT INTO tablename VALUES (val1,val2)"; $result = mysql_query($sql,$con); echo "ID of last inserted record is: " . mysql_insert_id(); mysql_close($con); ?> |
Note: Be sure to call mysql_insert_id() immediately after a query to get the correct value. The function returns 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.
Enjoy!!
Specific to Drupal, http://api.drupal.org/api/function/db_last_insert_id/6 (or its equivalents) will let you do this is a cross-DB compatible way.