PDO and MySQL UPDATE in Foreach Loop

Question

I'm having this problem that has me completely stumped. Here's my execution pattern. The PDO calls are nested in foreach loops.

foreach(){
    foreach(){

    }
}

I'm using PDO with MySQL, and when I execute UPDATE queries back-to-back, they interfere with each other within the loop. I know they work individually from commenting out one set, and executing the other. Here's the code template I'm dealing with:

$set_data1 = "UPDATE data_table
              SET data_status = 'PROCESSED' 
              WHERE 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'
              WHERE data_id = :data_id2";

$stmt = $db->prepare($set_data2);

$stmt->bindParam(':data_id2', $data_array2['data_id'], PDO::PARAM_INT);

$stmt->execute();

For some reason, when executing both queries within the nested foreach loops, the data from $set_data1 is being cancelled out by $set_data2. I've tried closing the cursor with $stmt->closeCursor(); I've tried using a single statement to prepare, and just binding new parameters to the statement. I've tried setting the $stmt and $db instances to null, and then re-instantiating them to no avail. I've tried using CASE THEN and IF conditionals within the query... nothing. Any info on what the problem is would be wonderful. I don't know if PDO has an error with calling UPDATES on the same table within a loop, because I've never had this problem elsewhere. Thanks in advance!

Answer 1

For starters you're using bindParam() like it's bindValue(), they're quite different.

Without seeing where you're getting your array values from it's a little harder to see what's going on with certainty. It looks like the information you're providing is likely not actually the code you're using and has been modified, particularly regarding the foreach loops and the data_array variables as what you're describing is an issue common with BindParam so that's the assumption I'll be working on. If that is the case, it's in general a good idea to provide actual code snippets including the initialization of the variables used and the blocks where the issue is found rather than just the code in those blocks.

Here's another answer with why, basically make sure your passing by reference the value portion of your foreach loop or your changing the bindParams to bindValues. You'll also want to make sure that you're using two separate objects here instead of one if you plan to continue using this structure since you're running both bindParam() methods each time you call execute().

So something like, say, were the code structure not changed (which it should probably be since this is all in loops and only Execute should be in a loop):

$set_data1 = "UPDATE data_table
          SET data_status = 'PROCESSED' 
          WHERE 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'
              WHERE 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 to do this though would be something like (keep in mind this is just a general example):

$set_data = "UPDATE data_table
          SET data_status = :data_status 
          WHERE data_id = :data_id";

$data_array = array( array('data_status' => $dataStatus1, 'data_id' => $dataId), array('data_status' => $dataStatus2, 'data_id' => $dataId2) ); 
/* this is just to represent a multidimensional array (or a multidimensional object) containing the data status and the data id which should be handled and decided before you pass them into a 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 as $name => $val ) {
    $data_status = $val['data_status'];
    $data_id = $val['data_id'];
    $stmt->execute()';
}

Answer 2

Although it's extremely hard to tell what is your real code from that sketch you provided,

Here is an simplified example of what are you doing:

$apples = 4;
$apples = 18;
echo $apples;

how much apples it will it print and why does it "interfere" with initial amount?