MySQL更新，跳过带PDO的空白字段

Question

我想通过下面的表单更新MySQL行。该表单工作得很好，但是，如果我将字段保留为空白，它也会将MySQL中的字段更改为空白。我想更新sql，但跳过任何空白字段。

我读过一些这样做的方法，但它们似乎不合逻辑。即在sql字符串本身中使用if语句。(让MySQL来完成在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();
}

Answer 1

像这样的东西应该能起作用

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

Answer 2

更改声明：

$stmt = $dbh->prepare('UPDATE links SET Program = :program , Descr = :descr, Cost = :cost WHERE Id= :id');

详情如下：

$stmt = $dbh->prepare('UPDATE links SET Program = IF(trim(:program)="", Program, :program) , Descr = IF(trim(:descr)="", Descr, :descr), Cost = IF(trim(:cost)="", Cost, :cost) WHERE Id= :id');

Answer 3

检查post字段是否为空:如果任何字段数据为空，它将跳过更新查询。

If( $_POST["a"] && $_POST["b"] && $_POST["c"] && $_POST["d"]){

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


    }

Option2更新除空以外的所有字段：

try {
            $sql ="UPDATE links SET ";
            if($_POST["a"])
                $sql .=" Program = :program ,";
            if($_POST["b"])
                $sql .=" Descr = :descr ,";
            if($_POST["c"])
                $sql .=" Cost = :cost ,";

            $sql = rtrim($sql,',');
            $dbh = new PDO($dsn, $user, $pass);
            $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $stmt = $dbh->prepare($sql);
            if($_POST["a"])
                $stmt->bindParam(":id", $_POST["a"]);
            if($_POST["b"])
                $stmt->bindParam(":program", $_POST["b"]);
            if($_POST["c"])
                $stmt->bindParam(":descr", $_POST["c"]);

            $stmt->execute();

            if (!$stmt) {
                echo "\nPDO::errorInfo():\n";
                print_r($dbh->errorInfo());}
            $dbh = null;

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