MySQL Affected Rows vs Rows Matched

PHP’s mysql_affected_rows() is used to get the number of rows that were “affected” by the last query executed using mysql_query().

Therefore, an UPDATE query that set the value of a record is only “affected” if the value changes. But what if we want to know how many records that UPDATE matched in it’s WHERE clause, regardless if the value changed or not?

The functionality above can be accomplished by specifying the CLIENT_FOUND_ROWS flag when connecting to MySQL. The method mysql_affected_rows() will now return the number of found rows, rather than the number of affected rows.

Specifying the constant CLIENT_FOUND_ROWS did not work for me and resulted in the error:
Warning: mysql_connect() expects parameter 5 to be long, string given.

This was because PHP was not recognizing the constant, for whatever reason. I found the actual value of the constant from the predefined MySQL constants page and hardcoded the value when connecting.

mysql_connect( "host", "username", "password", true, 2 )

Any call to mysql_affected_rows after an UPDATE query will now act as you want it to.

Leave a Reply

Your email address will not be published. Required fields are marked *