PDOStatement::execute() returns true but the data is not updated

Question

I don't know why, this prepared query doesn't work: The following php code is correct (not any warning or notice), but the correponding datas are not updated. For similar update the db are correctly updated, but not for this table.

<?php

  error_reporting(E_ALL);
  ini_set('display_errors', 'On');
  $pdo = new PDO('mysql:dbname=test;host=localhost', 'root', ''); 
  $sql = 'UPDATE `ml_user` SET `username` = :username, `password` = :password, `email` = :email, `active` = :active, `last_login` = :last_login WHERE `id_user` = :id_user';
  $sth = $pdo->prepare($sql);
  var_dump($sth); // object(PDOStatement)#39 (1) { ["queryString"]=> string(165) "UPDATE `ml_user` SET `username` = :username, `password` = :password, `email` = :email, `active` = :active, `last_login` = :last_login WHERE `id_user` = :id_user" }
  $datas = array(
    ':id_user' => 1, ':username' => 'my username',
    ':password' => 'ae25ff724d069dcb1a7fff05616ad6abc1',
    ':email' => '[email protected]',
    ':active' => 1, ':last_login' => 1382990654,
  );
  $res = $sth->execute($datas);
  var_dump($res); // bool(true) 

The sql table

CREATE TABLE `ml_user` (
 `id_user` int(11) NOT NULL AUTO_INCREMENT,
 `active` tinyint(1) NOT NULL DEFAULT '0',
 `email` varchar(127) NOT NULL,
 `username` varchar(32) NOT NULL DEFAULT '',
 `password` char(50) NOT NULL,
 `logins` int(10) unsigned NOT NULL DEFAULT '0',
 `last_login` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_user`),
  UNIQUE KEY `uniq_username` (`username`),
  UNIQUE KEY `uniq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

When execute() returns true it means the statement was "successful," but it doesn't mean it changed anything. It just means there was no error.

• It may have matched zero rows because of conditions in the WHERE clause. That's still considered a "success." You can try a SELECT with the same WHERE clause, and fetch the results, to confirm that it matches rows.

• It may have matched one or more rows, but the values you are setting are already the values on those rows. You can call $sth->rowCount() after you execute, to find out how many rows the UPDATE affected (this may be less than the number of rows it matched).

• If you have more than one copy of this table, you should double-check that the change has been made in the database you are reading. It happens to me sometimes -- I forget to change an application configuration file, and I don't realize I'm updating the wrong database.

---

This turned out to be the problem -- a variation on the third point:

• If you work on more than one server, also double-check you are checking the changes on the correct mysql server.

Answer 2

If there was no error and no data updated then no matching row were found.