如果参数有值，则更新记录 - SQL，PHP

Question

My current code updates all values, and if there is an empty parameter it updates the corresponding field on the table to null. I want to update only the fields that have a value.

This is my code:

$sql = "
    UPDATE drinks SET
    name        = :name,          // Mojito -> Update it
    description = :description,   // Lorem ipsum.. -> Update it
    glass_id    = :glass_id,      // NULL -> do not update
    video_url   = :video_url      // NULL -> do not update
    WHERE id = '$drinkId'
";

try {
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql);
    $stmt->bindParam(':name', $drinkName);
    $stmt->bindParam(':description', $description);   
    $stmt->bindParam(':glass_id', $glassId);
    $stmt->bindParam(':video_url', $videoUrl);
    $stmt->execute();



// Close databse
$db = null;


} catch(PDOException $e) {
    echo $e;
}

Answer 1

you don't want to update those 2 fields when they are empty.

this code check if a field is null and only when it is not null it will add the field to the query, also the same for binding the value.

$sql = 'UPDATE drinks SET';
$sql = sql . 'name = :name,';
$sql = sql . 'description = :description';

if (!is_null($glassId))
    $sql = sql . ',glass_id = :glass_id';

if (!is_null($videoUrl))
    $sql = sql . ',video_url = :video_url';

$sql = sql . ' WHERE id = :drinkId';

try {
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql);
    $stmt->bindParam(':name', $drinkName);
    $stmt->bindParam(':description', $description);
    $stmt->bindParam(':drinkId', $drinkId);

    if (!is_null($glassId))
    $stmt->bindParam(':glass_id', $glassId);

    if (!is_null($videoUrl))
    $stmt->bindParam(':video_url', $videoUrl);

    $stmt->execute();

    // Close databse
    $db = null;


} catch(PDOException $e) {
    echo $e;
}