Solved

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

Posted on 2015-02-07
8
603 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 109

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
The viewer will learn how to dynamically set the form action using jQuery.

778 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