PHP-7: mysqli query #2 returns no results unless query #1 is closed - why?

Question

I have some PHP-7 code (using "mysqli"), that, of course, wants to execute several queries and to scroll through their result sets. However, I am finding that unless I close() the previous statement, the next statement ... although it seems to run ... produces no results.

(That is to say, the affected-rows count will return zero.)

I am at a loss to explain this behavior . . .

(Incidentally, the necessary function seemed to be statement- closing. Not disposing of result sets.)

= = =

I do not believe that this is a duplicate of the question cited, because that answer is clearly using mysqli_multi_query and I am not. I am performing three different, unrelated queries, in three consecutive loops.

If you are suggesting that I am "accumulating multiple result-sets" and just don't know it, but I do not believe that this is my use-case. The only references to those API-calls that I have seen, specifically make use of the multi-query call, which I am not using. But I would be delighted to learn that I am wrong here.

Code example

echo "Test: simple query.\n"; 
$iter = DB::query('select count(*) from address_book'); 
echo "Query returned " . $iter->rowCount() . " results.\n"; 

foreach ($iter as $v) 
    print_r($v); 

$iter = null;  // WITHOUT this, next stmt will produce no results.

echo "Test: simple query again.\n"; 
$iter = DB::query('select count(*) from address_book'); 
echo "Query returned " . $iter->rowCount() . " results.\n"; 

foreach ($iter as $v) 
    print_r($v); 

 $iter = null;  // DITTO ...

And here are the iterator-support functions:

// Retrieve current element
public function current () {
    if ($this->current == null) {
        $this->current = $this->fetch();
    }

    return $this->current;
}

// Retrieve value of next element
public function next () {
    $this->current = $this->fetch();
    $this->position++;

    return $this->current;
}


// Retrieve key of current element
public function key () {
    return $this->position;
}


// Check if current position is valid - used to stop an iterator
public function valid () {
    if ($this->position >= 0 && $this->position < $this->rowCount()) {
        return true;
    }

    return false;
}

// Rewind the iterator to the first element
public function rewind () {
    $this->checkNoResultException();
    $this->position = 0;
    $this->_result->data_seek(0);
}

And here is 'query':

public static function query ( $stmt, $params = null ) {
    $objInstance = self::getInstance();

    try {
        if ($params === null) {
            return $objInstance->query($stmt);
        }
        else {
            return $objInstance->prepare($stmt)->execute($params);
        }
    }
    catch(MysqliException $e) {
        error_log("(DB::query) Query failed: " . $e->getMessage());
        die("SQL Query failed -- see log");
    }
}

Answer 1

For closure:   I wound up “resolving” this problem ... sort of ... by changing the class to do a fetch_all() on the cursor, storing the entire result-set in an array, then immediately closing the handle itself.   Since I know that I am always dealing with reasonably-sized result sets on a capacious machine, this actually allowed me to effectively circumvent several ... “weirdnesses.”  

I never did arrive at a satisfactory explanation as to why the observed behavior was occurring in the first place . . .

Answer 2

I'm going to "answer" this question, not because it's really-answered but to ... first of all, give a "shout-out" to RiggsFolly for immediately jumping in to help with the 18-comment exchange which follows above. I am very, very appreciative of his quickness and persistence.

---

The question is not completely resolved yet, but I have found a number of interesting things in the test-case code shown above:

• If you set $iter = null; as shown above, the problem reliably disappears ... even though setting a variable to null does not obligate PHP to perform garbage-collection immediately. (However, I do see the destructor being called immediately!)

• If you call $iter->close(), which in my code will close both the result-handle and the statement-handle, the problem disappears.

• Also (!) if you use a different variable to perform the second query, the problem also disappears! (In other words, change $iter to, say, $iter1 and $iter2.

^^^ This last bullet-point is MOST interesting, and unexpected! ^^^

As a precaution, I have also added an explicit __destruct method on the statement-object to explicitly call its close() method, explicitly destroying both statement and result handles, if they exist, as soon as cleanup does happen. This does not affect the outcome, but it makes me feel a little warm-and-fuzzier, I guess . . .

I welcome any further insights on this issue, because it sure confuses the heck outa me. :-D