How to get an autoincremented ID immediately after inserting a new record

Question

My script takes a CSV file containing people's details and inputs them into my mySQL database table using "INSERT INTO table". The primary key of the table is their ID number which is auto-incremented on each insertion.

I will have some PHP after each insertion to make a directory for them on the server. The directory name is their auto generated mySQL ID number.

How could I get their auto-generated ID number after inserting their details in to the database so I can make the directory for each person?

By inserting the data one row at a time and grabbing last insert id? — Salman A, Oct 13 '14 at 14:16
How about `mysqli::$insert_id` -- mysqli_insert_id — Returns the auto generated id used in the last query? Would this do what you need? — Max, Oct 13 '14 at 14:16

score 1 · Answer 1 · edited Oct 15 '14 at 10:38

1

PDO:

$last_id = $db->fetchAll('SELECT LAST_INSERT_ID() as last_id');  
$last_id = intval($last_id[0]['last_id']);

OR

$lastId = $db->lastInsertId();

edited Oct 15 '14 at 10:38

John Ruddell

25,283
6
57
86

answered Oct 13 '14 at 14:23

Maximilian Prepl

402
4
10

dont answer this question... there is already an exact way to do it (a few minutes after I marked it as a duplicate)... a duplicated question only clutters the community.. its much better to just close the question and if anyone finds this question they will be redirected to the other post... – John Ruddell Oct 13 '14 at 14:24
this is a very genious way using SQL to get last id. – Mimouni Oct 13 '14 at 14:34
@IlyasMimouni which was already used [HERE](http://stackoverflow.com/questions/10680943/pdo-get-the-last-id-inserted) – John Ruddell Oct 13 '14 at 14:35
@JohnRuddell, well, i opened question and went out :D came back and answered. i didn't saw your mark... – Maximilian Prepl Oct 13 '14 at 14:37
@IlyasMimouni for pdo i remember just this, for mysqli are more ways... – Maximilian Prepl Oct 13 '14 at 14:37

score 1 · Answer 2 · edited Oct 15 '14 at 10:38

here is a complete example with rollback:

<?php 
try { 
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'password'); 

    $stmt = $dbh->prepare("INSERT INTO test (name, email) VALUES(?,?)"); 

    try { 
        $dbh->beginTransaction(); 
        $tmt->execute( array('user', 'user@example.com')); 
        $dbh->commit(); 
        print $dbh->lastInsertId(); 
    } catch(PDOExecption $e) { 
        $dbh->rollback(); 
        print "Error!: " . $e->getMessage() . "</br>"; 
    } 
} catch( PDOExecption $e ) { 
    print "Error!: " . $e->getMessage() . "</br>"; 
} 
?>

we use PDO to communicate with database, and to know to last id : $dbh->lastInsertId()

Honesta · Answer 3 · 2014-10-13T14:24:20.983

-5

You may use the mysqli_insert_id function.

docs here.

edited Oct 13 '14 at 14:24

answered Oct 13 '14 at 14:18

Honesta

1,394
1
11
20

4

you should NEVER tell someone to use **mysql_*** as its deprecated.. also the OP is using PDO so this technology wouldn't even work – John Ruddell Oct 13 '14 at 14:19
1

@JohnRuddell It's "it's" – Strawberry Oct 15 '14 at 11:15
@Strawberry I smell a grammar nazi :P – John Ruddell Oct 15 '14 at 11:20
I know, I know - but I can't help myself. – Strawberry Oct 15 '14 at 11:49

How to get an autoincremented ID immediately after inserting a new record

3 Answers3

OR