UPDATE array using PDO

Question

I am creating a multi-step form for my users. They will be allowed to update all or all of the fields. So I need to send the values, check if they are set, and if so, run UPDATE . Here is what I still have:

 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, initial 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 an UPDATE so that it only updates elements in the array that are not NULL ?

I thought of starting a foreach like this:

  foreach($updates as $key => $value) { if($value == NULL) { unset($updates[$key]); } } 

but how would I write a prepare statement if I'm not sure about the values?

If I go about it completely wrong, point me in the right direction. Thanks.

Answer 1

First of all, use array_filter to remove all NULL values:

 $updates = array_filter($updates, function ($value) { return null !== $value; }); 

Secondly, bind the parameters, which will greatly simplify your life:

 $query = 'UPDATE table SET'; $values = array(); foreach ($updates as $name => $value) { $query .= ' '.$name.' = :'.$name.','; // the :$name part is the placeholder, eg :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 :) 

Answer 2

Below you can optimize:

 $i = 0; $query = array(); foreach($updates as $key => $value) { if ($value != NULL) { $query[] = "{$key} = :param_{$i}"; $i++; } } if (! empty($query)) { $finalQuery = implode(",", $query); $sth = $this->dbh->prepare('UPDATE user SET ' . $finalQuery); $i = 0; foreach($updates as $key => $value) { if ($value != NULL) { $sth->bindParam(':param_'.$i, $value, PDO::PARAM_STR); $i++; } } }