在php/mysqli中使用存储过程检索多个结果集 [英] Retrieving Multiple Result sets with stored procedure in php/mysqli
问题描述
我有一个具有多个结果集的存储过程.如何前进到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一起使用,但是由于某种原因,所有int均以空字符串形式返回.
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?
推荐答案
我认为您在这里缺少了一些东西(以下内容未经测试):
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);
使用 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']);
}
但是我听说 PDOStatement::nextRowset()
未实现与 MySQL PDO驱动程序使其无法检索多个结果集:
But I have heard that the PDOStatement::nextRowset()
is not implemented with the MySQL PDO driver making it impossible to retrieve multiple result sets:
- 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
因此,根据您的PHP版本,您必须坚持使用 mysqli
-解决方案.顺便说一句:您是否故意使用程序样式?将面向对象的样式与mysqli
结合使用会使您的代码更具吸引力(我个人认为).
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 mysqli
would make your code look a little bit more appealing (my personal opinion).
这篇关于在php/mysqli中使用存储过程检索多个结果集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!