Solved

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

Posted on 2015-02-07
8
623 Views
Last Modified: 2015-02-08
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
Comment
Question by:evibesmusic
8 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 40596517
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
 

Author Comment

by:evibesmusic
ID: 40596529
@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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40596533
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:evibesmusic
ID: 40596537
@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
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 125 total points
ID: 40596573
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
 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 125 total points
ID: 40596623
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
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 125 total points
ID: 40596879
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
 

Author Closing Comment

by:evibesmusic
ID: 40597297
@All:

Thank you for your answers.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question