CODEWITHSUNDEEP
phpmysqlpdo
Ted
Ted

Reputation: 3885

How to tell if PDO found a match to my UPDATE query, and updated or not?

UPDATE users SET 'name'='john' WHERE users.ID = 54;

actually all this question is about pdo::rowCount, as I need to know that a match to the query was:
1. found, but need not to update (matching values)
2. found, and updated
3. not found at all, (not updated)

I am looking for a way to tell in PHP one of those three options

Upvotes: 1

Views: 389

Answers (1)

eggyal
eggyal

Reputation: 126025

As documented under UPDATE Syntax:

UPDATE returns the number of rows that were actually changed. The mysql_info() C API function returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE.

In particular, after an UPDATE query mysql_info() returns ER_UPDATE_INFO. The MySQL Command-Line Tool displays such information immediately after a command is issued. For example:

mysql> UPDATE users SET name='john' WHERE ID=54;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MySQLi provides direct access to this function, through mysqli::$info. Even the Original MySQL API provided access thereto, through its own mysql_info() function.

Unfortunately, PDO provides no access to this function—a search of the codebase confirms that it never calls mysql_info().

Alternatively, clients can set the CLIENT_FOUND_ROWS connection flag—then UPDATE will return the number of rows that matched rather than the number that were changed. PDO does provide access to set this connection flag, via the PDO::MYSQL_ATTR_FOUND_ROWS driver-specific attribute.

However this connection flag is of little use to you, as instead of being unable to differentiate between "not found at all" and "found but no update", it will leave you unable to differentiate between "found but no update" and "found and updated".

Thus, as others have already suggested, your only option in this case would be to perform a separate SELECT query to differentiate between the possible conditions. In order to avoid race hazards, you should be careful to perform the SELECT within the same transaction and to use a locking read if reading before updating.

Upvotes: 3

Related Questions