Share via

SQL Server Driver for PHP: Last Inserted Row ID

  • Article

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.

Comments