php mysqli执行procedure 一次获得多个结果集, 同时执行多个查询select

一般来说一个mysqli_query(“query”)对应一个retval.

但是如果一个procedure里执行嘞两个select查询,那么mysqli_query(“call procedure()”)只会返回给你retval1,如果你需要retval2的话请自行执行mysqli_next_result(conn);之后retval2 = mysqli_use_result(conn);,其中conn是mysqli_connect的数据库链接.

$sql = sprintf("call search_all('%s',%d,%d,%d,%d,%d,%d,%d,%d,%d);", $keyWord, $sort, $isasc, $hasContect, $hasAddress, $hasHomePage, $notLeagal, $isCompany, $start, $len);
$retval1 = mysqli_query( $conn, $sql );

while($row = mysqli_fetch_assoc($retval1))
{
    $dataBuf["data"][$i]['id']=$row['id'];
    $dataBuf["data"][$i]['huazhan_id']=$row['huazhan_id'];
    $dataBuf["data"][$i]['company']=$row['company'];
    $dataBuf["data"][$i]['description']=$row['description'];
    $dataBuf["data"][$i]['tag']=$row['tag'];
    $dataBuf["data"][$i]['location']=$row['location'];
    $dataBuf["data"][$i]['address']=$row['address'];
    $dataBuf["data"][$i]['homePage']=$row['homePage'];
    $dataBuf["data"][$i]['product']=$row['product'];
    $dataBuf["data"][$i]['regCapital']=$row['regCapital'];
    $dataBuf["data"][$i]['contectName']=$row['contectName'];
    $dataBuf["data"][$i]['contectPosition']=$row['contectPosition'];
    $dataBuf["data"][$i]['contectPhone']=$row['contectPhone'];
    $dataBuf["data"][$i]['contectTel']=$row['contectTel'];
    $dataBuf["data"][$i]['contectQq']=$row['contectQq'];
    $dataBuf["data"][$i]['contectEmail']=$row['contectEmail'];
    $dataBuf["data"][$i]['contectAllJson']=$row['contectAllJson'];
    $dataBuf["data"][$i]['exhibitionJson']=$row['exhibitionJson'];
    $dataBuf["data"][$i]['raw']=$row['raw'];
    $dataBuf["data"][$i]['favorite']=$row['favorite'];
    $dataBuf["data"][$i]['addDate']=$row['addDate'];
    $i++;

}
mysqli_free_result($retval1);

mysqli_next_result($conn);
$retval2 = mysqli_use_result($conn);
$row = mysqli_fetch_assoc($retval2);

mysqli_close($conn);
$dataBuf["resultNumber"] = $row['count'];
echo json_encode($dataBuf);

 

 

如果需要在一个mysqli_query里执行多个select,应该使用mysqli_muti_query函数.

使用方式见:

https://www.php.net/manual/en/mysqli.multi-query.php

注意:

WATCH OUT: if you mix mysqli->multi_query andmysqli->query, the latter(s) won't be executed!

<?php
// BAD CODE:
mysqli->multi_query(" Many SQL queries ; "); // OKmysqli->query(" SQL statement #1 ; ") // not executed!
mysqli->query(" SQL statement #2 ; ") // not executed!mysqli->query(" SQL statement #3 ; ") // not executed!
mysqli->query(" SQL statement #4 ; ") // not executed!
?>

The only way to do this correctly is:

<?php
// WORKING CODE:mysqli->multi_query(" Many SQL queries ; "); // OK
while (mysqli->next_result()) {;} // flush multi_queriesmysqli->query(" SQL statement #1 ; ") // now executed!
mysqli->query(" SQL statement #2 ; ") // now executed!mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!
?>

 

另外在php中执行mysql procedure,可以使用预置语句的方式:mysqli_stmt类

实用方式详见https://www.php.net/manual/en/class.mysqli-stmt.php

例;

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

https://stackoverflow.com/questions/1683794/retrieving-multiple-result-sets-with-stored-procedure-in-php-mysqli

 

注:

mysqli_query 返回false,执行语句出现错误,具体查看$conn->error

command out of sync 错误: 理论上是因为没有mysqli_free_result();或mysqli_store_result()或mysqli_result::fetch_all()导致,实际添加了mysqli_free_result()依旧出错

https://stackoverflow.com/questions/3632075/why-is-mysqli-giving-a-commands-out-of-sync-error