Question

我有一套有多种结果的储存程序。我如何推动我方设法取得这些结果的第二次结果?

让我们说,它储存着一种像:

create procedure multiples( param1 INT, param2 INT )
BEGIN

SELECT * FROM table1 WHERE id = param1;

SELECT * FROM table2 WHERE id = param2;

END $$

购买力平价就是这样:

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

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

mysqli_stmt_execute( $stmt );

mysqli_stmt_bind_result( $stmt, $id );

那么,这是我无法工作的部分。我先尝试利用我sqli_next_result,去下一套成果,但不能再工作。我们确实与我的Sqli_store_result and mysqli_fetch_assoc/array/row合作,但出于某种原因,所有帐篷都被作为空洞归还。

是否有其他解决办法?

Answer 1

我认为你在这里再次找不到一些东西。这正是你如何利用我Sqli编写的发言稿从储存程序中获得多重结果:

$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);

Using 。您的法典希望:

$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的物体风格,使你的代码稍微增加吸引力(我的个人意见)。

Answer 2

对我来说,这确实是好的,它将处理(作为例子)像你们的《特殊标准》中那样的许多挑选名单。请注意,你必须如何关闭你从下至下期的预支款。

?><pre><?
$call = mysqli_prepare($db,  CALL test_lists(?, ?, @result) );
if($call == false) {
    echo "mysqli_prepare ($db,  CALL test_lists(?, ?, @result) FAILED!!!
";
} 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!!!
";
    } 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 "
Recordset #" . $set . "...
";
            if ($recordset->num_rows > 0) {
                $ctr = 0;
                while ($row = $recordset->fetch_assoc()) {
                    ++$ctr;
                    //print_r($row);
                    echo "	" . $ctr . ": ";
                    forEach($row as $key => $val) {
                        echo "[" . $key . "] " . $val . "	";
                    }
                    echo "
";
                }
            }
            echo $recordset->num_rows . " record" . ($recordset->num_rows == 1 ? "" : "s") . ".
";
            // 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 "
OUT @result = " . $result . "
";
    }
}
?></pre><?

而这正是上述法典的产出所看的,是使用我的标准——清单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

Answer 3

如同MySQLi一样,它只能通过<代码>mysqli_multi_query()支持多种结果集,因为<代码>MySQLi_STMT的物体的工作不同于<代码>MySQLi_Result的物体。

PDO似乎比较抽象,PDOStatement 。能够处理定期查询的多种结果集的物体(PDO:query)和准备的报表(PDO:prepare)。

友情链接