CODEWITHSUNDEEP
phpmysqlformspdo
Jaxchief
Jaxchief

Reputation: 146

MySQL update, skip blank fields with PDO

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.

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

Upvotes: 4

Views: 3523

Answers (4)

david strachan
david strachan

Reputation: 7228

It is easier to use unnamed parameters for dynamic queries in PDO and passing them as an array in execute(). The statement will not be executed unless at least 1 parameter is passed along with the id. I have left in the echo of the derived statement and the dump of the array.

Example statement

UPDATE `links` SET `Program` = ? , `Cost` = ? WHERE `Id` = ?

Example array

Array ( [0] => 2 [1] => 3 [2] => 2 )


if(isset($_GET['a'])){
$id = $_GET['a'];
$program = isset($_GET['b']) ? $_GET['b'] : NULL;
$descr = isset($_GET['c']) ? $_GET['c'] : NULL;
$cost= isset($_GET['d']) ? $_GET['d'] : NULL;
$params =array();
$sql = "UPDATE `links` SET "; //SQL Stub
if (isset($program)) {
    $sql .= " `Program` = ? ,";
    array_push($params,$program);   
}
if (isset($descr)) {
    $sql .= " `Descr` = ? ,";
    array_push($params,$descr); 
}
if (isset($cost)) {
    $sql .= " `Cost` = ? ,";
    array_push($params,$cost);  
}
$sql = substr($sql, 0, -1);//Remove trailing comma
if(count($params)> 0){//Only execute if 1 or more parameters passed.
    $sql .= " WHERE `Id` = ? ";
    array_push($params,$id);
    echo $sql;//Test
    print_r($params);//Test
    $stmt = $dbh->prepare($sql);
        $stmt->execute($params);
}
}

Upvotes: 0

Class
Class

Reputation: 3160

Something like this should work

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

Upvotes: 6

Rajesh Paul
Rajesh Paul

Reputation: 7009

Change the statement:

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

As follows:

$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');

Upvotes: 1

Awlad Liton
Awlad Liton

Reputation: 9351

Check post field for empty : It will skip update query if any field data is empty.

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 Update all fields except empty: 

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

Upvotes: 0

Related Questions