时间戳

首页 > 解决方案 > PHP 无法从 mysqli 语句中读取第二个结果集

php - PHP 无法从 mysqli 语句中读取第二个结果集

问题描述

我正在尝试使用 PHP 从存储过程中读取两个结果集。第一个结果集很好，但虽然第二个似乎存在，但我无法强制 php 处理它。相反，当尝试绑定时，它会因旧栗子而失败

“PHP 警告：( mysqli_stmt_bind_result)：绑定变量的数量与准备好的语句中的字段数量不匹配”。

public function GetSession($sessionId) {
        $conn = mysqli_connect(DbConstants::$servername, DbConstants::$username, DbConstants::$password, DbConstants::$dbname);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $call = mysqli_prepare($conn, 'CALL Sp_Session_GetById(?)');
        mysqli_stmt_bind_param($call, 'i', $sessionId);

        mysqli_stmt_execute($call);

        mysqli_stmt_bind_result(
            $call,
            $id,
            $startTime,
            $duration,
            $description,
            $instructorId,
            $aircraftId,
            $display,
            $bookable,
            $cancelled
        );

        mysqli_stmt_fetch($call);
        $session = new Session($id, $startTime, $duration, $description, $instructorId, $aircraftId, $display, $bookable, $cancelled, []);

        if(mysqli_stmt_more_results($call)) { /* This is true */
            mysqli_stmt_next_result($call);
            /* This is where it goes wrong */
            mysqli_stmt_bind_result(
                $call,
                $sessionTypeId,
                $spaces,
                $enabled
            );
        }
}

如果我在 mysql 工作台中执行存储过程，我会得到两个结果选项卡，如下所示：

# id, startTime, duration, description, instructor_id, aircraft_id, display, bookable, cancelled
'38', '2018-05-19 09:00:00', '180', NULL, '18', '2', '1', '1', '0'

# sessiontype_id, spaces, enabled
'1', '3', '1'
'2', '3', '1'

存储过程创建如下：

DELIMITER //

CREATE PROCEDURE Sp_Session_GetById (IN
    _id int
)
BEGIN
SELECT 
    s.id,
    s.startTime,
    s.duration,
    s.description,
    s.instructor_id,
    s.aircraft_id,
    s.display,
    s.bookable,
    s.cancelled
FROM session s
WHERE s.id = _id
AND s.cancelled = false
AND s.display = true;

SELECT sst.sessiontype_id, sst.spaces, sst.enabled
FROM session_sessiontype as sst
WHERE sst.session_id = _id;

END //

DELIMITER ;

标签： phpmysqlstored-proceduresmysqli

解决方案

我有多个使用 Barmar 建议使用 mysqli_multi_query() 的结果集，我将在下面发布。但是，它不使用 mysqli_stmt_next_result()，所以如果有人使用 mysqli_stmt_next_result() 提供了一个可行的解决方案，我会将他们的解决方案标记为接受的解决方案。

        public function GetSession($sessionId) {
        $conn = mysqli_connect(DbConstants::$servername, DbConstants::$username, DbConstants::$password, DbConstants::$dbname);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $query = 'CALL Sp_Session_GetById(' . $sessionId . ');';
        mysqli_multi_query($conn, $query);

        $sessionResult = mysqli_store_result($conn);
        $sessionRow = mysqli_fetch_row($sessionResult);

        $session = new Session(
            $sessionRow[0],
            $sessionRow[1],
            $sessionRow[2],
            $sessionRow[3],
            $sessionRow[4],
            $sessionRow[5],
            $sessionRow[6],
            $sessionRow[7],
            $sessionRow[8],
            []);

        mysqli_free_result($sessionResult);

        mysqli_next_result($conn);

        $sessionTypeResult = mysqli_store_result($conn);
        while($sessionTypeRow = mysqli_fetch_row($sessionTypeResult)) {
            array_push($session->sessionTypesForSession, $sessionTypeRow[0]);
        }

        $conn->close();

        return $session;

    }

推荐阅读