Solved

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

Posted on 2015-02-07
8
591 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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
 

Author Comment

by:evibesmusic
Comment Utility
@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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 125 total points
Comment Utility
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 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 125 total points
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 125 total points
Comment Utility
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
Comment Utility
@All:

Thank you for your answers.
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.​
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now