Foreach循环中的PDO和MySQL更新 [英] PDO and MySQL UPDATE in Foreach Loop

问题描述

我有这个问题，让我完全难住。这是我的执行模式。

  foreach（）{
 foreach（）{
 
 
 
 
 
 $ b我使用PDO与MySQL，当我执行更新查询连接，它们在循环内相互干扰。我知道他们单独从一套评论出来，然后执行另一套。这里是我正在处理的代码模板：
 $ b $ $ p $ $ $ $ c $ $ 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（）;由于某些原因，在嵌套的foreach循环中执行两个查询时，$ set_data1中的数据是取消了$ set_data2。我试着用$ stmt-> closeCursor（）关闭游标。我试过使用一个单一的语句来准备，只是绑定新的参数的声明。我已经尝试设置$ stmt和$ db实例为空，然后重新实例化它们无济于事。我试过在查询中使用CASE THEN和IF条件...什么也没有。任何问题的信息将是美好的。我不知道PDO是否在循环中的同一个表上调用UPDATES时有错误，因为我从来没有在其他地方遇到这个问题。对于初学者，你使用 bindParam（）像它是 bindValue（），它们是完全不同的。
 
 
 
没有看到从哪里得到数组值，有点难以确定发生了什么。它看起来像你提供的信息可能不是你正在使用的代码，并已被修改，特别是关于foreach循环和data_array变量，因为你所描述的是一个BindParam常见的问题，所以这是假设我将继续工作。如果是这种情况，通常提供实际的代码片段，包括所使用变量的初始化和发现问题的块，而不仅仅是这些块中的代码。
 
 
这是另一个答案为什么，基本上确保通过引用您的foreach循环的值部分或您将bindParams更改为bindValues传递。您还需要确保在这里使用了两个单独的对象，而不是一个，因为您正在运行 bindParam（）方法每次调用 execute（）。
 
 
所以，比如说，代码结构没有改变它应该可能是因为这是全部在循环中，只有执行应该在一个循环）：
 
 pre $ $ c $ $ set_data1 =更新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（）; 
  

更好的方法是这样的（请记住这只是一个普通的例如）：
$ b $ pre $ $ $ $ $ $ $ $ $ $ $ $ set $'$' data_id;

$ data_array = array（array（'data_status'=> $ dataStatus1，'data_id'=> $ dataId），array（'data_status'=> $ dataStatus2，'data_id'=> ; $ dataId2））;
/ *这只是表示包含数据状态和数据ID的多维数组（或多维对象），在将数据传递到循环之前应该处理和决定数据状态和数据ID。 * /

$ 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（）';
}

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!

解决方案

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()';
}

这篇关于Foreach循环中的PDO和MySQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！