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.