PDO MYSQL Update Only If Different

Question

I have a web program which allows the administrator to update a user's information... With that being said, I only want columns updated which have indeed been 'updated'...

I have done quite a bit of researching on this and it seems that all methods use outdated querys, which do not make use of the prepare statement to escape input...

Can someone please help me with the statement?

Essentially in psuedocode: Update FIRSTNAME if $editedUserdata['firstname'] != FIRSTNAME, LASTNAME if $editedUserData['lastname'] != LASTNAME ...etc...

Here is what I have for the post code...

        $password = sha1($password);
        $editedUserData = array(
              'firstname' => $firstname,
              'lastname' => $lastname,
              'username' => $username,
              'password' => $password,
              'cellphone' => $cellphone,
              'security_level' => $seclvl,
              'email' => $email,
              'direct_phone' => $direct,
              'ext_num' => $extension,
              'is_active' => $userflag
            );

Then it should be something like

$query = $this->db->prepare('UPDATE FIRSTNAME if(?) IS NOT FIRSTNAME, LASTNAME if(?) IS NOT LASTNAME, USERNAME if (?) IS NOT USERNAME.... VALUES (:firstname, :lastname, :username).....'

if ($query -> execute($editedUserData)) {
    more code....

Answer 1

According to MySQL documentation - Ref: (http://dev.mysql.com/doc/refman/5.0/en/update.html)

"If you set a column to the value it currently has, MySQL notices this and does not update it."

Answer 2

Maybe I'm not understanding the problem which you're trying to solve but you don't have to test if field value did change.

If field value is "A" and you put there an "A" it will remain the same otherwise, if you put there a "B" it will be updated as expected

The prepared statement would be something like

$stmt = $dbh->prepare("
    UPDATE table_name
    SET
        field1 = :value1,
        field2 = :value2
    WHERE
        field0 = :key
");

$stmt->bindParam(':value1', $value1, PDO::PARAM_STR);
$stmt->bindParam(':value2', $value2, PDO::PARAM_STR);
$stmt->bindParam(':key', $key, PDO::PARAM_INT);

$stmt->execute()