PDO更新帮助执行pdo更新

Question

这个函数非常简单：

变量：$table是正在进行更新的表，$fields是表中的字段，$values是从post生成并放入$values数组的，$where是表的索引字段的id值，$indxfldnm是索引字段名

function SQLUpdate($table,$fields,$values,$where,$indxfldnm) {

  //Connect to DB
    $dbaddr = DB_HOST;
    $dbusr = DB_USER;
    $dbpwd = DB_PASSWORD;
    $dbname = DB_DATABASE;

  $db = new PDO('mysql:host='.$dbaddr .';dbname='.$dbname.';charset=UTF8', $dbusr, $dbpwd); 

  //build the fields
  $buildFields = '';
  if (is_array($fields)) {

    //loop through all the fields
    foreach($fields as $key => $field) :
      if ($key == 0) {
        //first item
        $buildFields .= $field;
      } else {
        //every other item follows with a ","
        $buildFields .= ', '.$field;
      }
    endforeach;

  } else {
    //we are only inserting one field
    $buildFields .= $fields;
  }

  //build the values
  $buildValues = '';
  if (is_array($values)) {

    //loop through all the values
    foreach($values as $key => $value) :
      if ($key == 0) {
        //first item
        $buildValues .= '?';
      } else {
        //every other item follows with a ","
        $buildValues .= ', ?';
      }
    endforeach;

  } else {
    //we are only updating one field
    $buildValues .= ':value';
  }
 $sqlqry = 'UPDATE '.$table.' SET ('.$buildFields.' = '.$buildValues.') WHERE `'.$indxfldnm.'` = \''.$where.'\');';

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

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



 echo $sqlqry;
 return $sqlqry;
} 

到目前为止还不错，但是它不能正常工作，在正确的update语句中将值传递到字段中是有问题的，但我不太擅长pdo，如果能设置一点帮助来修复将参数绑定到update中的值的代码，我将非常感激

谢谢

Answer 1

尝试在您的函数中获取此代码

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // execute the query
    $stmt->execute();

    // echo a message to say the UPDATE succeeded
    echo $stmt->rowCount() . " records UPDATED successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

Answer 2

已将代码更改为不同的版本：

这就消除了多值问题。

function SQLUpdate($table,$fields,$values,$where,$indxfldnm) {


  $dbdata = array();
  $i=0;

    foreach ($fields as $fld_nm) 
    {
        if ($i > 0) {
        $dbdata[$fld_nm] = $values[$i]; }   
        $i++;

    } //end foreach
$buildData = '';
foreach ($dbdata as $key => $val) {
    if (empty($val)) {$buildData .= '`'.$key.'` = \'NULL\', ';} else {
    $buildData .= '`'.$key.'` = \''.$val.'\', ';}

}
$buildData = substr($buildData,0,-2);


    $dbaddr = DB_HOST;
    $dbusr = DB_USER;
    $dbpwd = DB_PASSWORD;
    $dbname = DB_DATABASE;
$prepareUpdate ='';
try {
  $db = new PDO('mysql:host='.$dbaddr .';dbname='.$dbname.';charset=UTF8', $dbusr, $dbpwd); 
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec("SET CHARACTER SET utf8"); 

 $sqlqry = 'UPDATE '.$table.' SET '.$buildData.' WHERE `'.$indxfldnm.'` = \''.$where.'\';';

   $prepareUpdate = $db->exec($sqlqry); 
  //execute the update for one or many values
}
 catch(PDOException $e)
    {
    $e->getMessage();
    print_r($e);
    }


 return $sqlqry;


} 
//END: SQLUpdate