CODEWITHSUNDEEP
phpmysqlarrayspdo
marked-down
marked-down

Reputation: 10418

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

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?

Upvotes: 1

Views: 710

Answers (1)

Jeremy Kendall
Jeremy Kendall

Reputation: 2869

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);

Upvotes: 4

Related Questions