Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

Can you retrieve the value of an auto-increment column during MySQL insert?

Experts,

If I have a table which has 3 columns (ID, firstname, lastname), where 'ID' is an auto-increment data type is there any way to retrieve the auto-increment value during row creation?

Considering the following statement:

INSERT INTO table (firstname, lastname) VALUES ('Bob', 'Roberts');

Can I find out what the value of 'ID' is when the statement above is executed?

Cheers!


Is there any way to get th
0
evibesmusic
Asked:
evibesmusic
4 Solutions
 
PortletPaulCommented:
Please read:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

mysql> SELECT LAST_INSERT_ID();
        -> 195
0
 
evibesmusicAuthor Commented:
@PortletPaul:

Thank you.

So using PHP I would assume that the following would place the last auto-increment value into my desired session variable?

$sql = "INSERT INTO table (firstname, lastname) VALUES ('Bob', 'Roberts')";
$query = mysql_query($sql) or die ("Could not run query: " . $sql . "<br />\n" . mysql_error () );
$last_id = mysql_insert_id();
$_SESSION['last_id'] = $last_id;
0
 
PortletPaulCommented:
Regret to say I don't use PHP so I cannot verify that.

& as I'm not a PHP person I would wonder:
What guarantees that no other sql is being executed between $query and $last_id? (assuming multi-user access)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
evibesmusicAuthor Commented:
@PortletPaul:

I understand the concern.

I am using the InnoDB DB type so I know that row level locking is supported but, that doesn't necessarily prevent a dirty read I guess?

This may not be a solid programming practice but, I'd still like to know if my code above would work?
0
 
Dave BaldwinFixer of ProblemsCommented:
Your code will work in simple situations.  It can't be guaranteed all the time though.  PHP info:  http://php.net/manual/en/function.mysql-insert-id.php
0
 
Chris StanyonCommented:
Whilst your suggested code will work, you may want to think about updating it to use the mySQLi or PDO extension - the mySQL extension is deprecated in PHP and will be removed in the future. If your host updates PHP without telling you, then you entire code will simply stop working - see the big red warning at the top of the page that Dave linked to!!

Here are the links to the pages relating to mySQLi and PDO:

http://php.net/manual/en/mysqli.insert-id.php
http://php.net/manual/en/pdo.lastinsertid.php
0
 
Ray PaseurCommented:
The insert-id and many other questions are addressed here.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Regarding the "race condition" between the query and the PHP instructions to retrieve the last insert id, there is no risk of an error in almost any reasonable PHP installation.  The last insert id is determined on a per-connection basis, and a PHP script usually has one connection that is not shared with other PHP scripts.  Sometimes a PHP script can have more than one connection or no connections at all, but a PHP script cannot simply choose to use another script's connection.  It's nearly impossible (not to mention structurally foolish) to have two separate PHP scripts sharing the same database connection.  In practice over the last 15 years of PHP and MySQL programming, I have never seen the last-insert-id functions return an incorrect id.  So I think that the last-insert-id functions are safe, if used as described in the man pages.
0
 
evibesmusicAuthor Commented:
@All:

Thank you for your answers.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now