Why Am I Getting a 'Commands Out of Sync' Error with MySQL Prepared Statements in PHP?

Commands Out of Sync, MySQL Prepared Statement Issue

You are encountering an error "Commands out of sync" while attempting to run two MySQL queries using mysqli and PHP. This error occurs when there are multiple unbuffered queries in progress, and commands are not executed in the correct order.

In your code, you have prepared two queries: $countQuery and $recordsQuery. However, by default, mysqli uses unbuffered queries for prepared statements. This means that results for each query must be fetched before executing the next query.

Cause of the Error

Your code attempts to execute $recordsQuery within the loop of $countQuery. Since the results of $countQuery are not fetched, mysqli treats this as an unbuffered query and proceeds to execute $recordsQuery out of order. This causes the "Commands out of sync" error.

Solution

To resolve this issue, you have two options:

• Fetch the first query's results: Fetch the results of $countQuery into an array using $numRecords->get_result(). This will buffer the results and allow you to execute $recordsQuery afterward.
• Use mysqli store_result(): Alternatively, you can use the $stmt->store_result() method before fetching results. This will buffer the results internally without requiring an additional array.

Example with store_result()

if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s", $brand);
    $numRecords->execute();
    $numRecords->store_result(); // Buffer the query results
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = $data->num_rows;
    // Continue with your code...

Note:

If you use $countQuery to count rows before $recordsQuery, ensure that you free the result of $countQuery using $numRecords->free_result() before executing $recordsQuery. This is not necessary if you use store_result.

The above is the detailed content of Why Am I Getting a 'Commands Out of Sync' Error with MySQL Prepared Statements in PHP?. For more information, please follow other related articles on the PHP Chinese website!