UPDATE an array using PDO
pdo, php
Solution
First of all, use `array_filter` to remove all NULL values:
$updates = array_filter($updates, function ($value) {
return null !== $value;
});
Secondly, bind parameters, that makes your live a lot easier:
$query = 'UPDATE table SET';
$values = array();
foreach ($updates as $name => $value) {
$query .= ' '.$name.' = :'.$name.','; // the :$name part is the placeholder, e.g. :zip
$values[':'.$name] = $value; // save the placeholder
}
$query = substr($query, 0, -1).';'; // remove last , and add a ;
$sth = $this->dbh->prepare($query);
$sth->execute($values); // bind placeholder array to the query and execute everything
// ... do something nice :)
Problem
I'm creating a multi-step form for my users. They will be allowed to update any or all the fields. So, I need to send the values, check if they are set and if so, run an `UPDATE`. Here is what I have so far: ``` public function updateUser($firstName, $lastName, $streetAddress, $city, $state, $zip, $emailAddress, $industry, $password, $public = 1, $phone1, $phone2, $website,){ $updates = array( 'firstName' => $firstName, 'lastName' => $lastName, 'streetAddress' => $streetAddress, 'city' => $city, 'state' => $state, 'zip' => $zip, 'emailAddress' => $emailAddress, 'industry' => $industry, 'password' => $password, 'public' => $public, 'phone1' => $phone1, 'phone2' => $phone2, 'website' => $website, ); ``` Here is my PDO (well, the beginning attempt) ``` $sth = $this->dbh->prepare("UPDATE user SET firstName = "); //<---Stuck here $sth->execute(); $result = $sth->fetchAll(PDO::FETCH_ASSOC); return $result; ``` Basically, how can I create the `UPDATE` statement so it only updates the items in the array that are not `NULL`? I thought about running a `foreach` loop like this: ``` foreach($updates as $key => $value) { if($value == NULL) { unset($updates[$key]); } } ``` but how would I write the `prepare` statement if I'm unsure of the values? If I'm going about this completely wrong, please point me in the right direction. Thanks.