English
English
When using PHP's mysqli extension for database operations, we usually use the mysqli::$affected_rows property to get the number of rows affected after executing an SQL statement. This property returns the actual number of database records changed after performing INSERT, UPDATE, or DELETE operations. However, sometimes even when data is successfully inserted, mysqli::$affected_rows returns 0, as if the insert didn’t take effect. So, what causes this situation? This article analyzes several common reasons to help you quickly identify the problem.
When using INSERT IGNORE or REPLACE SQL statements, if the inserted record matches an existing record or violates a uniqueness constraint (such as a primary key or unique index), the database will not perform the insert. In this case, although the SQL statement was executed, no rows were actually inserted into the database, so mysqli::$affected_rows returns 0. Note that this does not mean the insert failed—it simply means no new record was added.
<span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice')"</span></span><span>;
</span><span><span class="hljs-title function_ invoke__">mysqli_query</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>, </span><span><span class="hljs-variable">$sql</span></span><span>);
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli_affected_rows</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>); </span><span><span class="hljs-comment">// Returns 0 if a user with ID 1 already exists</span></span><span>
</span></span>When using AUTO_INCREMENT fields, mysqli::$affected_rows may return 0 if the inserted record does not cause the field to update. For example, if the data you insert already includes a unique-constrained field with an existing value, the insert operation succeeds but does not create a new row, and affected_rows will return 0.
<span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"INSERT INTO users (id, name) VALUES (1, 'Alice')"</span></span><span>;
</span><span><span class="hljs-title function_ invoke__">mysqli_query</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>, </span><span><span class="hljs-variable">$sql</span></span><span>);
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli_affected_rows</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>); </span><span><span class="hljs-comment">// Returns 0 if a record with ID 1 already exists</span></span><span>
</span></span>Sometimes an INSERT statement is executed but returns 0 in affected_rows, especially during batch inserts. If the inserted data already exists and does not violate uniqueness constraints, the INSERT executes successfully, but the database remains unchanged, and affected_rows will be 0.
<span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"INSERT INTO users (id, name) VALUES (1, 'Alice')"</span></span><span>;
</span><span><span class="hljs-title function_ invoke__">mysqli_query</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>, </span><span><span class="hljs-variable">$sql</span></span><span>);
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli_affected_rows</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>); </span><span><span class="hljs-comment">// Returns 0 if a record with ID 1 exists and does not violate uniqueness constraints</span></span><span>
</span></span>When using transactions, insert operations only take effect after the transaction is committed. If an error occurs during the transaction or it is manually rolled back, even if the INSERT appears to execute successfully, no data is actually inserted into the database, and affected_rows may return 0.
<span><span><span class="hljs-title function_ invoke__">mysqli_begin_transaction</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>);
<p></span>$sql = "INSERT INTO users (name) VALUES ('Alice')";<br>
mysqli_query($link, $sql);</p>
<p>// Suppose some condition triggers a rollback<br>
mysqli_rollback($link);</p>
<p>echo mysqli_affected_rows($link); // Returns 0 because the transaction was rolled back<br>
</span>When using mysqli to execute SQL, sometimes we fail to check whether the database connection is successful or fail to capture errors from SQL execution. If mysqli_query() fails, affected_rows is likely 0 because no operation was actually performed. Therefore, it is crucial to check the result of each query and ensure there are no SQL errors or database connection issues.
<span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"INSERT INTO users (name) VALUES ('Alice')"</span></span><span>;
</span><span><span class="hljs-variable">$result</span></span><span> = </span><span><span class="hljs-title function_ invoke__">mysqli_query</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>, </span><span><span class="hljs-variable">$sql</span></span><span>);
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$result</span></span><span> === </span><span><span class="hljs-literal">false</span></span><span>) {
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"SQL execution failed: "</span></span> . </span><span><span class="hljs-title function_ invoke__">mysqli_error</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>);
} </span><span><span class="hljs-keyword">else</span></span><span> {
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli_affected_rows</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>);
}
</span></span>Certain database settings can also cause affected_rows to return 0. For example, MySQL configurations related to sql_mode or transaction may affect query behavior. If STRICT_TRANS_TABLES mode is enabled and the inserted data violates certain restrictions, MySQL may not perform the insert, returning 0.
Encountering mysqli::$affected_rows returning 0 affected rows when using PHP's mysqli extension is not uncommon. Usually, this indicates that the SQL execution did not change any data in the database. To troubleshoot this issue, you can examine the SQL statement itself, database settings, transaction handling, and data integrity. Understanding these common causes can help you quickly identify and resolve the problem.
Related Tags:
mysqli
