Stored Procedure causing problems with mysqli when

Question

I have broken down this issue to it's essence but am still having problems.

When I try and use fetch_all to get the results of a stored procedure, I get results returned to array as expected, but subsequent mysqli calls throw "command out of sync" errors.

I even tried simplifying my stored procedure to...

CREATE PROCEDURE testp()
BEGIN
   SELECT * FROM tbl
END

I made the table small, 10 rows 3 cols.

I use the following PHP

$query = "CALL testp()";
$result = $db->query($query);
$rows = $result->fetch_all(MYSQL_ASSOC);
$result->free();
print_r($rows);
$query = "SELECT * FROM tbl WHERE id = ?";
$stmt = $this->db->prepare($query);
echo $this->db->error;
$stmt->close();

Echo's "Commands out of sync; you can't run this command now" and throws Fatal error: Call to a member function close() on a non-object

$rows outputs as expected

Any ideas?

Answer 1

This is actually how mysqli works to be honest! Things happen in batches of 2, which means you need to clear the last result before doing anything else. If you don't -> ERROR!

You can achieve that by using next_result(), example follows:

while($this->_mysqli->next_result());

..then carry on with your code successfully. The above example would fit just after the fetch_assoc_ process is run before returning the array.

(I know this was asked 3 months ago, but rather late than never ;)