Resolving 'Commands out of sync' Error in MySQLi: Causes and Fixes

Error Background and Problem Description

When executing database queries with PHP's MySQLi extension, developers often encounter the 'Commands out of sync; you can't run this command now' error. This typically occurs when attempting to run multiple queries simultaneously without properly handling the results of the previous query. For instance, in the original code, the user uses MySQLi prepared statements but fails to buffer queries, leading to synchronization issues with subsequent queries.

Analysis of Error Causes

MySQLi uses unbuffered queries by default for prepared statements, meaning query results are not immediately transferred from the server to the client. If a new query is initiated before the previous query's results are fully fetched, this error is triggered. Additionally, the original code contains SQL syntax issues, such as improper use of placeholders in LIKE clauses, which exacerbate the problem.

Core Solutions

The primary solution involves using the store_result() method to buffer query results. For example, after executing a prepared statement, call $stmt->store_result() to store results on the client side, allowing subsequent queries to proceed. Below is a corrected code example:

<?php
$con = mysqli_connect("localhost", "user", "password", "db");
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}
$con->query("SET NAMES 'utf8'");

$brand = "o";
$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
    $searchTerm = "%" . $brand . "%";
    $numRecords->bind_param("s", $searchTerm);
    $numRecords->execute();
    $numRecords->store_result(); // Buffer results
    $rowcount = $numRecords->num_rows;
    $numRecords->close();

    $rows = getRowsByArticleSearch("test", "Auctions", " ");
    // Other logic handling
} else {
    echo "Query preparation failed: " . $con->error;
}

function getRowsByArticleSearch($searchString, $table, $max) {
    $con = mysqli_connect("localhost", "user", "password", "db");
    $recordsQuery = "SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y') AS shortDate FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ? ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s')" . $max;
    if ($getRecords = $con->prepare($recordsQuery)) {
        $searchTerm = "%" . $searchString . "%";
        $getRecords->bind_param("s", $searchTerm);
        $getRecords->execute();
        $getRecords->store_result(); // Buffer results
        $getRecords->bind_result($ARTICLE_NO, $USERNAME, $ACCESSSTARTS, $ARTICLE_NAME, $shortDate);
        $rows = array();
        while ($getRecords->fetch()) {
            $rows[] = [
                'ARTICLE_NO' => $ARTICLE_NO,
                'USERNAME' => $USERNAME,
                'ACCESSSTARTS' => $ACCESSSTARTS,
                'ARTICLE_NAME' => $ARTICLE_NAME,
                'shortDate' => $shortDate
            ];
        }
        $getRecords->close();
        return $rows;
    } else {
        return [];
    }
}
?>

In this example, we corrected the SQL syntax by properly binding placeholders in the LIKE clause and used store_result() to ensure query results are buffered, preventing the commands out of sync error.

Additional Technical Insights

Referencing other answers, such as using mysql_next_result() in C for handling multiple result sets, similarly in PHP, if stored procedures or complex queries return multiple result sets, one can use mysqli_more_results() and mysqli_next_result() to iterate through them. For example:

while ($con->more_results()) {
    $con->next_result();
    if ($result = $con->store_result()) {
        $result->free();
    }
}

This ensures all pending results are processed, avoiding synchronization errors.

Summary and Best Practices

In summary, the 'Commands out of sync' error stems from unbuffered queries and improper result handling. By employing store_result(), correctly binding parameters, and managing multiple result sets, this issue can be effectively resolved. It is recommended to always buffer query results in development and verify SQL syntax to enhance code robustness and performance.