English
English
When using PHP's mysqli extension for database operations, the multi_query function allows us to execute multiple SQL statements at once, which is very convenient when batching data. However, after executing multiple statements, how to correctly obtain the affected number of rows ( affected_rows ) has become a common question.
This article will combine the mysqli::$affected_rows attribute to explain how to use it correctly and issues that need to be paid attention to in the multi_query scenario.
mysqli::$affected_rows is an attribute of the mysqli object, indicating the number of rows affected by the most recent execution of INSERT , UPDATE , or DELETE statements. A single SQL statement is very intuitive to execute:
$mysqli = new mysqli('gitbox.net', 'user', 'password', 'database');
$mysqli->query("UPDATE users SET status=1 WHERE last_login > NOW() - INTERVAL 30 DAY");
echo "Number of affected rows: " . $mysqli->affected_rows;
After executing this update statement, affected_rows is the number of lines affected.
multi_query allows multiple SQL statements to be executed at one time:
$sql = "UPDATE users SET status=1 WHERE id=1;";
$sql .= "UPDATE users SET status=2 WHERE id=2;";
$mysqli->multi_query($sql);
But at this time, what will the value of affected_rows be? The answer is: affected_rows only reflects the number of affected rows corresponding to the result set currently being processed. Since multi_query needs to loop through the results of each statement, it must be retrieved one by one.
Sample code:
$mysqli = new mysqli('gitbox.net', 'user', 'password', 'database');
$sql = "UPDATE users SET status=1 WHERE id=1;";
$sql .= "UPDATE users SET status=2 WHERE id=2;";
$sql .= "DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;";
if ($mysqli->multi_query($sql)) {
do {
// Get the number of affected rows for the current result
echo "Number of affected rows: " . $mysqli->affected_rows . "\n";
// Prepare to process the next result
} while ($mysqli->more_results() && $mysqli->next_result());
} else {
echo "Execution failed: " . $mysqli->error;
}
Here we use the do...while loop to process statements one by one. After each execution, we read the current affected_rows to accurately obtain the number of rows affected by each statement.
multi_query must be used to cooperate with next_result() to traverse the results one by one <br> If you do not call next_result() , you can only get affected_rows of the first statement, and the subsequent statements will be ignored.
Some statements may not affect the number of rows <br> For example, the SELECT statement returns -1 . Only valid for statements that modify data.
Affected_rows may be -1 when an error occurs
If the SQL statement fails to execute, affected_rows will return -1 . At this time, you need to obtain the error message through $mysqli->error .
Pay attention to consistency when handling transactions <br> If multiple statements are executed in a transaction, the affected_rows of a single statement cannot guarantee the overall final state of the transaction and need to be controlled based on transaction commit and rollback.
When multi_query executes multiple SQLs, a loop must be used to cooperate with next_result() to traverse all results.
After each statement is executed, affected_rows is the number of affected rows of the current statement.
Pay attention to detecting error status and the return value of special statements to affected_rows .
When operating a transaction, the number of affected rows is only an auxiliary judgment, and the final state is subject to the transaction commit result.
By mastering the above points, you can correctly and efficiently use mysqli::$affected_rows and multi_query to perform multiple SQL execution and result processing.
