在 php/mysqli 中使用存储过程检索多个结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1683794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving Multiple Result sets with stored procedure in php/mysqli
提问by MacAnthony
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?
我有一个具有多个结果集的存储过程。我如何前进到 mysqli 中的第二个结果集以获得这些结果？
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 $$
The PHP is something like this:
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 );
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.
然后这是我无法开始工作的部分。我已经尝试使用 mysqli_next_result 移动到下一个结果集，但无法让它工作。我们确实让它与 mysqli_store_result 和 mysqli_fetch_assoc/array/row 一起工作，但由于某种原因，所有整数都作为空字符串返回。
Any one else come across this and have a solution?
有没有其他人遇到过这个问题并有解决方案？
采纳答案by Stefan Gehrig
I think you're missing something here (the following has not been tested):
我认为您在这里遗漏了一些东西（以下内容尚未经过测试）：
$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_use_result($db);
// you have to read the result set here
while ($row = $result1->fetch_assoc()) {
printf("%d\n", $row['id']);
}
// now we're at the end of our first result set.
mysqli_free_result($result1);
//move to next result set
mysqli_next_result($db);
$result2 = mysqli_use_result($db);
// you have to read the result set here
while ($row = $result2->fetch_assoc()) {
printf("%d\n", $row['id']);
}
// now we're at the end of our second result set.
mysqli_free_result($result2);
// close statement
mysqli_stmt_close($stmt);
Using PDOyour code would look like:
使用PDO您的代码如下所示：
$stmt = $db->prepare('CALL multiples(:param1, :param2)');
$stmt->execute(array(':param1' => $param1, ':param2' => $param2));
// read first result set
while ($row = $stmt->fetch()) {
printf("%d\n", $row['id']);
}
$stmt->nextRowset();
// read second result set
while ($row = $stmt->fetch()) {
printf("%d\n", $row['id']);
}
But I have heard that the PDOStatement::nextRowset()is not implemented with the MySQL PDO drivermaking it impossible to retrieve multiple result sets:
但我听说MySQL PDO 驱动程序PDOStatement::nextRowset()没有实现，因此无法检索多个结果集：
- PDO nextRowset not working on MySQL
- pdo_mysql: stored procedure call returning single rowset blocks future queries
- Can't use stored procedures from PDO on Windows
So, depending on your PHP version, you'd have to stick with your mysqli-solution. By the way: do you use the procedural style deliberately? Using object oriented style with mysqliwould make your code look a little bit more appealing (my personal opinion).
因此，根据您的 PHP 版本，您必须坚持使用mysqli-solution。顺便说一句：你是故意使用程序风格的吗？使用面向对象的风格mysqli会让你的代码看起来更有吸引力（我个人的意见）。
回答by Andrew Foster
This has worked really well for me, it will deal with (as an example) as many Select Lists as there are in your SP. Note how you have to close the $call BEFORE you can then get to the OUT parameters from your SP...
这对我来说非常有效，它将处理（作为示例）与您的 SP 中一样多的选择列表。请注意您必须如何关闭 $call，然后才能从 SP 获取 OUT 参数...
?><pre><?
$call = mysqli_prepare($db, 'CALL test_lists(?, ?, @result)');
if($call == false) {
echo "mysqli_prepare ($db, 'CALL test_lists(?, ?, @result) FAILED!!!\n";
} else {
// A couple of example IN parameters for your SP...
$s_1 = 4;
$s_2 = "Hello world!";
// Here we go (safer way of avoiding SQL Injections)...
mysqli_stmt_bind_param($call, 'is', $s_1, $s_2);
// Make the call...
if(mysqli_stmt_execute($call) == false) {
echo "mysqli_stmt_execute($call) FAILED!!!\n";
} else {
//print_r($call);
// Loop until we run out of Recordsets...
$set = 0;
while ($recordset = mysqli_stmt_get_result($call)) {
++$set;
//print_r($recordset);
echo "\nRecordset #" . $set . "...\n";
if ($recordset->num_rows > 0) {
$ctr = 0;
while ($row = $recordset->fetch_assoc()) {
++$ctr;
//print_r($row);
echo "\t" . $ctr . ": ";
forEach($row as $key => $val) {
echo "[" . $key . "] " . $val . "\t";
}
echo "\n";
}
}
echo $recordset->num_rows . " record" . ($recordset->num_rows == 1 ? "" : "s") . ".\n";
// Clean up, ready for next iteration...
mysqli_free_result($recordset);
// See if we can get another Recordset...
mysqli_stmt_next_result($call);
}
// Then you have to close the $call...
mysqli_stmt_close($call);
// ...in order to get to the SP's OUT parameters...
$select = mysqli_query($db, "SELECT @result");
$row = mysqli_fetch_row($select);
$result = $row[0];
echo "\nOUT @result = " . $result . "\n";
}
}
?></pre><?
And this is what the output from the above code looks like using my test_lists SP...
这就是使用我的 test_lists SP 时上述代码的输出结果...
Recordset #1...
1: [s_1] 4 [user_name] Andrew Foster
2: [s_1] 4 [user_name] Cecil
3: [s_1] 4 [user_name] Sheff
3 records.
Recordset #2...
1: [s_2] Hello world! [section_description] The Law
2: [s_2] Hello world! [section_description] History
3: [s_2] Hello world! [section_description] Wisdom Literature
4: [s_2] Hello world! [section_description] The Prophets
5: [s_2] Hello world! [section_description] The Life of Jesus and the Early Church
6: [s_2] Hello world! [section_description] Letters from the Apostle Paul
7: [s_2] Hello world! [section_description] Other Letters from Apostles and Prophets
8: [s_2] Hello world! [section_description] Prophecy - warnings for the present and revelation of the future
8 records.
OUT @result = 16
回答by gapple
It looks like MySQLi may only support multiple result sets through mysqli_multi_query(), since MySQLi_STMTobjects work differently from MySQLi_Resultobjects.
看起来 MySQLi 可能只通过 支持多个结果集mysqli_multi_query()，因为MySQLi_STMT对象与对象的工作方式不同MySQLi_Result。
PDO seems to be somewhat more abstracted, with the PDOStatementobjects being able to handle multiple result sets for both regular queries (PDO::query) and prepared statements(PDO:prepare).
PDO 似乎更抽象一些，PDOStatement对象能够处理常规查询 ( PDO::query) 和准备语句 ( PDO:prepare) 的多个结果集。
