How to know MySQL update command has found the record or not?

Question

Sometimes I need to know MySQL has found the record or no. I don't mean to know is there affected rows or not, just finding.

    $db = database_system::connect();
    $query = "UPDATE member SET username=?,password=? WHERE id=?";
    $stmt = $db->prepare($query);
    $stmt->bind_param("ssi",$this->username,$this->password,$this->id);
    $stmt->execute();
    $stmt->store_result();
    if(!$stmt->affected_rows) throw new Exception("something about");
    $stmt->close();

For example, php code above will throw exception if there is no affected record, but I wanna throw exception when there there is no such user (with that ID).

by adding your where clause you are already checking whether the id exists or not. — mamdouh alramadan, Apr 13 '14 at 22:56
Note: You are using `MySQLi_` not it's predecessor, might help if you declare that. — Jack, Apr 13 '14 at 23:24
Duplicate of http://stackoverflow.com/questions/5289475/get-number-of-rows-matched-by-update-query-with-php-mysqli — Your Common Sense, Apr 14 '14 at 01:32

score 0 · Accepted Answer · answered Apr 13 '14 at 23:01

You need to check it in a separate query. The number of affected rows can return zero if the details haven't changed, too. (i.e. updating a valid user ID with the same username & password as it was before)

So best run a separate select for the user, first.

score 0 · Answer 2 · answered Jun 20 '14 at 00:23

You can set an option to return the number of rows found, instead of the number of rows updated.

For PDO on PHP 5.3 and later:

$p = new PDO($dsn, $u, $p, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));

For MySQLi:

mysqli_real_connect($dsn, $host, $u, $p, $dbname, $port, $socket, MYSQLI_CLIENT_FOUND_ROWS);

How to know MySQL update command has found the record or not?

2 Answers2