Difference between revisions of "How to Get the Primary ID after a SQL Insert"
From Hostek.com Wiki
(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.
Contents
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>"; } ?>