Geek Answers Handbook

MYSO PDO update only if

I have a web program that allows the administrator to update user information ... With that said, I only need updated columns that have really been "updated" ...

I have done quite a bit of research on this, and it seems that all methods use deprecated queries that don't use instructions prepareto exit ...

Can someone please help me with the expression?

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

Here is what I have for the postal 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....
+4
source share
4 answers

, , , , .

"A", "A", , , "B", , .

$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()
+3

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

" , ,  MySQL . "

+15

, .

-, users userid username? WHERE UPDATE, .

UPDATE :

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

PDO, SQL : :

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

, :

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

( - , , .)

, , bind_param, .

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

- , SQL bindParam, . ; UPDATE , MySQL , . ( ), , .

+1

, .

, @spencer7593 :

This method is more overhead ... it accesses the database, analyzing the approval, development of the execution plan, execution of the application, receiving locks, returning status, checking the status of the client, etc. This seems like an inefficient approach.

I assume that any DBMS is smart enough to notice that Caches, etc. should not be recounted (if nothing changes) if this is a problem.

0
source

More articles:


All Articles