使用 php/mysqli 中的存储过程检索多个结果集

php mysqli resultset stored-procedures

2022-08-30 22:18:01

我有一个具有多个结果集的存储过程。如何前进到mysqli中的第二个结果集以获得这些结果？

假设它是一个存储的过程，例如：

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/数组/行一起使用，但由于某种原因，所有int都作为空白字符串返回。

还有其他人遇到这种情况并有解决方案吗？

答案 1

我认为你在这里错过了一些东西。以下是使用 mysqli 预准备语句从存储过程获取多个结果的方法：

$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\n", $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\n", $row['id']);
}
// now we're at the end of our second result set.

// 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']);
}

顺便说一句：你是否故意使用程序风格？使用面向对象的样式会让你的代码看起来更具吸引力（我个人的观点）。mysqli

答案 2

这对我来说非常有效，它将处理（作为示例）SP中尽可能多的选择列表。请注意，在从SP中获取OUT参数之前，您必须如何关闭$call...

?><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><?

这就是使用我的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