How to Get the Primary ID after a SQL Insert

Often times you need to get the ID of a new record that was just added to a database. This wiki gives you the solution on How to Get the Primary ID after a SQL insert.

ColdFusion - How to Get the Primary ID after a SQL Insert

 <CFQUERY NAME="MyInsert" DATASOURCE="#MyDataSource#">
 SET NOCOUNT ON
 INSERT INTO MyTable (name,email)
 VALUES ('#fname#','#email#')
 SELECT @@identity AS MyNewID
 SET NOCOUNT OFF
 </CFQUERY>
 <CFOUTPUT>#MyInsert.MyNewID#</CFOUTPUT>

Note: Notice the NOCOUNT lines. This gives us data integrity so that we get the proper ID, even if another record was added during this time.

PHP - How to Get the Primary ID after a SQL Insert

Using legacy mysql extension

<?php
mysql_query("INSERT INTO cities (city) values ('Austin')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>

Using mysqli

Object oriented style
<?php
$query = "INSERT INTO myCity VALUES ('Austin', 'TX')";
$mysqli->query($query);
 
printf ("New Record has id %d.\n", $mysqli->insert_id);
 
?>

Procedural style

<?php
$query = "INSERT INTO cities VALUES ('Austin', 'TX')";
mysqli_query($link, $query);
 
printf ("New Record has id %d.\n", mysqli_insert_id($link));
?>

Using PDO

<?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>"; 
} 
?>