PHP PDO：在一个foreach循环中绑定值，关于插入重复键更新

Question

I'm using two foreach loops to bind the values to the positional parameters, one for INSERT INTO, the other for DUPLICATE KEY UPDATE. I was trying to reduce it to just one loop. But array_merge didn't work. Can someone tell me how can I get it done by using just one loop?

Here's an example:

  $facts = array("age","race");

  $ifield = "";
  $ufield="";
  $comma = "";
  foreach($facts as $f)
  {  
    $$f  = (isset($_POST["$f"])?$_POST["$f"]:"");
    if ($$f)
    {    
      $ifield .= $comma.$f;
      $ufield .= "{$comma}{$f} = ?";
      $comma = ",";
    }
  }

  $i = explode(',',$ifield);

  $in  = str_repeat('?,', count($i) - 1) . '?';   

  $sql .= "INSERT INTO `users` (user_id,model_no,{$ifield}) VALUES(?,?,$in)";

  $sql .= " ON DUPLICATE KEY UPDATE $ufield";

  $users = $dbh->prepare($sql);

  $user_id = $_SESSION['user_id'];
  $model_no = $_POST["model_no"];

  $users->bindValue(1,$user_id);
  $users->bindValue(2,$model_no);

  /****************************************************************/    
  $i = 3;

  foreach($facts  as $f)   
  {
    global $$f;

     if ($$f)
     {    
       $users->bindValue($i++, $$f);

     }
  }

  foreach($facts  as $f)
  {
    global $$f;

     if ($$f)
     {    
       $users->bindValue($i++, $$f);

     }
  }

  $users->execute();

Answer 1

You don't need to use bound parameters in the ON DUPLICATE KEY clause. Use:

$ufield = "$comma $f = VALUES($f)";

Using VALUES(columndname) in the ON DUPLICATE KEY clause makes it use the value that would have been inserted into that column. See the documentation