MySQL- Commands out of sync you can’t run this command now

Today, I coded a piece of PHP where there were 2 consecutive calls to 2 MYSQL stored procedures (select statements).
The first “call” queried the database and populated a page area with the result, and the second call came right after the first call to populate another area with another result set. I thought everything was fine. My Procedures were ok I knew it because I had tried them out on MySQL Workbench, my PHP code was also ok. It was not a complicated code piece anyway. I run the page code and encountered this error:
Fatal error: Call to a member function fetch_array() on a non-object in…

I said what the heck?? I investigated the code and everything still looked ok to me. Then to see the underlying “real” problem, I used “trigger_error” method of MySQLi like so:

When I re-run the page the real error showed itself:

Notice: Commands out of sync; you can’t run this command now in…

After some investigation, I found this on MySQL reference page:

B.5.2.14 Commands out of sync
If you get Commands out of sync; you can’t run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between. (MySQL 5.0 Ref. page)

It is pretty clear. I need to store the results of the first query before running the next query, I mean before calling the second stored procedure.

I used the function below to achieve this:

Call this function after executing and looping through the results of the first query like so “clearResults($myCon);”.

Here I store the results from the “first call” (and free them) before making the second call (running the second query).

This solved my problem. Now the page works like a charm.

Do your part against global warming.
Good Luck,
Serdar

Leave a Reply

Your email address will not be published. Required fields are marked *