Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-07
8
Medium Priority
?
673 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 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 49

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 500 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 44

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 500 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 111

Accepted Solution

by:
Ray Paseur earned 500 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…

705 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