13

The older mysql extension has the CLIENT_FOUND_ROWS connection flag, but I couldn't find any equivalent for mysqli.

I have an update query and want to know how many rows its where clause matched, not how many were actually changed (as indicated by mysqli -> affected_rows).

The only way I've figured out so far is to parse mysqli -> info (which looks something like Rows matched: 40 Changed: 40 Warnings: 0) with a regex. But that seems hacky.

Core Xii
  • 6,270
  • 4
  • 31
  • 42

6 Answers6

7

I used the following code:

// Parse the digits from the info string that has the following format:
// Rows matched: 0 Changed: 0 Warnings: 0
preg_match_all('!\d+!', $mysqli->info, $m);
return $m[0][0];
Zorayr
  • 23,770
  • 8
  • 136
  • 129
5

it's in the options of mysqli_real_connect.

Also introduced in PDO::MySQL in PHP 5.3.

Pacerier
  • 86,231
  • 106
  • 366
  • 634
Wrikken
  • 69,272
  • 8
  • 97
  • 136
  • Wonder how I missed that, I certainly thought I looked! Thanks. – Core Xii Mar 13 '11 at 13:37
  • Ah, yes, `_real_connect` is seldomly used, usually only by people wanting a specific timeout etc. :) – Wrikken Mar 13 '11 at 14:06
  • 1
    @Wrikken, 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`? Is there a setting we can modify while *halfway* through the connection? – Pacerier Feb 16 '15 at 23:42
  • 1
    @Pacerier: not that I'm aware of (but don't assume I'm right, never needed it). The documentation of [`ROW_COUNT()`](https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_row-count) doesn't bode well for it. Of course, you can just `SELECT` (with an optional `COUNT(*)`) before an update, but this becomes a bit unreliable due to race conditions. However, depending on your transaction isolation level and the table engines involved you can get this reliable again, with _considerable_ overhead. – Wrikken Mar 26 '15 at 16:19
  • @Wrikken, Since a single statement alone is atomic, Why will there be race conditions? – Pacerier Mar 29 '15 at 02:49
  • 1
    @Pacerier: because what I'm saying is the (current PHP) driver does not give you the option to do it in one statement (both affected & matched). It would have to be 2 statements at minimum getting both those values. So, you have 1 `UPDATE` & `SELECT` statement, and unless you use the huge overhead of a [`SERIALIZABLE`](https://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable) transation, or a locking `SELECT`, you aren't guaranteed the records remain the same between those 2 statements.\ – Wrikken Mar 29 '15 at 19:50
2

You could run SELECT COUNT(*) with the same WHERE clause before running the UPDATE. That will give you a count of the number of rows that would be matched.

GordonM
  • 31,179
  • 15
  • 87
  • 129
  • 2
    Why is this being downvoted? This is a perfectly valid solution, doubly so if you're using transactions. – Charles Mar 13 '11 at 16:11
  • 1
    @Charles, Not me, but for something that **only requires one table query**, your solution has one query more. That's an unnecessary waste of resources. – Pacerier Feb 16 '15 at 23:39
0

Also SELECT ROW_COUNT() can give the number of rows affected by update query.

nawfal
  • 70,104
  • 56
  • 326
  • 368
0

I did an explode function on the results returned from the info command and then took index[1].

$affected = explode(":", $connection->info);
$rowsAffected = $affected[1];
Dharman
  • 30,962
  • 25
  • 85
  • 135
sixstring
  • 262
  • 4
  • 10
0

From the official docs, looks like this can be achieved by building the PDO object like this:

new PDO($dsn, $u, $p, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
jverce
  • 36
  • 1
  • 4