CODEWITHSUNDEEP
phpmysqlpdo
user1762087
user1762087

Reputation: 460

PDO update - keep original values

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?

Upvotes: 1

Views: 398

Answers (2)

PeeHaa
PeeHaa

Reputation: 72662

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.

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157896

you need to make your query conditional as well

$data = array("value" => $value, "id" => $id);

$symbol_set = '';
if (isset($symbol))
    $symbol_set = ", symbol=:symbol";
    $data["symbol"] => $symbol;
}

$sql = "UPDATE table SET value=:value $symbol_set WHERE id=:id";
$stm = $pdo->prepare($sql);
$stm->execute($data);

It is also a good idea to implement a distinct placeholder for the update data.

It can dramatically reduce the amount of code, to just one line actually, (using safeMysql as an example wrapper):

$data = array("value" => $value);
$db->query("UPDATE table SET ?u WHERE id= ?i", $data, $id)

Upvotes: 2

Related Questions