Get last auto increment value from insert into?

Question

My situation is I am inserting a new row into table users where user_id is the primary key and auto incremented. I need the value of that auto inc'ed user_id key to use in another insert for a separate table to set default settings for the newly added user.

Is there a way to get the values of an insert after it has happened... in this case whatever default value user_id ends up being since it is auto inc'ed? Or... will I be forced to do a select right after this insert to find its value?

//insert the user
$stmt = $db->prepare("
    INSERT INTO users (
            computer_id,
            username,
            timestamp
        )
        VALUES (
            :computer_id,
            :username,
            :timestamp
       )
");

//bindings
$binding = array(
    'computer_id' => $result['computer_id'],
    'username' => $_POST['username'],
    'timestamp' => time()
);

$added_user = $stmt->execute($binding);

//successfully added the user
if($added_user)
{
    //add defaults for this user
    $stmt = $db->prepare("
        INSERT INTO defaults (
                user_id,
                default1,
                default2
            )
            VALUES (
                :user_id,
                :default1,
                :default2
           )
    ");

    //bindings
    $binding = array(
        'user_id' => **need this value from above insert**
        'default1' => $default1,
        'default2' => $default2
    );

    $status = $stmt->execute($binding);
}

score 2 · Accepted Answer · answered Jul 03 '14 at 12:25

2

You can get the id of the last inserted row by calling :

$id = $db->lastInsertId();

http://www.php.net/manual/fr/pdo.lastinsertid.php

answered Jul 03 '14 at 12:25

Erxyon

251
2
4

This will be a very high traffic site... is it possible this value can be wrong or is this 'connection specific'? In other words, it doesn't just return the last id, but the last id for this specific connection to the db? – user756659 Jul 03 '14 at 17:38
This value is connection specific so there will be no problems. See https://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html (4th paragraph) – Erxyon Jul 04 '14 at 08:21

score 1 · Answer 2 · answered Jul 03 '14 at 12:25

1

this depends on your db extension:

for mysql: http://php.net/manual/en/function.mysql-insert-id.php
for mysqli: http://php.net/manual/en/mysqli.insert-id.php
for pdo: http://www.php.net/manual/en/pdo.lastinsertid.php

answered Jul 03 '14 at 12:25

Kevin

1,232
10
28

Get last auto increment value from insert into?

2 Answers2