php - PDO and MySQL UPDATE in Foreach Loop -

i'm having problem has me stumped. here's execution pattern. pdo calls nested in foreach loops.

foreach(){     foreach(){      } } 

i'm using pdo mysql, , when execute update queries back-to-back, interfere each other within loop. know work individually commenting out 1 set, , executing other. here's code template i'm dealing with:

$set_data1 = "update data_table               set data_status = 'processed'                data_id = :data_id1";  $stmt = $db->prepare($set_data1);  $stmt->bindparam(':data_id1', $data_array1['data_id'], pdo::param_int);  $stmt->execute();  $set_data2 = "update data_table               set data_status = 'pending'               data_id = :data_id2";  $stmt = $db->prepare($set_data2);  $stmt->bindparam(':data_id2', $data_array2['data_id'], pdo::param_int);  $stmt->execute(); 

for reason, when executing both queries within nested foreach loops, data $set_data1 being cancelled out $set_data2. i've tried closing cursor $stmt->closecursor(); i've tried using single statement prepare, , binding new parameters statement. i've tried setting $stmt , $db instances null, , re-instantiating them no avail. i've tried using case , if conditionals within query... nothing. info on problem wonderful. don't know if pdo has error calling updates on same table within loop, because i've never had problem elsewhere. in advance!

for starters you're using bindparam() it's bindvalue(), they're quite different.

without seeing you're getting array values it's little harder see what's going on certainty. looks information you're providing not code you're using , has been modified, particularly regarding foreach loops , data_array variables you're describing issue common bindparam that's assumption i'll working on. if case, it's in general idea provide actual code snippets including initialization of variables used , blocks issue found rather code in blocks.

here's answer with why, make sure passing reference value portion of foreach loop or changing bindparams bindvalues. you'll want make sure you're using 2 separate objects here instead of 1 if plan continue using structure since you're running both bindparam() methods each time call execute().

so like, say, code structure not changed (which should since in loops , execute should in loop):

$set_data1 = "update data_table           set data_status = 'processed'            data_id = :data_id1";  $stmt = $db->prepare($set_data1);  $stmt->bindvalue(':data_id1', $data_array1['data_id'], pdo::param_int);  $stmt->execute();  $set_data2 = "update data_table               set data_status = 'pending'               data_id = :data_id2";  $stmt2 = $db->prepare($set_data2);  $stmt2->bindvalue(':data_id2', $data_array2['data_id'], pdo::param_int);  $stmt2->execute(); 

a more optimal way though (keep in mind general example):

$set_data = "update data_table           set data_status = :data_status            data_id = :data_id";  $data_array = array( array('data_status' => $datastatus1, 'data_id' => $dataid), array('data_status' => $datastatus2, 'data_id' => $dataid2) );  /* represent multidimensional array (or multidimensional object) containing data status , data id should handled , decided before pass them loop. */  $stmt = $db->prepare($set_data);  $data_status = null; $data_id = null;  $stmt->bindparam(:data_status, $data_status); $stmt->bindparam(:data_id, $data_id);  foreach( $data_array $name => $val ) {     $data_status = $val['data_status'];     $data_id = $val['data_id'];     $stmt->execute()'; }