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

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
// ----------------------------------------------------------------------开发者_StackOverflow社区---------------------------------
$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);

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); 

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());

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";
 }  

I had a very similar issue recently and, while I haven't tried the other solutions here. I'm sure a better programmer could come up with a better solution. My solution to the problem was to generate the UUID using a select query and then insert the ID instead of generating it on my insert query. It's also pretty clean compared to the other solutions listed here.

Here's my solution:

//Create the UUID
$uid = mssql_fetch_assoc(mssql_query("SELECT CONVERT(VARCHAR(200),NEWID()) as id"));

//Insert the item
$sql = "INSERT INTO TableName (Uid,colA,colB) VALUES ('".$uid['id']."','a','b')";

Now you have the ID of the field, it's unique, and you have the value in PHP already.

Edit: My environment is PHP 5.3+SQL Server 2005.