問題描述

我有一個存儲過程 IsUserPresent，例如:

I have a stored procedure IsUserPresent like:

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

我想使用 mysqli 準備好的語句從 PHP 調用它.我正在按照代碼片段執行此操作，但它給了我警告.

and I want to call it from PHP using mysqli prepared statement. I am doing it following code snippet but it gives me warning.

$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 手冊 指出您必須使用會話變量(MySQL 會話，而不是 PHP)

The way stored procedures work with prepared statements is a bit more complicated. PHP manual states that you've got to use session variables (MySQL sessions, not PHP)

INOUT/OUT 參數

INOUT/OUT parameter

使用會話變量訪問 INOUT/OUT 參數的值.

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 參數的函數.

I recommend to rewrite this procedure as a function with one IN parameter that returns INT.

這篇關于PHP mysqli 為沒有參數的存儲過程準備的語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！