问题描述
我有一个包含多个结果集的存储过程.我如何前进到 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).