Getting Last Insert ID with PHP and MSSQL
I wrote about getting the last insert ID before, and gave examples for using PHP’s mysql_insert_id function and an ASP alternative that used MSSQL’s @@IDENTITY function.
Using PHP, the solution is pretty easy but required some tinkering because you don’t need to SET NOCOUNT on and off, PHP’s mssql functions seem to want to return only the last recordset anyways. So:
$q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT LAST_INSERT_ID=@@IDENTITY");
$r = mssql_fetch_assoc($q);
Beef up the above code with your preferred abstraction layer or error handling code and $r['LAST_INSERT_ID'] will have the last insert id for you.
Here’s my previous writeup about implementing this with ASP and MSSQL, ASP and MySQL, and PHP and MySQL:
MSSQL and ASP using vbScript:
This code assumes you have an ADODB.Connection object cn which is already connected to your database. For readability I concatenate different lines of the query into a single string and separate lines for SQL Server’s sake using vbCrLf.
Dim rs
Set rs = cn.Execute( _
"SET NOCOUNT ON" & vbCrLf & _
"INSERT INTO TableName(Column1,Column2) " & vbCrLf & _
"VALUES(Value1,Value2) " & vbCrLf & _
"SELECT LAST_INSERT_ID=@@IDENTITY " & vbCrLf & _
"SET NOCOUNT OFF")
Response.Write "Last Insert ID: " & rs("LAST_INSERT_ID")
rs.Close
Set rs = Nothing
A great explanation of this including the bizzare lore of why you have to dispable affected row counting in order to actually get the result of this double query is available in this article on sqlteam.com, which is a great resource for T-SQL development.
MySQL and ASP using vbScript
With MySQL you can execute an INSERT using cn.Execute and then as long as you’re using the same connection object, just using the LAST_INSERT_ID() statement will give you the last insert ID.
cn.Execute "INSERT INTO TableName(Column1,Column2) " & vbCrLf & _ "VALUES(Value1,Value2);" rs.Open "SELECT LAST_INSERT_ID();",cn,0,1 Response.Write "Last Insert ID: " & rs(0) rs.Close
MySQL and PHP
Just use the mysql_insert_id() function in PHP after you perform an insert query.
if (mysql_query("INSERT INTO TableName(Column1,Column2) " .
"VALUES(Value1,Value2);")) {
echo "Last Insert ID: " . mysql_insert_id($link); }