SQL Server Driver for PHP: Last Inserted Row ID
After a mysql_query with an INSERT statement, a quick call to mysql_insert_id provides the ID of the last inserted row.
$lastRow = mysql_insert_id($link);
How do you accomplish the same with the SQL Server Driver for PHP?
In this extended example, it’s a simple matter of adding some extra SQL to the INSERT statement, and popping the result from the query.
<?php
function lastInsertId($queryID) {
sqlsrv_next_result($queryID);
sqlsrv_fetch($queryID);
return sqlsrv_get_field($queryID, 0);
}
$serverName = '.\SQLEXPRESS';
$connectionInfo = array( "Database"=>"database" );
/* Connect using Windows Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false )
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}
/* Define the Transact-SQL query. Use question marks as parameter placeholders. */
$insertSQL = "INSERT INTO table
(message, fromuser)
VALUES (? , ?) ";
/* Initialize $message and $from */
$message = "Please call me";
$fromuser = "Nick Hodge";
/* special Transact-SQL addition to the SQL insert statement. It will return the last insert ID */
$insertSQL .= "; SELECT SCOPE_IDENTITY() AS IDENTITY_COLUMN_NAME";
/* Execute the statement with the specified parameter values. */
$stmt1 = sqlsrv_query( $conn, $insertSQL, array($message, $fromuser));
if( $stmt1 === false )
{
echo "Could not retrieve last inserted row ID.</br>";
die( print_r( sqlsrv_errors(), true));
}
echo "The last inserted row ID is ".lastInsertId($stmt1);
?>
The magic happens in the addition of $insertSQL .= "; SELECT SCOPE_IDENTITY() AS IDENTITY_COLUMN_NAME"; appended to the INSERT query.
Also note the use of a parameterized query. To reduce the risk of SQL injection nastiness, this nifty query does all the heavy lifting.
