PHP - MySQL 从存储过程中获取 out 参数的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11693234/
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
PHP - MySQL gets value of out parameter from a stored procedure
提问by mtk
I have called a MySQL stored procedure from PHP using mysqli. This has one out parameter.
我已经使用 .php 从 PHP 调用了一个 MySQL 存储过程mysqli。这有一个输出参数。
$rs = $mysqli->query("CALL addNewUser($name,$age,@id)");
Here, @id is the out parameter. Next, I fire the following query to get the value of the out parameter:
这里，@id 是输出参数。接下来，我触发以下查询以获取 out 参数的值：
$rs2 = $mysqli->query("SELECT @id");
while($row = $rs->fetch_object()){
echo var_dump($row);
}
The output of var_dumpis as follows.
的输出var_dump如下。
object(stdClass)#5 (1) { ["@id"]=> string(6) "100026" }
So, now I want to retrieve the value of @id, which I am unable to. I tried $row[0]->{@id}but this gave following error:
所以，现在我想检索 的值@id，但我无法检索。我试过了，$row[0]->{@id}但这给出了以下错误：
PHP Fatal error: Cannot use object of type stdClass as array
PHP 致命错误：无法使用 stdClass 类型的对象作为数组
回答by TerryE
Or even just do a "SELECT @id AS id"then $row->idwill work fine. I always rename select columns to keep the name meaningful when necessary :-)
或者甚至只是做一个"SELECT @id AS id"then$row->id就可以了。我总是重命名选择列以在必要时保持名称有意义:-)
BTW, you can simply concatenate the call and select @... (with a ; statement delimiter) and the RS will be the returned value. Unfortunately this returns a mutli-resultset and you need to flush the full set otherwise the subsequent queries will stall. See following examples:
顺便说一句，您可以简单地连接调用并选择 @... （使用 ; 语句分隔符），RS 将是返回值。不幸的是，这会返回一个多结果集，您需要刷新完整集，否则后续查询将停止。请参阅以下示例：
$db->multi_query( "CALL addNewUser($name,$age,@id);SELECT @id as id" );
$db->next_result(); // flush the null RS from the call
$rs=$db->store_result(); // get the RS containing the id
echo $rs->fetch_object()->id, "\n";
$rs->free();
Alternatively add the select into the addNewUser and return a RS instead of out param
或者将选择添加到 addNewUser 并返回 RS 而不是 out 参数
$rs = $db->query( "CALL addNewUser($name,$age)" );
echo $rs->fetch_object()->id, "\n";
$rs->close();
$db->next_result(); // flush the null RS from the call
The first returns a multiquery (NULL, RS) set and the second a (RS, NULL) set, hence you can use a simple query() call which embeds the first fetch_object(), but you still need to flush the RS stack.
第一个返回一个多查询 (NULL, RS) 集，第二个返回一个 (RS, NULL) 集，因此您可以使用嵌入第一个 fetch_object() 的简单 query() 调用，但您仍然需要刷新 RS 堆栈。
回答by Madara's Ghost
Just $row->{"@id"}would work here. You can't use an stdClassas an array ($row[0]...).
只是$row->{"@id"}会在这里工作。您不能将 anstdClass用作数组 ( $row[0]...)。
回答by Prabakar Sebastin
Another correct methods its working fine: Cheers!!
另一个正确的方法它工作正常：干杯！！
$procedureName = 'VALIDATE_USER';
$procedure = "CALL $procedureName('$username','$pwd',@p_userid)";
$results1 = $dbconnection->query($procedure);
$results2 = $dbconnection->query("SELECT @p_userid");
$num_rows = $results2->num_rows;
if ($num_rows > 0) {
while($row = $results2->fetch_object())
{
echo $row->{"@p_userid"};
}
}
回答by Palladium
Alternatively, you can just fetch the data as an array using mysqli::fetch_assoc()and access the data using $row['@id'].
或者，您可以使用 将数据作为数组获取并使用mysqli::fetch_assoc()访问数据$row['@id']。
回答by SATYAJEET RANJAN
Here is the working solution:
这是工作解决方案：
enter code $res = $conn->multi_query( "CALL PROCNAME(@x);SELECT @x" );
if( $res ) {
$results = 0;
do {
if ($result = $conn->store_result()) {
printf( "<b>Result #%u</b>:<br/>", ++$results );
while( $row = $result->fetch_row() ) {
foreach( $row as $cell ) echo $cell, " ";
}
$result->close();
if( $conn->more_results() ) echo "<br/>";
}
} while( $conn->next_result() );
}
$conn->close();
