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....

Atrip

@spencer7593 ahhh I apologize, the statement in the question was misconstrued. What I want is simply ONE statement to update all the fields alltogether... The psudocode statement will be updated in a second. I do not require an individual query for each field. I just want one singular update for all fields – Atrip

I apologize for misunderstanding the question you asked. You can't conditionally specify a column to be updated in an UPDATE statement. IF the column is named in the SET list, then you have to supply a value for it. You can use an expression following the assignment operator, and that expression can conditionally return the current value of the column. There's no performance advantage to retrieving the current values, comparing to the new values, and then deriving an UPDATE statement to assign only the changed values. It's going to be faster to just update all the columns. – Footton

TO ALL: THANK YOU FOR YOUR RESPONSES. I was looking at it from a performance point of view but from your answers, I understand that a single update statement will be most useful. Thank you again – Atrip

Answer 1

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()

Moffatt

Answer 2

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."

Saddle

Trigger seem to be triggered either way #6296813 – Mechanize

Answer 3

Run a single statement to update the row.

Firstly, what's the unique identifier for a row in the users table, is there a unique userid or username? You'll want a WHERE clause on the UPDATE statement so that only that row will be updated.

The normative pattern for an UPDATE statement to update several columns in a single row is like this:

UPDATE users
   SET col2 = 'value'
     , col3 = 'another value'
     , col4 = 'fi'
 WHERE idcol = idvalue ;

To use a prepared statement with PDO, the SQL text could look something like this, if you use named placeholders:

UPDATE users
   SET col2 = :col2_value
     , col3 = :col3_value
     , col4 = :col4_value
 WHERE idcol = :id_value

Or this, if you use positional notation for the placeholders:

UPDATE users
   SET col2 = ?
     , col3 = ?
     , col4 = ?
 WHERE idcol = ?

(My personal preference is to use the named placeholders, rather than positional, but either will work.)

This is how I'd do it, run the prepare, then the bind_param, and then the execute.

$sql = "UPDATE users
           SET col2 = :col2_value
             , col3 = :col3_value
             , col4 = :col4_value
         WHERE idcol = :id_value ";

$stmt = $dbh->prepare($sql);
$stmt->bindParam(':col2_value', $col2_val, PDO::PARAM_STR);
$stmt->bindParam(':col3_value', $col3_val, PDO::PARAM_STR);
$stmt->bindParam(':col4_value', $col4_val, PDO::PARAM_STR);
$stmt->bindParam(':id_value'  , $id_val, PDO::PARAM_STR);
$stmt->execute();

To do something different, to dynamically create the SQL text, and adjust the bindParam calls, that would add unnecessary complexity to the code. There's no performance advantage to doing that; when that UPDATE statement runs, MySQL has to lock the row, store a new copy of the row. It doesn't really save anything (aside from a few bytes of data transfer) to avoid sending a column value that hasn't changed.

Footton

Answer 4

If you realy want to use cases, read this.

There is no reason to do it in your case, as stated from @spencer7593 in the comments:

That's WAY more overhead... roundtrips to the database, parsing the statement, developing an execution plan, executing the statement, obtaining locks, returning a status, client checking the status, etc. That's just seems an all-around inefficient approach.

I assume that any RDBMS is smart enough, to notice, that Caches etc should not be recalculated (if nothing changes), if that is the problem.

Enaenable