Difference between revisions of "How to Get the Primary ID after a SQL Insert"

From Hostek.com Wiki
Jump to: navigation, search
(Created page with "__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 ...")
 
 
Line 4: Line 4:
  
 
==ColdFusion - 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#">
 
  <CFQUERY NAME="MyInsert" DATASOURCE="#MyDataSource#">
 
  SET NOCOUNT ON
 
  SET NOCOUNT ON
Line 13: Line 13:
 
  </CFQUERY>
 
  </CFQUERY>
 
  <CFOUTPUT>#MyInsert.MyNewID#</CFOUTPUT>
 
  <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.
 
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>

Latest revision as of 17:33, 6 May 2014


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