MySQL更新，使用PDO跳过空白字段 [英] MySQL update, skip blank fields with PDO

问题描述

我想通过下面的表格更新MySQL行.表单按原样运行良好，但是，如果我将字段保留为空白，则会将MySQL中的字段也更改为空白.我想更新sql，但跳过任何空白的字段.

I would like to update a MySQL row via the form below. The form works great as is but, if I leave a field blank, it changes the field in MySQL to blank as well. I would like to update the sql but skip over any fields that are blank.

我已经阅读了几种方法，但它们似乎不合逻辑.IE.在sql字符串本身中使用if语句.(让MySQL完成应在PHP中完成的工作).

I have read a few ways of doing this but they didn't seem logical. I.e. using if statements in the sql string itself. (Having MySQL do the work that should be done in PHP).

if($_SERVER['REQUEST_METHOD'] != 'POST')
{   
 echo '<form method="post" action="">
    ID: <input type="text" name="a" /><br>
    Program: <input type="text" name="b" /><br>
    Description: <textarea row="6" cols="50" name="c"></textarea><br>
    Cost: <input type="text" name="d"><br>
    <input type="submit" value="Add Link" />
 </form>';
}


try {
  $dbh = new PDO($dsn, $user, $pass);
  $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
  $stmt = $dbh->prepare('UPDATE links SET Program = :program , Descr = :descr, Cost = :cost WHERE Id= :id');

  $stmt->bindParam(":id", $_POST["a"]);
  $stmt->bindParam(":program", $_POST["b"]);
  $stmt->bindParam(":descr", $_POST["c"]);
  $stmt->bindParam(":cost", $_POST["d"]);
  $stmt->execute();
  if (!$stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());}
    $dbh = null;

  }
}catch (PDOException $e) {
  print "Error!: " . $e->getMessage() . "<br/>";
  die();
}

推荐答案

类似的方法应该起作用

.
.
.
$q = array();
if(trim($_POST["b"]) !== ""){
    $q[] = "Program = :program";
}
if(trim($_POST["c"]) !== ""){
    $q[] = "Descr = :descr";
}
if(trim($_POST["d"]) !== ""){
    $q[] = "Cost = :cost";
}
if(sizeof($q) > 0){//check if we have any updates otherwise don't execute
    $query = "UPDATE links SET " . implode(", ", $q) . " WHERE Id= :id";
    $stmt = $dbh->prepare($query);
    $stmt->bindParam(":id", $_POST["a"]);
    if(trim($_POST["b"]) !== ""){
        $stmt->bindParam(":program", $_POST["b"]);
    }
    if(trim($_POST["c"]) !== ""){
        $stmt->bindParam(":descr", $_POST["c"]);
    }
    if(trim($_POST["d"]) !== ""){
        $stmt->bindParam(":cost", $_POST["d"]);
    }
    $stmt->execute();
}
.
.
.

这篇关于MySQL更新，使用PDO跳过空白字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！