MySQL update using PDO and prepared statement not working

Question

I'm having a strange problem with php PDO and mysql.

I have the following table:

create table test_table ( id integer, value text );

with a single row:

insert into test_table values (1, "asdf");

when I try to update this single row with a prepared statement, I got different behaviours depending on the syntax I use:

// connection to db (common code)
$dbh = new PDO("mysql:host=localhost;dbname=test", "myuser", "mypass");

=========================================================

// WORKING
$q = 'update test_table set id=1, value='.rand(0,99999).' where id=1';
$dbh->exec($q);

=========================================================

// WORKING
$q = 'update test_table set value=:value where id=:id';
$par = array(
    "id" => 1,
    "value" => rand(0,99999)
  );
$sth = $dbh->prepare($q, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute($par);

=========================================================

// NOT WORKING
$q = 'update test_table set id=:id, value=:value where id=:id';
$par = array(
    "id" => 1,
    "value" => rand(0,99999)
  );
$sth = $dbh->prepare($q, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute($par);

In the third case, on my server, the update is not performed on the row, without any reason nor exception/error. On another server it works. I' not looking for answers like: "and so? use the first or second implementation" :)

I'm asking why the third implementation doesn't work because I'm migrating a lot of code from a server to another one (it's not my code) and it contains a lot of queries like this one and I have no time to fix them one by one. On the current server it works and on the new one it doesn't.

Why the third implementation doesn't work? Is there any kind of configuration for php/pdo/mysql which could affect this behaviour?

Thanks.

Update: Tried to sqeeze out error messages:

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

try {
// NOT WORKING
  $q = 'update test_table set id=:id, value=:value where id=:id';
  $par = array(
    "id" => 1,
    "value" => rand(0,99999)
  );
  $sth = $dbh->prepare($q, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  print_r($sth);
  print_r($dbh->errorInfo());
} catch(PDOException $e) {
  echo $e->getMessage();
}

$sth->execute($par);

Executing this code on both servers (working and not working):

PDOStatement Object
(
    [queryString] => update test_table set id=:id, value=:value where id=:id
)
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)

Update 2

Look at this further test:

create table test_table ( value0 text, value text );
insert into test_table values ("1", "pippo");

// NOT WORKING

$q = 'update test_table set value0=:value0, value=:value where value0=:value0';
$par = array(
    "value0" => "1",
    "value" => rand(0, 839273)
);

create table test_table ( value0 text, value text );
insert into test_table values ("pippo", "1");

// WORKING

$q = 'update test_table set value=:value, value0=:value0 where value=:value';
$par = array(
    "value" => "1",
    "value0" => rand(0, 839273)
);

Incredible, isn't it? My suspect now is that exists some special update beahaviour specifically made for the first column of every table on PDO+placeholder handling.

Answer 1

http://php.net/manual/en/pdo.prepare.php states:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

As this indicates, the likely reason behind your code working on one server and not another is that PDO::ATTR_EMULATE_PREPARES is disabled on the server which the code fails on. As the documentation says, this attribute effectively removes the restriction preventing you from using a parameter marker of the same name twice (along with some other restrictions).

Answer 2

Facing the same issue

with update statement (insert and select working well with array set)

i found out execute by binding Params :

$qry = $bd->prepare("UPDATE users SET name = :name WHERE id = :id");
$qry->bindParam(':name','user1');          
$qry->bindParam(':id','1');   
$qry->execute();

Answer 3

try {
     $db = new PDO('mysql:host=localhost;dbname=vendor_management_system', 'root', '');
     $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}




$fields[] = 'car_name';
$fields[] = 'model_no';
$fields[] = 'maker_id';
$fields[] = 'dealer_id';

$values[] = "testcar";
$values[] = "no#1";
$values[] = 2;
$values[] = 4;



echo SQLUpdate('car_details', $fields, $values,'car_id = 32 and car_name = "testname"',$db);




//START: SQLUpdate
//$fields = array of fields in DB
//$values = array of values respective to the $fields
 function SQLUpdate($table,$fields,$values,$where,$db) {



  //build the field to value correlation
  $buildSQL = '';
  if (is_array($fields)) {

        //loop through all the fields and assign them to the correlating $values
        foreach($fields as $key => $field) :
      if ($key == 0) {
            //first item
            $buildSQL .= $field.' = ?';
          } else {
            //every other item follows with a ","
            $buildSQL .= ', '.$field.' = ?';
          }
    endforeach;

  } else {
    //we are only updating one field
        $buildSQL .= $fields.' = :value';
  }

  $prepareUpdate = $db->prepare('UPDATE '.$table.' SET '.$buildSQL.'
WHERE '.$where);

  //execute the update for one or many values
  if (is_array($values)) {
    $affected_rows=$prepareUpdate->execute($values);
    return $affected_rows;
  } else {
    $affected_rows=$prepareUpdate->execute(array(':value' => $values));
    return $affected_rows;
  }


  //record and print any DB error that may be given
  $error = $prepareUpdate->errorInfo();
  if ($error[1]) print_r($error);

} 
//END: SQLUpdate

Answer 4

$maker_id=1;
$stmt = $db->prepare("UPDATE car_details SET maker_id=?");
$affected_rows=$stmt->execute(array($maker_id));
echo $affected_rows.' were affected';