Getting the update row_count in mysql using php

Question

This is a very simple example:

I have a table with two columns, id and password. I want to know when an update was done successfully, with success being defined as applied to an existing row.

PHP seems to only offer mysql_affeted_rows(), which doesn't differentiate between 0 rows affected, and 0 rows updated. http://php.net/manual/en/function.mysql-affected-rows.php This is awkward because the return value is dependent on the state of rows at the time of the transaction. In Postgresql you can get it both ways, UPDATE foo SET bar = NULL, will return the amount of rows updated in foo; whereas, UPDATE foo SET bar = NULL WHERE bar IS NOT NULL will return the amount of rows affected, by limiting the update.

Don't bother suggesting a previous SELECT, or a PHP counter var, that assumes there is a row. I want to know of clever ways to get the Pg functionality, without redundant queries.

Powerlord · Accepted Answer · 2009-12-21 16:32:30Z

1

mysql_affected_rows will always return the number of rows updated by an UPDATE.

In other words, it will return the same number of rows for UPDATE foo SET bar = NULL as for UPDATE foo SET bar = NULL WHERE bar IS NOT NULL given the same starting data.

Or at least that's the way it's documented on the manual page you linked to, under the Return Values heading.

answered Dec 21, 2009 at 16:32

Powerlord

89k17 gold badges130 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OMG Ponies Over a year ago

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

Powerlord Over a year ago

OMG Ponies: Exactly, that's the documentation I was referring to. Both queries have the effect of changing all non-null values in that column to null, so both should return the same number of affected rows.

Arno · Accepted Answer · 2009-12-21 16:34:08Z

1

Does the mysqli function num_rows fits your needs ?

answered Dec 21, 2009 at 16:34

Arno

7234 silver badges17 bronze badges

2 Comments

Reynolds Over a year ago

This would have been my answer.

Powerlord Over a year ago

I automatically assumed mysqli_num_rows has the same quirk as mysql_num_rows... that it only works for SELECT queries.

Collectives™ on Stack Overflow

Getting the update row_count in mysql using php

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related