English
English
In PHP, when working with databases using the mysqli extension, the $mysqli->affected_rows property is very commonly used. It tells you how many rows were affected by the most recent SQL statement executed. However, sometimes you might encounter $mysqli->affected_rows returning -1. What does this actually mean? This article will walk you through several common situations that cause it to return -1, helping you better understand and debug your code.
mysqli::$affected_rows returns the number of rows affected by the last executed INSERT, UPDATE, or DELETE statement. For example:
<?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
<p>$mysqli->query("UPDATE users SET active = 1 WHERE id = 10");<br>
echo $mysqli->affected_rows; // Outputs the number of affected rows<br>
?><br>
If the update query executes successfully, $affected_rows will return a non-negative integer representing the number of rows actually modified. However, if an error occurs or in certain special cases, the return value may be -1.
If your SQL statement fails to execute—for example, due to syntax errors or non-existent tables—$affected_rows will return -1. At this point, you should check the error message to diagnose the issue:
<?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
<p>$result = $mysqli->query("UPDATE non_existing_table SET active=1");</p>
<p>if ($result === false) {<br>
echo "Query failed, error message: " . $mysqli->error;<br>
echo "Affected rows: " . $mysqli->affected_rows; // Outputs -1 here<br>
}<br>
?><br>
$affected_rows only applies to modification operations such as INSERT, UPDATE, and DELETE. If you execute a SELECT, SHOW, or other queries that do not change data, $affected_rows will return -1.
<?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
<p>$mysqli->query("SELECT * FROM users");<br>
echo $mysqli->affected_rows; // Outputs -1 because SELECT does not affect rows<br>
?><br>
If you are using transactions and execute a modification operation without committing, $affected_rows may return an unusual value. Make sure your transactions are properly committed.
<?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
<p>$mysqli->autocommit(false);<br>
$mysqli->query("UPDATE users SET active=1 WHERE id=5");</p>
<p>echo $mysqli->affected_rows; // May still show the correct value<br>
$mysqli->commit();<br>
?><br>
Uncommitted transactions might cause changes not to actually take effect. Although affected_rows usually returns correctly, in special cases it could be misleading.
When using prepared statements, in some cases $stmt->affected_rows may be -1, for example, if you execute statements that do not support returning affected rows, or if the query fails.
<?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
<p>$stmt = $mysqli->prepare("UPDATE users SET active=1 WHERE id=?");<br>
$id = 10;<br>
$stmt->bind_param("i", $id);<br>
$stmt->execute();</p>
<p>echo $stmt->affected_rows; // May be -1, indicating an execution error<br>
?><br>
Always check $stmt->error to confirm whether there are errors.
Check if the SQL execution was successful
Determine if the return value is false, and use $mysqli->error to get detailed error information.
Confirm the SQL statement type
affected_rows only applies to modification operations; SELECT queries will always return -1.
Check transaction status
Ensure that the transaction for modification operations has been committed.
Handle errors with prepared statements
Check whether $stmt->execute() succeeds and handle errors promptly.
mysqli::$affected_rows returning -1 usually means the last SQL execution failed or that the executed statement does not affect any rows.
You must judge the result in combination with error messages and the type of SQL statement.
When coding, get into the habit of checking $mysqli->error or $stmt->error to quickly locate problems.
By following these methods, you can more accurately understand and handle cases where mysqli::$affected_rows returns -1, improving your code’s robustness and debugging efficiency.
Related Tags:
mysqli
