View source for How to Get the Primary ID after a SQL Insert

__FORCETOC__

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==
<syntaxhighlight lang="cfm">
 <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>
</syntaxhighlight>
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===
<syntaxhighlight lang="php">
<?php
mysql_query("INSERT INTO cities (city) values ('Austin')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>
</syntaxhighlight>
===Using mysqli ===
<syntaxhighlight lang="php">
Object oriented style
<?php
$query = "INSERT INTO myCity VALUES ('Austin', 'TX')";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

?>
</syntaxhighlight>
Procedural style
<syntaxhighlight lang="php">
<?php
$query = "INSERT INTO cities VALUES ('Austin', 'TX')";
mysqli_query($link, $query);

printf ("New Record has id %d.\n", mysqli_insert_id($link));
?>
</syntaxhighlight>

===Using PDO ===
<syntaxhighlight lang="php">
<?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>"; 
} 
?>
</syntaxhighlight>