检索插入记录的 ID：Php & MS SQL SERVER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6850375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieve the ID of an inserted record: Php & MS SQL SERVER
提问by Ralph M. Rivera
This question relates to:
PHP Version 5.3.6
这个问题涉及：
PHP 版本 5.3.6
I am trying to properly retrieve the ID of a record insert using a combination of PHP and SQL Server 2005 or 2008.
我正在尝试使用 PHP 和 SQL Server 2005 或 2008 的组合正确检索记录插入的 ID。
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)
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.
如果我在 Management Studio 中或通过 ASP（经典）运行以下查询，将插入一条记录并返回一个 ID，但 PHP 和此驱动程序似乎不存在相同的行为。
INSERT INTO users (username, password, first_name, last_name) VALUES ('x','x','x','x') ; SELECT @@IDENTITY ;
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.
我需要帮助弄清楚如何通过将 SQL 语句链接在一起或通过任何其他方法正确提取 ID。
I have worked up some code.
我已经编写了一些代码。
Variation 1 is a simple select (via query) - no insertion or ID retrieveal performed
变体 1 是一个简单的选择（通过查询） - 不执行插入或 ID 检索
Variation 2 is a chained SQL statement which inserts the new record properly, but does not return the ID
变体 2 是一个链式 SQL 语句，它正确插入新记录，但不返回 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.
变体 3 使用 (execute) 而不是 (query)。记录已插入，但未返回 ID。这个产生了一个错误，因为 sqlsrv_execute 返回 true/false 而不是记录集。
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)
那么如何修改我的代码以插入记录并检索 ID？（我正在假设这个问题的答案将扩展到返回数据的存储过程，但也许事实并非如此）
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);
回答by Jonathon
http://www.php.net/manual/en/function.mssql-query.php#104263
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:
我根本不使用 MS SQL，但已经简要地阅读了这一点。您似乎需要调用 sqlsrv_next_result。该评论中提供了一个示例：
$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);
回答by HLGEM
First, do not use @@identityfor 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.
首先，@@identity如果您重视数据的完整性，请不要用于此目的，因为它可能会返回错误的答案。更糟糕的是，在有人将触发器放在桌面上并开始悄悄发送错误值之前，它可以正常工作多年。
Use the OUTPUTclause or Scope_identity().
使用OUTPUT子句 or Scope_identity()。
Example of the SQL used to do the output clause:
用于执行输出子句的 SQL 示例：
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());
回答by HLGEM
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.
我最近遇到了一个非常相似的问题，虽然我还没有在这里尝试其他解决方案。我相信一个更好的程序员可以想出一个更好的解决方案。我对这个问题的解决方案是使用选择查询生成 UUID，然后插入 ID，而不是在我的插入查询中生成它。与此处列出的其他解决方案相比，它也非常干净。
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.
现在您拥有了该字段的 ID，它是唯一的，并且您已经在 PHP 中拥有了该值。
Edit: My environment is PHP 5.3+SQL Server 2005.
编辑：我的环境是 PHP 5.3+SQL Server 2005。
