检索插入记录的 ID:Php &MS SQL 服务器 [英] Retrieve the ID of an inserted record: Php & MS SQL SERVER
问题描述
这个问题涉及:
PHP 5.3.6 版
用于 SQL Server 的 PHP 的 Microsoft 驱动程序
我正在尝试使用 PHP 和 SQL Server 2005 或 2008 的组合正确检索记录插入的 ID.
这个问题假设存在下表:
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 和此驱动程序似乎不存在相同的行为.
INSERT INTO users (username, password, first_name, last_name) VALUES ('x','x','x','x') ;选择@@IDENTITY ;
我需要帮助弄清楚如何通过将 SQL 语句链接在一起或通过任何其他方法正确提取 ID.
我已经编写了一些代码.
变体 1 是一个简单的选择(通过查询) - 不执行插入或 ID 检索
变体 2 是一个链式 SQL 语句，它正确插入新记录，但不返回 ID
变体 3 使用 (execute) 而不是 (query).记录已插入，但未返回 ID.这个产生了一个错误，因为 sqlsrv_execute 返回 true/false 而不是记录集.
那么如何修改我的代码以插入记录并检索 ID?(我假设这个问题的答案也将扩展到返回数据的存储过程，但也许事实并非如此)
谢谢.
//数据库连接//-------------------------------------------------------------------------------------------------------$conn = sqlsrv_connect(DB_SERVER,array("UID" => DB_USER, "PWD" => DB_PASSWORD, "Database"=> DB_NAME));//变体1，直接选择//-------------------------------------------------------------------------------------------------------$sql = "SELECT * FROM users;";$result = sqlsrv_query($conn,$sql);$row = sqlsrv_fetch_array($result);//变体2，插入新记录并选择@@IDENTITY//-------------------------------------------------------------------------------------------------------$sql = "INSERT INTO users (username, password, first_name, last_name) VALUES ('x','x','x','x');选择@@IDENTITY ;";$result = sqlsrv_query($conn,$sql);$row = sqlsrv_fetch_array($result);//变体 3，使用 EXECUTE 而不是 QUERY//-------------------------------------------------------------------------------------------------------//$sql = "INSERT INTO users (username, password, first_name, last_name) VALUES ('x','x','x','x');SELECT @@IDENTITY 作为 id ;";$sql = "INSERT INTO users (username, password, first_name, last_name) VALUES ('x','x','x','x');SELECT SCOPE_IDENTITY() 作为 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
我根本不使用 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);回声 sqlsrv_get_field($resource, 0);This question relates to:
PHP Version 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.
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);
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);
这篇关于检索插入记录的 ID:Php &MS SQL 服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
