PDO update - keep original values

Question

I have following UPDATE statement:

 "UPDATE table SET value=:value, symbol=:symbol WHERE id=:id";

value is always set, but symbol doesn't have to be (meaning, that if user doesn't update symbol, it will stay the same)

corresponding php code:

$stmtDS->bindParam("value", $value);
if (isset($symbol))
    $stmtDS->bindParam("symbol", $symbol);

Problem is, that if symbol is not set, there's an error with

Invalid parameter number: number of bound variables does not match number of tokens

So is there any way how can I keep the original value without making another select query to find out the original value?

Answer 1

You are going to have to build different queries. However something like below should help you building the dynamic query:

<?php

$updatedValues = array(
    'value' => $value,
);

if (isset($symbol)) {
    $updatedValues['symbol'] = $symbol;
}

$query = 'UPDATE table SET';
$separator = '';
foreach ($updatedValues as $key => $_) {
    $query .= ' ' . $separator . $key . ' = :' . $key;
    $separator = ',';
}
$query .= ' WHERE id = :id';

$stmt = $connection->prepare($query);
$stmt->execute(array_merge($updateValues, array('id', $id)));

This method provides the most flexibility imho.