CODEWITHSUNDEEP
phpmysql
Alex Moore
Alex Moore

Reputation: 249

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

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?

Upvotes: 22

Views: 33030

Answers (9)

user3404872
user3404872

Reputation: 111

This is because mySql is checking whether the field made any change or not, To over come this, I created a new TINY field 'DIDUPDATE' in the table.

added this to your query 'DIDUPDATE=DIDUPDATE*-1'

it looks like.

    $updateQuery = "UPDATE myTable SET foo=1, DIDUPDATE=DIDUPDATE*-1  WHERE bar=2";
    mysql_query($updateQuery);

    if (mysql_affected_rows() > 0) 
    {
            echo "affected!";
    }
    else 
    {
            echo "not affected"; 
    }

it works fine!!!

Upvotes: 1

DLastCodeBender
DLastCodeBender

Reputation: 327

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);

Upvotes: 1

Gladson Samuel S
Gladson Samuel S

Reputation: 21

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

Upvotes: 2

Mrigesh Raj Shrestha
Mrigesh Raj Shrestha

Reputation: 620

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.

Upvotes: 24

steveukx
steveukx

Reputation: 4368

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

Upvotes: 1

David
David

Reputation: 1144

You work this?

$timestamp=mktime();
$updateQuery = "UPDATE myTable SET foo=1, timestamp={$timestamp} WHERE bar=2";
mysql_query($updateQuery);

$updateQuery = "SELECT COUNT(*) FROM myTable WHERE timestamp={$timestamp}";
$res=mysql_query($updateQuery);
$row=mysql_fetch_row($res);
if ($row[0]>0) {
    echo "affected!";
}
else {
    echo "not affected";
}

Upvotes: 0

Marko Vasic
Marko Vasic

Reputation: 301

I think you need to try something else in update then foo=1. Put something totaly different then you wil see is it updating or not without if loop. then if it does, your if loop should work.

Upvotes: 0

Stefan
Stefan

Reputation: 2068

Try connecting like this:

$connection = mysql_connect(...,...,...);

and then call like this

if(mysql_affected_rows($connection) > 0)
    echo "affected";
} else { ...

Upvotes: 0

Innogen
Innogen

Reputation: 49

Was My Tought !

I was just about to tell to check if the function's being called many times !

Just a little advice:

try using isset() & POST / GET or something like that;

if ( isset( $_POST['Update'] == 'yes' ) ) :

// your code goes here ...

endif;

Hope it was clear and useful, Ciao :)

Upvotes: -1

Related Questions