Why Am I Getting the 'Commands out of sync' Error in My MySQLi PHP Code?

PHP MySQL Error: Command out of sync

Your PHP code attempts to execute two MySQL queries using mysqli, but encounters the error "Commands out of sync; you can't run this command now". This is caused by mysqli prepared statement queries being in non-buffered mode by default.

Source of error

mysqli executes queries in unbuffered mode by default, which means that the result set is not stored in memory but streamed directly to the client. Since you used two queries, the first query executes and streams the result set. However, before getting the results from the first query, you try to execute the second query. This results in a "command out of sync" error because mysqli requires you to get the results of the first query before executing the second query.

Solution: Buffered Query

To solve this problem, you need to buffer the query. You can use the store_result() method of the prepared statement object:

<code class="language-php">$numRecords->execute();
$numRecords->store_result();</code>

By buffering queries, mysqli stores the result set of the first query in memory, allowing you to retrieve it later without conflicting with the execution of the second query.

Other notes

In your code, you are also trying to execute different queries inside a foreach loop:

<code class="language-php">$result = $con->query($recordsQuery);
$rows = array();
while($row = $result->fetch_assoc()) {
    $rows[] = $row;
}</code>

This is actually not necessary since you already store the results of the query in a variable named $rows . You can use this variable directly in the loop to access the fetched rows.

By implementing these changes, you should be able to execute MySQL queries without encountering "command out of sync" errors.

The above is the detailed content of Why Am I Getting the 'Commands out of sync' Error in My MySQLi PHP Code?. For more information, please follow other related articles on the PHP Chinese website!