Update query with conditional?

database, mysql, pdo, php

Solution

You don't need to do multiple queries. Why don't you just structure your query string based on that conditional, and after that just pass it to your DB adapater for execution ? It could be as follow:

    $pdo = new PDO($dsn, $user, $password);

    $sql = "UPDATE table SET columnname1=:field1, columname2=:field2";

    if ( $update ) $sql .= ",columname3=:field3";

    $sql .= " WHERE key=:key";

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":key", $key, PDO::PARAM_INT);
    $stmt->bindParam(":field1", $field1, PDO::PARAM_STR);
    $stmt->bindParam(":field2", $field2, PDO::PARAM_STR);

    if($update) $stmt->bindParam(":field3", $field3, PDO::PARAM_STR);

    $stmt->execute();

Problem

I'm not sure if this possible. If not, let me know. I have a PDO mysql that updates 3 fields. ``` $update = $mypdo->prepare("UPDATE tablename SET field1=:field1, field2=:field2, field3=:field3 WHERE key=:key"); ``` But I want `field3` to be updated only when `$update3 = true;` (meaning that the update of `field3` is controlled by a conditional statement) Is this possible to accomplish with a single query? I could do it with 2 queries where I update `field1` and `field2` then check the boolean and update `field3` if needed in a separate query. ``` //run this query to update only fields 1 and 2 $update_part1 = $mypdo->prepare("UPDATE tablename SET field1=:field1, field2=:field2 WHERE key=:key"); //if field3 should be update, run a separate query to update it separately if ($update3){ $update_part2 = $mypdo->prepare("UPDATE tablename SET field3=:field3 WHERE key=:key"); } ``` But hopefully there is a way to accomplish this in 1 query?

Original source