Mysql out参数取值,PHP-MySQL从存储过程获取out参数的值

I have called a MySQL stored procedure from PHP using mysqli. This has one out parameter.

$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:

$rs2 = $mysqli->query("SELECT @id");

while($row = $rs->fetch_object()){

echo var_dump($row);

}

The output of var_dump is as follows.

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:

PHP Fatal error: Cannot use object of type stdClass as array

解决方案

Or even just do a "SELECT @id AS id" then $row->id will work fine. I always rename select columns to keep the name meaningful when necessary :-)

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:

$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

$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.