mysql_affected_rows() returns 0 for UPDATE statement even when an update actually happens

Question

I am trying to get the number of rows affected in a simple mysql update query. However, when I run this code below, PHP's mysql_affected_rows() always equals 0. No matter if foo=1 already (in which case the function should correctly return 0, since no rows were changed), or if foo currently equals some other integer (in which case the function should return 1).

$updateQuery = "UPDATE myTable SET foo=1 WHERE bar=2";
mysql_query($updateQuery);
if (mysql_affected_rows() > 0) {
    echo "affected!";
}
else {
    echo "not affected"; // always prints not affected
}

The UPDATE statement itself works. The INT gets changed in my database. I have also double-checked that the database connection isn't being closed beforehand or anything funky. Keep in mind, mysql_affected_rows doesn't necessarily require you to pass a connection link identifier, though I've tried that too.

Details on the function: mysql_affected_rows

Any ideas?

Answer 1

Newer versions of MySQL are clever enough to see if modification is done or not. Lets say you fired up an UPDATE Statement:

UPDATE tb_Employee_Stats SET lazy = 1 WHERE ep_id = 1234

Lets say if the Column's Value is already 1; then no update process occurs thus mysql_affected_rows() will return 0; else if Column lazy had some other value rather than 1, then 1 is returned. There is no other possibilities except for human errors.

Answer 2

The following notes will be helpful for you,

mysql_affected_rows() returns

• +0: a row wasn't updated or inserted (likely because the row already existed, but no field values were actually changed during the UPDATE).

• +1: a row was inserted

• +2: a row was updated

• -1: in case of error.

mysqli affected rows developer notes

Answer 3

Have you tried using the MySQL function ROW_COUNT directly?

mysql_query('UPDATE myTable SET foo = 1 WHERE bar = 2');
if(mysql_result(mysql_query('SELECT ROW_COUNT()'), 0, 0)) {
  print "updated";
}
else {
  print "no updates made";
}

More information on the use of ROW_COUNT and the other MySQL information functions is at: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count

Answer 4

mysqli_affected_rows requires you to pass the reference to your database connection as the only parameter, instead of the reference to your mysqli query. eg.

$dbref=mysqli_connect("dbserver","dbusername","dbpassword","dbname");

$updateQuery = mysqli_query($dbref,"UPDATE myTable SET foo=1 WHERE bar=2");

echo mysqli_affected_rows($dbref);

NOT

echo mysqli_affected_rows($updateQuery);