Submit a Pull Request Report a Bug

mysqli::store_result

mysqli_store_result

(PHP 5, PHP 7, PHP 8)

mysqli::store_result -- mysqli_store_result — 传输上次查询的结果集

说明

面向对象风格

public mysqli::store_result(int $mode = 0): mysqli_result|false

过程化风格

mysqli_store_result(mysqli $mysql, int $mode = 0): mysqli_result|false

传输由 mysql 参数表示的连接上的上一次查询的结果集，可在 mysqli_data_seek() 函数中使用。

参数

mysql

仅以过程化样式：由mysqli_connect() 或 mysqli_init() 返回的 mysqli 对象。

mode

想要设置的选项。自 PHP 8.1 起，此参数无效。可以是下列中的某一值：

**有效选项**
名称	说明
`MYSQLI_STORE_RESULT_COPY_DATA`	将结果从内部 mysqlnd 缓冲区复制到 PHP 变量中。默认 mysqlnd 采取引用策略来避免在内容中复制多份同样的结果集。例如，对于包含了很多小行的结果集，复制方法可以减少整体内存使用，因为保存结果的 PHP 变量可能会更早释放。（此选项适用于 mysqlnd 驱动）。

返回值

返回缓冲的结果对象，如果发生错误则返回 false。

注意:
如果查询没有返回结果集（例如，执行 INSERT 语句查询），mysqli_store_result() 会返回 false。如果读取结果集失败，也会返回 false。如果通过检查 mysqli_error() 是否返回非空字符串、mysqli_error() 或 mysqli_field_count() 是否返回了非零值来检查是否有错误。此函数在成功调用 mysqli_query() 后返回 false 的一个可能原因是结果集太大（无法分配内存）。如果 mysqli_field_count() 返回非零值，语句应该产生非空结果集。

错误／异常

If mysqli error reporting is enabled (MYSQLI_REPORT_ERROR) and the requested operation fails, a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT, a mysqli_sql_exception is thrown instead.

示例

参阅 mysqli_multi_query()。

注释

注意:
执行查询之后，使用 mysqli_free_result() 函数来释放结果集所占用的内存，是一个很有用的实战经验。尤其是当使用 mysqli_store_result() 函数来转移数量较大的结果集的时候，释放结果集内存的操作尤为重要。

参见

mysqli_real_query() - 执行一个mysql查询
mysqli_use_result() - Initiate a result set retrieval

＋add a note

User Contributed Notes 5 notes

down

mitchind ¶

13 years ago

After reading through original notes and example above as well as wading through the documentation, I finally got a loop to work with two stored procedures.

Using the results of the first one as a parameter for the second one. Easier to do this way than a huge modified sequence of Inner Join queries.

Hope this helps others...

<?php
// Connect to server and database
$mysqli        = new mysqli("$dbServer", "$dbUser", "$dbPass", "$dbName");

// Open First Stored Procedure using MYSQLI_STORE_RESULT to retain for looping
$resultPicks = $mysqli->query("CALL $proc ($searchDate, $maxRSI, $incRSI, $minMACD, $minVol, $minTrades, $minClose, $maxClose)", MYSQLI_STORE_RESULT);

// process one row at a time from first SP
while($picksRow = $resultPicks->fetch_array(MYSQLI_ASSOC)) {
    // Get Parameter for next SP
    $symbol     = $picksRow['Symbol'];

    // Free stored results
    clearStoredResults($mysqli);

    // Execute second SP using value from first as a parameter (MYSQLI_USE_RESULT and free result right away)
    $resultData    = $mysqli->query("CALL prcGetLastMACDDatesBelowZero('$symbol', $searchDate)", MYSQLI_USE_RESULT);
    $dataRow    = $resultData->fetch_array(MYSQLI_ASSOC);

    // Dump result from both related queries
    echo "<p>$symbol ... Num Dates: " . $dataRow['NumDates'];

    // Free results from second SP
    $resultData->free();

}

// Free results from first SP
$resultPicks->free();

// close connections
$mysqli->close();

#------------------------------------------
function clearStoredResults($mysqli_link){
#------------------------------------------
    while($mysqli_link->next_result()){
      if($l_result = $mysqli_link->store_result()){
              $l_result->free();
      }
    }
}
?>

down

filippo at ecoms dot it ¶

5 years ago

Code to handling errors:

if ($mysqli->multi_query($query)) {
    $result = $mysqli->store_result();
    if ($mysqli->errno == 0) {
    
        /* First result set or FALSE (if the query didn't return a result set) is stored in $result */

        while ($mysqli->more_results()) {
            if ($mysqli->next_result()) {
                $result = $mysqli->store_result();
                if ($mysqli->errno == 0) {
                    /* The result set or FALSE (see above) is stored in $result */
                }
                else {
                    /* Result set read error */
                    break;
                }
            }
            else {
                /* Error in the query */
            }
        }
    }
    else {
        /* First result set read error */
    }
}
else {
    /* Error in the first query */
}

down

-3

Warner ¶

14 years ago

It also seems, that executing a SET statement in multi_query() returns an extra recordset too, which one would not expect.

down

-4

lau at goldenweb dot com dot au ¶

16 years ago

Beware when using stored procedures:
If you connect to the database and then call dbproc A followed by a call to db proc B and then close the connection to the db, the second procedure call will not work.

It looks like there is a bug in MYSQL or mysqli that returns an extra recordset than you would expect. It then doesn't let you call another stored procedure until you finish processing all the recordsets from the first stored procedure call. 

The solution is to simply loop through the additional recordsets between calls to db procs. Here is a function that I call between db proc calls:

<?php
#--------------------------------
function ClearRecordsets($p_Result){
#--------------------------------
    $p_Result->free();    
    while($this->Mysqli->next_result()){
      if($l_result = $this->Mysqli->store_result()){
              $l_result->free();
      }
    }
}

?>

down

-6

Tex Morgan ¶

10 years ago

There's a simpler way to clear out database stored procedure problems:

class MySQLiQuery {
     protected $_resultSet;
     protected $databaseConnection;
....

    protected function free(){
        $this->_resultSet->free();
        $this->_resultSet=null;
    }

    protected function checkMoreResults(){
        if($this->databaseConnection->more_results()){
            return true;
        } else {
            return false;
        }
    }

    protected function clearResults(){
        if($this->checkMoreResults()){
            if($this->databaseConnection->next_result()){
                if($this->_resultSet=$this->databaseConnection->store_result()){
                    $this->free();
                }
                $this->clearResults(); // <----------- recursive call is your friend
            }
        }
    }
.......
}

＋add a note