【mysqli::$affected_rows】Common errors after inserting data

gitbox 2025-05-26

1. Basic usage of mysqli::$affected_rows

 <?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");

$sql = "INSERT INTO users (username, email) VALUES ('testuser', '[email protected]')";
$mysqli->query($sql);

echo "Number of affected rows：" . $mysqli->affected_rows;
?>

In the above code, a new data is inserted, and $affected_rows should return 1, indicating that a row is inserted.

2. Common misunderstandings and solutions

Misunderstanding 1: Return 0 after inserting data, it is obviously successful

Cause : If you are executing the INSERT ... ON DUPLICATE KEY UPDATE statement and don't actually update any data, then $affected_rows may return 2 or 0, depending on the MySQL version.
illustrate :
- In MySQL 5.1 and earlier, the number of rows affected by ON DUPLICATE KEY UPDATE is 1 (insert) or 2 (update).
- After MySQL 5.7, if no data is actually updated, 0 will be returned.
solve :
- You can check $mysqli->info for details, or adjust the logic according to your needs.

Misconception 2: When executing multiple SQLs, the number of affected rows is inaccurate

 <?php
$sql = "INSERT INTO users (username) VALUES ('user1');";
$sql .= "INSERT INTO users (username) VALUES ('user2');";

$mysqli->multi_query($sql);
do {
    if ($result = $mysqli->store_result()) {
        $result->free();
    }
    echo "Number of affected rows：" . $mysqli->affected_rows . "\n";
} while ($mysqli->more_results() && $mysqli->next_result());
?>

Note : $affected_rows is a query that was executed for the last time. If you execute multiple statements in batches, you must process the results one by one to accurately obtain the number of affected rows for each statement.

Misunderstanding 3: $affected_rows is invalid for SELECT statement

$affected_rows is only valid for write operations (INSERT, UPDATE, DELETE). The SELECT statement will not affect any row count and returns -1.
If you want to know how many results a SELECT query returns, you should use $result->num_rows .

3. Other factors affecting $affected_rows

Transaction not committed : If the transaction is enabled and not committed, $affected_rows still reflects the actual number of rows affected, but the data is not committed to the database.
Trigger impact : Modification of rows during MySQL trigger execution will not change the value of $affected_rows in PHP.
Connection status : Ensure that mysqli connection is normal, $affected_rows may return -1 in error.

4. Recommended debugging skills

Use $mysqli->error to check for SQL errors.
Use $mysqli->info to view the details of the most recently executed statement.
Turn on MySQL logs or use tools to see if the executed SQL is consistent with the expected one.

5. Summary

mysqli::$affected_rows is an effective tool for detecting the number of rows that affect the write operation, but it is easy to be confused in the following situations:

When using complex SQL (such as ON DUPLICATE KEY UPDATE ), special attention should be paid to the return value rules.
When multiple SQL batches are executed, the results need to be processed one by one.
The SELECT statement does not affect the number of rows, use $result->num_rows instead.
Transactions, triggers, etc. may also affect actual performance.

As long as you understand its working principle and boundary conditions, $affected_rows can help you accurately judge the database operation effect and improve the code robustness.

mysqli::$affected_rows