Updating an unknown number of unknown fields with unknown values using PDO?

Question

I'm trying to update a record into my table where I don't know ahead of time the fields that will be updated, how many will be updated, or what values those fields will hold - some of them will be set to null.

I've chosen to log the changes between what the record was beforehand and what the record is after alteration rather than simply update the entire record (considering only one field might be changing at any one time, it seems pointless to do this).

These logged changes are held in my $changes array, where the key is the field name and the value is the new value the field needs to hold. I've tried looking into using question mark notation to record unknown values, but this alone cannot account for the variances in how many fields might be updated, and also, I've only seen this notation used with a select query.

Here's what I've got so far:

$dbh->prepare("UPDATE `missions` SET ??? WHERE `mission_id`=:mission_id");
$dbh->bindParam(':mission_id', $mission_id); // Mission ID is constant and WILL NOT change

I understand I can simply loop over my array to bind parameters, but I have no clue how to get my array of changed values into the notation required for question marks. Thoughts and ideas?

Do you need do use prepared statements? Otherwise I would just create the Query as a String and execute that directly. — Matthias Dunkel, Dec 31 '13 at 11:34
@MatthiasDunkel I was under the impression not using prepared statements opened the doors to SQL injection, which I'd like to prevent if given the opportunity. — marked-down, Dec 31 '13 at 11:37
@MatthiasDunkel EVERYONE ALWAYS need to use prepared statements. — Your Common Sense, Dec 31 '13 at 11:38
@YourCommonSense It's not true that you should always use prepared statements. There are exceptions, like this may be. It depends on where the data comes from and if you trust the source... — Matthias Dunkel, Dec 31 '13 at 11:41
@MatthiasDunkel you are wrong. There are no exceptions. What you think is just a delusion, that already led to thousands injections. — Your Common Sense, Dec 31 '13 at 11:43
@YourCommonSense I personally alwas use prepared statements too. But I have seen that developers on purpose dindn't used them so speed up a specific task... But the solution you postet is the right one in this case ;-) — Matthias Dunkel, Dec 31 '13 at 11:47
@MatthiasDunkel first, you don't understand the difference between various implementations of prepared statements. Second, the speed difference is not that impressive as you imagine. — Your Common Sense, Dec 31 '13 at 13:12

score 3 · Answer 1 · answered Dec 31 '13 at 14:37

You're going to have to experiment with this a bit, but this should get you really close:

// Unknown name and number of changes
$changes = array(
    'col1' => 'first',
    'col4' => 'fourth',
    'col7' => 'seventh',
);

$setSql = array();

// Create named params, nicer than positional params
foreach ($changes as $column => $value) {
    $setSql[] = "`{$column}` = :{$column}";
}

// Use implode to create the 'set' string
$setString = implode(', ', $setSql);
var_dump($setString);
// string(46) "`col1` = :col1, `col4` = :col4, `col7` = :col7"

// Add the mission_id to the end of the changes array
$changes['mission_id'] = 1234;

$sql = sprintf('UPDATE `missions` SET %s WHERE `mission_id` = :mission_id', $setString);
var_dump($sql);
// string(101) "UPDATE `missions` SET `col1` = :col1, `col4` = :col4, `col7` = :col7 WHERE `mission_id` = :mission_id"

$stmt = $dbh->prepare($sql);
$stmt->execute($changes);

Perfect : you saved my time ! I should force myself to use sprintf more often because of it's power. Can't understand why it's not already the accepted answer ... — hugsbrugs, May 21 '15 at 15:07

Updating an unknown number of unknown fields with unknown values using PDO?

1 Answers1