php 执行存储过程 out,PHP mysqli为带有out参数的存储过程准备了语句

我有一个存储过程IsUserPresent像：

DELIMITER $$

CREATE PROCEDURE IsUserPresent(

in userid varchar (150),

out isPresent bit

)

BEGIN

SET isPresent=0;

SELECT COUNT(*)

INTO isPresent

FROM users_table

WHERE users_table.userid=userid;

END$$

我想用PHP调用它来使用mysqli预编译语句.我是在代码片段之后做的,但它给了我警告.

$connect=&ConnectDB();

$stmt=$connect->prepare("CALL IsUserPresent(?,?)");

$stmt->bind_param('si',$uid,$userCount);

$stmt->execute();

$toRet = $userCount!=0;

Disconnect($connect);

return $toRet;

警告如下：

Premature end of data (mysqlnd_wireprotocol.c:1112)

Warning: mysqli_stmt::execute(): RSET_HEADER packet 1 bytes shorter than expected

Warning: mysqli_stmt::execute(): Error reading result set's header

解决方法:

存储过程使用预处理语句的方式有点复杂. PHP manual声明你必须使用会话变量(MySQL会话,而不是PHP)

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

所以你可以做到

$connect=&ConnectDB();

// bind the first parameter to the session variable @uid

$stmt = $connect->prepare('SET @uid := ?');

$stmt->bind_param('s', $uid);

$stmt->execute();

// bind the second parameter to the session variable @userCount

$stmt = $connect->prepare('SET @userCount := ?');

$stmt->bind_param('i', $userCount);

$stmt->execute();

// execute the stored Procedure

$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter

$r = $connect->query('SELECT @userCount as userCount');

$row = $r->fetch_assoc();

$toRet = ($row['userCount'] != 0);

备注：

我建议将此过程重写为具有一个返回INT的IN参数的函数.

标签：php,mysql,mysqli,prepared-statement,stored-procedures

来源： https://codeday.me/bug/20191001/1840102.html