CODEWITHSUNDEEP
phpmysqlpdo
ltvie
ltvie

Reputation: 973

PDO update table with existing value?

I built PHP 2 year ago and now i want to change all about database to PDO,i have some problem with update table. I use this function to update table.

public function update($tabel, $fild = null ,$where = null)
    {
         $update = 'UPDATE '.$tabel.' SET ';
         $set=null; $value=null;
         foreach($fild as $key => $values)
         {
             $set .= ', '.$key. ' = :'.$key;
             $value .= ', ":'.$key.'":"'.$values.'"';
         }
         $update .= substr(trim($set),1);
         $json = '{'.substr($value,1).'}';
         $param = json_decode($json,true);

         if($where != null)
         {
            $update .= ' WHERE '.$where;
         }

         $query = parent::prepare($update);
         $query->execute($param);
         $rowcount = $query->rowCount();
         return $rowcount;
    }

everything work fine using this

$updatefild = array('count' => 20);
$where = "id = '123'"; 

echo $db->update("mytable",$updatefild, $where);

but i get problem when i want to update row with existing row, in mysql_query I usually use

mysql_query("update mytable set count=count+1 where id='123'");

how i achieve that use PDO ?

thanks

Upvotes: 3

Views: 3556

Answers (1)

Devon Bessemer
Devon Bessemer

Reputation: 35337

First, why are you using JSON just to decode it into an array? That is confusing.

Secondly, if you were trying to add a number to an existing field, you don't even need prepare().

You could just do

PDO->query("update mytable set count=count+".intval($int)." where id='123'");

If you were doing prepare, you could do:

$stmt = PDO->prepare("update mytable set count=count+:count where id='123'");
$stmt->execute(array(':count' => 1));

or

$stmt = PDO->prepare("update mytable set count=count+? where id='123'");
$stmt->execute(array(1));

Edit: You wouldn't be able to do it with how your function is written as you can't bind column names. PDO will quote it as a standard string. You would have to find a work around, possibly including the =count in the field somehow.

Upvotes: 3

Related Questions