检索插入的记录的ID:Php& MS SQL服务器 [英] Retrieve the ID of an inserted record: Php & MS SQL SERVER
问题描述
这个问题涉及:
This question relates to:
PHP版本5.3.6
PHP Version 5.3.6
用于SQL Server的PHP的Microsoft驱动程序
我正在尝试结合使用PHP和SQL Server 2005或2008来正确检索记录插入的ID.
I am trying to properly retrieve the ID of a record insert using a combination of PHP and SQL Server 2005 or 2008.
此问题假定存在下表:
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)
如果我要在Management Studio中或通过ASP(经典)运行以下查询，则会插入一条记录并返回一个ID，但是PHP和此驱动程序似乎不存在相同的行为.
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 ;
我需要帮助弄清楚如何通过将SQL语句链接在一起或通过任何其他方法来正确提取ID.
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.
变量1是一个简单的选择(通过查询)-不执行插入或ID检索
Variation 1 is a simple select (via query) - no insertion or ID retrieveal performed
版本2是一条链式SQL语句，可正确插入新记录，但不返回ID
Variation 2 is a chained SQL statement which inserts the new record properly, but does not return the ID
版本3使用(执行)而不是(查询).记录已插入，但未返回ID.由于sqlsrv_execute返回true/false而不是记录集，因此该错误产生了一个错误.
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.
那么如何修改我的代码以插入记录并获取ID? (我正在假设该问题的答案将扩展到还返回数据的存储过程，但事实并非如此)
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)
谢谢.
// 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);
推荐答案
我根本不使用MS SQL，但已简要阅读了此内容.似乎您需要调用sqlsrv_next_result.该注释中提供了一个示例:
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);
这篇关于检索插入的记录的ID:Php& MS SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
