10

I am trying to write a PHP-MySQL database processor that is somewhat intelligent. When this processor decides it needs to make an update, I want to report if it was really successful or not. I thought I could use mysql_affected_rows...

// Example:
// After running query "UPDATE mytable SET name='Test' WHERE ID=1"
$result = mysql_affected_rows();
if ($result >= 1) { /* Success */ }

If, for example, there was no row with ID=1, then $result would be 0.

However, it turns out that PHP's mysql_affected_rows is the actual affected rows, and may be still be 0 if the row exists but name was already "Test". (The PHP docs even say this is the case).

If I run this in the command line, I get the following meta information about the query:

Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Is there any way for me to get that "Rows matched" value in PHP instead of the affected rows?

[Edit]: I should note that I know I can run a separate query, but I'd like to not do that, for the sake of performance.

Nicole
  • 32,841
  • 11
  • 75
  • 101
  • I'm not offering a duplicate, but this might be of interest to researchers that are doing INSERT-or-UPDATE processes. https://stackoverflow.com/a/29787059/2943403 – mickmackusa Apr 29 '21 at 23:33

2 Answers2

16

From the MySQL documentation for mysql_affected_rows:

For UPDATE statements, if you specify the CLIENT_FOUND_ROWS flag when connecting to mysqld, mysql_affected_rows() returns the number of rows matched by the WHERE clause. Otherwise, the default behavior is to return the number of rows actually changed.

With mysqli, you can specify the CLIENT_FOUND_ROWS using mysqli::real_connect.

$db = mysqli_init();
$db->real_connect('host', 'username', 'password', 'dbname', '3306', null, MYSQLI_CLIENT_FOUND_ROWS);

In PDO, the constant is named PDO::MYSQL_ATTR_FOUND_ROWS

$db = new PDO('mysql:dbname=mydatabase;host=myhost', 'username', 'password', array(
    PDO::MYSQL_ATTR_FOUND_ROWS => true
));

With the old and deprecated MySQL extension, you can specify the CLIENT_FOUND_ROWS passing the value 2 as the 5th parameter for mysql_connect (source).

Vinicius Braz Pinto
  • 8,209
  • 3
  • 42
  • 60
  • This is great. While it doesn't offer me a way to distinguish between the two (so I'm going to test it before accepting the answer) it seems to me to be the preferable condition in my case. Thanks! – Nicole May 28 '10 at 00:46
  • The PHP lib is such a transparent wrapper over the MySQL C api lib, you really can read the MySQL docs for a better understanding of the PHP/MySQL api – bobobobo May 12 '13 at 10:01
  • @Vinicius, If you set that option, it now **only** returns *"number of matched rows, not the number of affected rows"*. How can we access both *affected_rows* and *matched_rows*? – Pacerier Feb 09 '15 at 07:40
  • @Pacerier with `mysqli` you can use `mysqli_info()`, as mentioned in the other answer (http://php.net/manual/en/mysqli.info.php). With PDO I'm not sure if there's a solution without making an extra query. – Vinicius Braz Pinto Feb 09 '15 at 13:21
  • @ViniciusPinto, It seems like a hacky fix (due to the need to parse the string and such). – Pacerier Feb 09 '15 at 22:47
  • @ViniciusPinto, Dang, this question would have been solved if only there's some way to [change the behavior of `mysql_affected_rows` between different queries](http://stackoverflow.com/questions/5289475/get-number-of-rows-matched-by-update-query-with-php-mysqli/5289535#comment45417545_5289535). As of now, parsing the output of `mysql_info();` is the best solution if you have multiple queries that use `mysql_affected_rows` differently. – Pacerier Feb 16 '15 at 23:49
7

You can also use the function

$variable = mysql_info();

That function retrieves a string like this:

Rows matched: 1 Changed: 0 Warnings: 0

You can work with strings functions on your variable to extract the substring with the number of rows that matched and you should have it!

Josh Darnell
  • 11,304
  • 9
  • 38
  • 66
Alessandro Villa
  • 79
  • 1
  • 1