Why Do I Get 'Commands out of Sync' Errors in PHP's MySQLi Prepared Statements, and How Can I Fix Them?

Command out of sync error in PHP: Understanding and resolving

Before preparing any query, the query "SET NAMES 'utf8'" must be executed because it sets the character set of the database connection and affects the interpretation of data in subsequent queries.

Since MySQL uses unbuffered queries by default in prepared statements, running multiple queries in a single script can cause problems. mysqli performs immediate queries that require one query to complete before another query can be executed. To resolve this issue, you have two options:

1. Extract the results into an array

This method involves executing the first query, extracting its results into an array, and then executing the second query. The code below demonstrates this:

<code class="language-php">$con = mysqli_connect("localhost", "user", "password", "db");
if (!$con) {
    echo "无法连接到 MySQL 服务器。错误代码：%s\n". mysqli_connect_error();
    exit;
}
// 设置字符集
$con->query("SET NAMES 'utf8'");

// 第一个查询
$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE % ? %";
$numRecords = $con->prepare($countQuery);
$numRecords->bind_param("s", $brand);
$numRecords->execute();
$result = $numRecords->get_result(); // 将结果提取到数组中
$rowcount = $result->num_rows;

// 第二个查询
$rows = getRowsByArticleSearch("test", "Auctions", " ");</code>

2. Buffered query

Use store_result() to instruct mysqli to buffer query results, allowing you to execute multiple queries simultaneously. Do this by adding the following line after the prepare() statement:

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

This will effectively save the query results in a buffer, allowing you to execute a second query without encountering a "command out of sync" error.

The above is the detailed content of Why Do I Get 'Commands out of Sync' Errors in PHP's MySQLi Prepared Statements, and How Can I Fix Them?. For more information, please follow other related articles on the PHP Chinese website!