Retrieve the ID of an inserted record: Php & MS SQL SERVER

Question

This question relates to:

PHP Version 5.3.6

Microsoft Drivers for PHP for SQL Server

I am trying to properly retrieve the ID of a record insert using a combination of PHP and SQL Server 2005 or 2008.

This question assumes the existence of the following table:

CREATE TABLE users([id] [int] IDENTITY(1,2) NOT NULL,[username] [varchar](50) NOT NULL,[password] [varchar](40) NOT NULL,[first_name] [varchar](30) NOT NULL,[last_name] [varchar](30) NOT NULL)

If I were to run the following query in Management Studio or via ASP (Classic), a record would be inserted and an ID would be returned, but the same behavior does not seem to exist with PHP and this driver.

INSERT INTO users (username, password, first_name, last_name) VALUES ('x','x','x','x') ; SELECT @@IDENTITY ;

I need help figuring out how to properly pull an ID either by chaining SQL statements together or by any other method.

I have worked up some code.

Variation 1 is a simple select (via query) - no insertion or ID retrieveal performed

Variation 2 is a chained SQL statement which inserts the new record properly, but does not return the ID

Variation 3 uses (execute) instead of (query). The record is inserted, but the ID is not returned. This one generated an error because sqlsrv_execute returns true/false instead of a recordset.

So how can I modify my code to insert a record and retrive an ID? (I'm working on the assumption that the answer to this question will extend to Stored Procedures that return data as well, but perhaps that is not the case)

Thank you.

// Database connection
// -------------------------------------------------------------------------------------------------------
$conn = sqlsrv_connect(DB_SERVER,array("UID" => DB_USER, "PWD" => DB_PASSWORD, "Database"=> DB_NAME ));

// Variation 1, straight select
// -------------------------------------------------------------------------------------------------------
$sql = "SELECT * FROM users;";
$result = sqlsrv_query($conn,$sql);
$row = sqlsrv_fetch_array($result);

// Variation 2, Insert new record and select @@IDENTITY
// -------------------------------------------------------------------------------------------------------
$sql = "INSERT INTO users  (username, password, first_name, last_name) VALUES ('x','x','x','x')  ; SELECT @@IDENTITY ;";
$result = sqlsrv_query($conn,$sql);
$row = sqlsrv_fetch_array($result);

// Variation 3, using EXECUTE instead of QUERY
// -------------------------------------------------------------------------------------------------------
//$sql = "INSERT INTO users  (username, password, first_name, last_name) VALUES ('x','x','x','x')  ; SELECT @@IDENTITY as id ;";
$sql = "INSERT INTO users  (username, password, first_name, last_name) VALUES ('x','x','x','x')  ; SELECT SCOPE_IDENTITY() as id ;";

$stmt = sqlsrv_prepare( $conn, $sql);
$result = sqlsrv_execute($stmt);
$row = sqlsrv_fetch_array($result);

Answer 1

http://www.php.net/manual/en/function.mssql-query.php#104263

I don't use MS SQL at all but have briefly read up on this. It would seem you need to make a call to sqlsrv_next_result. An example is provided in that comment:

$query = "INSERT INTO test (col1, col2) VALUES (?,?); SELECT SCOPE_IDENTITY()";
$resource=sqlsrv_query($conn, $query, $arrParams); 
sqlsrv_next_result($resource); 
sqlsrv_fetch($resource); 
echo sqlsrv_get_field($resource, 0); 

Answer 2

First, do not use @@identity for this purpose if you value the integrity of your data as it can return the wrong answer. Even worse, it can work correctly for years before someone puts a trigger on the table and it starts quietly sending the wrong value.

Use the OUTPUT clause or Scope_identity().

Example of the SQL used to do the output clause:

DECLARE @MyTableVar table( myID int,
                           myName varchar(50),
                           ModifiedDate datetime);
INSERT dbo.mytable
    OUTPUT INSERTED.myID, INSERTED.myName, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES ('test', GETDATE());

Answer 3

This works for me...

 $sql = "Insert into MyTable(column1,column2,column3) VALUES (? , ? , ?); SELECT @@IDENTITY as id;";
 $params = array($value1,$value2,$value3);
 $stmt = sqlsrv_query( $conn, $sql, $params);
 $next_result = sqlsrv_next_result($stmt); 
 $row = sqlsrv_fetch_array($stmt); 
 echo "ROW INSERTED WITH ID : " . $row["id"];
 if( $stmt === false ) {
 Echo "Error writing new application to table";
 }