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();
}
}
}
?>
mysqli::store_result
mysqli_store_result
(PHP 5, PHP 7, PHP 8)
mysqli::store_result -- mysqli_store_result — 传输上次查询的结果集
参数
-
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_free_result() 函数来释放结果集所占用的内存，是一个很有用的实战经验。尤其是当使用 mysqli_store_result() 函数来转移数量较大的结果集的时候，释放结果集内存的操作尤为重要。
add a note
User Contributed Notes 5 notes
mitchind ¶
14 years ago
filippo at ecoms dot it ¶
6 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 */
}
Warner ¶
15 years ago
It also seems, that executing a SET statement in multi_query() returns an extra recordset too, which one would not expect.
lau at goldenweb dot com dot au ¶
17 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();
}
}
}
?>
Tex Morgan ¶
11 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
}
}
}
.......
}
