CODEWITHSUNDEEP
phpstored-proceduresmysqli
etuardu
etuardu

Reputation: 5516

PHP: «Commands out of sync» if I mysqli::query() again after a call to a results-giving stored procedure

I have a stored procedure in my db that returns all records in a table:

CREATE PROCEDURE showAll()
BEGIN
    SELECT * FROM myTable;
END

The SP works just as expected. But, if I call it in a php script and then I try to query the database again, it always fail:

// $mysqli is a db connection

// first query:

if (!$t = $mysqli->query("call showAll()"))
    die('Error in the 1st query');

while ($r = $t->fetch_row()) {
    echo $r[0] . "<br>"; // this is ok
}

$t->free(); // EDIT (this doesn't help anyway)

// second query (does the same thing):

if (!$t = $mysqli->query("SELECT * from myTable"))
    die('Error in the 2nd query'); // I always get this error

while ($r = $t->fetch_row()) {
    echo $r[0] . "<br>";
}

Notable, if I swap the two queries (i.e. I call the stored procedure at the end) it works without any error. To close() the result before the second query doesn't help. Some hints?

EDIT: mysqli::error() is: «Commands out of sync; you can't run this command now».

Upvotes: 7

Views: 2845

Answers (2)

Deepan Prabhu Babu
Deepan Prabhu Babu

Reputation: 912

@jymian, Your answer is not clear. Please express clearly.

After freeing result set using $t->free(),

Call $mysqli->next_result().

The above call will prepare for next result set and you wont get out of sync errors.

Upvotes: 2

jymian
jymian

Reputation: 51

The comments on the php.net/manual entry for mysqli.query have been updated, and now include an answer for this. To summarize, call $mysqli->next_result() after $t->close(). Kudos to petrus.jvr!

Link: http://www.php.net/manual/en/mysqli.query.php#102904

Upvotes: 5

Related Questions