Get ID of 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 get the record insert ID correctly 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 ran the following query in Management Studio or through ASP (classic), the record would be inserted and the identifier 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 correctly get the identifier, either by concatenating SQL statements, or in any other way.

I have processed some code.

Option 1 - this is a simple choice (through a query) - neither inserting nor re-executing the identifier is performed

Option 2 is an encoded SQL statement that correctly inserts a new record but does not return an identifier

Option 3 uses (executes) instead of (query). The record is inserted, but the identifier is not returned. This caused an error because sqlsrv_execute returns true / false instead of a recordset.

So, how can I change my code to insert a record and extract an identifier? (I am working on the fact that the answer to this question will apply to stored procedures that also return data, but maybe this is not so)

Thanks.

 // 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 I read a little about it. It would seem that you need to call sqlsrv_next_result. An example is given in this 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 may return an incorrect answer. Worse, it can work correctly for many years before someone puts the trigger on the table and it starts quietly sending the wrong value.

Use the OUTPUT clause or Scope_identity() .

An example of SQL used to display a sentence:

 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

I had a very similar problem recently, and I have not tried other solutions here. I am sure that the best programmer can find the best solution. My solution to the problem was to generate a UUID using a select query and then insert the identifier instead of generating it in my insert request. It is also pretty clean compared to the other solutions listed here.

Here is 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 a field identifier, it is unique, and you already have a value in PHP.

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