問題描述

我有一個包含多個結果集的存儲過程.我如何前進到 mysqli 中的第二個結果集以獲得這些結果?

I have a stored procedure that has multiple result sets. How do I advance to the 2nd result set in mysqli to get those results?

假設它是一個存儲過程，例如:

Let's say it's a stored proc like:

create procedure multiples( param1 INT, param2 INT )
BEGIN

SELECT * FROM table1 WHERE id = param1;

SELECT * FROM table2 WHERE id = param2;

END $$

PHP 是這樣的:

$stmt = mysqli_prepare($db, 'CALL multiples(?, ?)');

mysqli_stmt_bind_param( $stmt, 'ii', $param1, $param2 );

mysqli_stmt_execute( $stmt );

mysqli_stmt_bind_result( $stmt, $id );

然后這是我無法開始工作的部分.我已經嘗試使用 mysqli_next_result 移動到下一個結果集，但無法讓它工作.我們確實讓它與 mysqli_store_result 和 mysqli_fetch_assoc/array/row 一起工作，但由于某種原因，所有整數都作為空字符串返回.

Then this is the part I can't get to work. I've tried using mysqli_next_result to move to the next result set, but can't get it to work. We did get it to work with mysqli_store_result and mysqli_fetch_assoc/array/row, but for some reason all the ints get returned as blank strings.

有其他人遇到過這個問題并有解決方案嗎?

Any one else come across this and have a solution?

推薦答案

我認為您在這里遺漏了一些東西.這是使用 mysqli 準備好的語句從存儲過程中獲取多個結果的方法:

I think you're missing something here. This is how you can get multiple results from stored procedure using mysqli prepared statements:

$stmt = mysqli_prepare($db, 'CALL multiples(?, ?)');
mysqli_stmt_bind_param($stmt, 'ii', $param1, $param2);
mysqli_stmt_execute($stmt);
// fetch the first result set
$result1 = mysqli_stmt_get_result($stmt);
// you have to read the result set here 
while ($row = $result1->fetch_assoc()) {
    printf("%d
", $row['id']);
}
// now we're at the end of our first result set.

//move to next result set
mysqli_stmt_next_result($stmt);
$result2 = mysqli_stmt_get_result($stmt);
// you have to read the result set here 
while ($row = $result2->fetch_assoc()) {
    printf("%d
", $row['id']);
}
// now we're at the end of our second result set.

// close statement
mysqli_stmt_close($stmt);

使用 PDO 你的代碼看起來像:

Using PDO your code would look like:

$stmt = $db->prepare('CALL multiples(:param1, :param2)');
$stmt->execute(array(':param1' => $param1, ':param2' => $param2));
// read first result set
while ($row = $stmt->fetch()) {
    printf("%d
", $row['id']);
}
$stmt->nextRowset();
// read second result set
while ($row = $stmt->fetch()) {
    printf("%d
", $row['id']);
}

順便說一句:你是故意使用程序風格的嗎?在 mysqli 中使用面向對象的風格會讓你的代碼看起來更有吸引力(我的個人觀點).

By the way: do you use the procedural style deliberately? Using object oriented style with mysqli would make your code look a little bit more appealing (my personal opinion).

這篇關于在 php/mysqli 中使用存儲過程檢索多個結果集的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！