代码之家 › 专栏 › 技术社区 › webdevduck

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

mysqli stored-procedures mysql php

0

webdevduck · 技术社区 · 7 年前

我试图使用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 workbench中执行存储过程,我会得到如下两个结果选项卡:

# 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 ;

1 回复 | 直到 7 年前

1

webdevduck 7 年前

我使用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;

    }