How to Fix 'Commands Out of Sync' Errors in PHP MySQL Queries?

Commands Out of Sync: Resolving the Issue in PHP

When working with multiple MySQL queries in PHP using mysqli, you may encounter the error: "Commands out of sync; you can't run this command now." This error arises due to the use of unbuffered queries, which means that the result of a query must be processed sequentially before a subsequent query can be executed.

In the code snippet provided, you execute a query to determine the number of records ($numRecords) in a table based on a LIKE clause. However, the $data variable is still referencing the result of the previous query, which is out of sync.

To resolve this issue, you have two options:

1. Fetch the Result Array:

After executing $numRecords->execute(), fetch the results into an array using $data = $numRecords->get_result(). This will store the results of the query and allow you to continue executing other queries.

2. Use store_result():

Use the store_result() method on the statement object after execution ($numRecords->store_result()). This will instruct mysqli to buffer the results, allowing you to execute subsequent queries without the out of sync error.

Here's a modified code snippet using the get_result() method:

$con->query("SET NAMES 'utf8'");
$brand ="o";
$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE % ? %";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s", $brand);
    $numRecords->execute();
    
    // Fetch the result array into $data
    $data = $numRecords->get_result();
    $rowcount = mysqli_num_rows($data);
    $rows = getRowsByArticleSearch("test", "Auctions", " ");
    $last = ceil($rowcount/$page_rows);
} else {
    print_r($con->error);
}

Note that the commands out of sync error can also occur if you execute a non-query (e.g., INSERT, UPDATE) and then try to execute a query that retrieves data from the same table without first closing the connection or fetching the results of the non-query. Ensure that you have closed the connection or handled the results of previous queries before executing subsequent queries.

The above is the detailed content of How to Fix 'Commands Out of Sync' Errors in PHP MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!