MYSQL gives same response '0 rows affected' in different situations

Question

MYSQL update query give same response in different situations

"0 rows affected. (Query took 0.0789 sec)"

1) where clause is not matched

2) where clause is matched but the given value is same to the existing value

ex:
assume that user_id = '86' is not exist in the table

UPDATE `undergraduate` set `faculty` = 'Efac' WHERE `user_id` = '86'

assume that user_id = '86' is exist. But the value 'Efac' is already in there

UPDATE `undergraduate` set `faculty` = 'Efac' WHERE `user_id` = '86'

Question is what is the better way to identify both conditions significantly ?

I'm using : MYSQL :

+-------------------------+
| @@version               |
+-------------------------+
| 5.6.19-0ubuntu0.14.04.1 |
+-------------------------+

PHP : 5.5.9-1 (PDO)

Answer 1

MySQL Command Line

If you are using the MySQL command line tool the output given will let you differentiate the two scenarios.

If the entry doesn't exist:

+---------------+---------+
| user_id       | faculty |
+---------------+---------+
| 80            | abc     |
+---------------+---------+

mysql> UPDATE `undergraduate` set `faculty` = 'Efac' WHERE `user_id` = '86';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

If the entry does exist, but isn't updated:

+---------------+---------+
| user_id       | faculty |
+---------------+---------+
| 80            | abc     |
| 86            | Efac    |
+---------------+---------+

mysql> UPDATE `undergraduate` set `faculty` = 'Efac' WHERE `user_id` = '86';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

If the entry does exist, and is updated:

+---------------+---------+
| user_id       | faculty |
+---------------+---------+
| 80            | abc     |
| 86            | bcd     |
+---------------+---------+

mysql> UPDATE `undergraduate` set `faculty` = 'Efac' WHERE `user_id` = '86';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

PHP - PDO

PDO unfortunately doesn't provide a mechanism to get both rows matched and rows changed for a query, but you can choose which one you want. The default is to return the number of rows changed, but the option can be specified on connection to return the number of rows match instead:

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

PHP - MySQLi

The MySQLi functions can do the same as PDO in terms of choosing between matched and updated:

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

But you can also call the function mysqli_info() / $db->info() (see http://php.net/manual/en/mysqli.info.php) and it will return a string with the matched / updated rows which you could then parse:

Records: 3 Duplicates: 0 Warnings: 0