不更新数组的php更新到mysql

Question

我有一个应该更新行的函数，无论在日志中发生什么，都不会出现错误。据我所见，这应该是可行的。我从一个update用户函数中获取了这个函数，并试图为新特性模仿它。

下面是向其发送的数据数组。

$data = array('id' =>  $vid, 'name' => $vname, 'logo' => $vlogo, 'info' => $vinfo, 'site' => $vsite, 'est' => $vest);

这篇文章很有效，我正在更新页面上做一个转储。所以他们确实被安排好了。我想可能是因为这个功能。任何洞察力都会很棒！

public static function updateCompany($toUpdate = array(), $company = null){
  self::construct();
   if( is_array($toUpdate) && !isset($toUpdate['id']) ){
    if($company == null){
    echo "No company ID set!";
  }
  $columns = "";
  foreach($toUpdate as $k => $v){
    $columns .= "`$k` = :$k, ";
  }

  $sql = self::$dbh->prepare("UPDATE companys SET {$columns} WHERE `id` = :id");
  $sql->bindValue(":id", $company);
  foreach($toUpdate as $key => $value){
    $value = htmlspecialchars($value);
    $sql->bindValue(":$key", $value);
  }
  $sql->execute();

  }else{
  return false;
 }
}



$vid = $_POST["idnum"];
$vname = $_POST["name"];
$vlogo = $_POST["logo"];
$vinfo = $_POST["info"];
$vsite = $_POST["site"];
$vest = $_POST["est"];

Answer 1

您的update SQL无法工作，因为您在update值集中有逗号。

你看，你在没有考虑的情况下附上逗号：

  $columns = "";
  foreach($toUpdate as $k => $v){
    $columns .= "`$k` = :$k, ";
  }

然后，最后的SQL将如下所示：

UPDATE companys SET `name`=:name, `logo`=:logo, WHERE `id`=:id

你注意到WHERE前面的逗号了吗？它不应该在那里！

因此，您应该更新代码如下：

$columns = "";
foreach($toUpdate as $k => $v){
  if ($columns != "") $columns .= ",";
  $columns .= "`$k` = :$k ";
}

这应该管用的。

Answer 2

我可能会尝试在execute()方法中使用bind数组，因为您只是在绑定这些值(假设这是PDO)。另一个特性是使用数组来组装列部分，并在使用时进行内爆。

public static function updateCompany($toUpdate = array(), $company = null)
    {
        # Also this may supposed to be: self::__construct(); ?? Notice the "__" before "construct"
        self::construct();

        if(is_array($toUpdate) && !isset($toUpdate['id'])) {
            if(empty($company)){
                # Throw exception, catch it in a parent wrapper
                throw new \Exception("No company ID set!");
                # Stop, no sense in continuing if an important part is missing
                return;
            }

            foreach($toUpdate as $k => $v){
                $bKey        = ":{$k}";
                # I would create a bind array here
                $bind[$bKey] = $value;
                # I generally save to an array here to implode later
                $columns[]   = "`{$k}` = {$bKey}";
            }
            # Add the id here
            $bind[":id"] = $company;
            # I would use a try here for troubleshooting PDO errors
            try {
                # Create sql with imploded columns
                $sql = self::$dbh->prepare("UPDATE companys SET ".implode(",",$columns)." WHERE `id` = :id");
                # Throw the bind array into the execute here
                $sql->execute($bind);
            }
            catch(\PDOException $e) {
                # I would only die here to see if there are any errors for troubleshooting purposes
                die($e->getMessage());
            }
        } else {
            return false;
        }
    }

Answer 3

或者不必注意最后的逗号，请尝试如下：

$columns = array();
foreach($toUpdate as $k => $v){
    $columns[] = "`$k` = :$k";
}

$sql = self::$dbh->prepare("UPDATE `companys` SET ".implode(',', $columns)." WHERE `id` = :id");