UPDATE an array using PDO

Question

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.

You can probably use `SET firstName = IFNULL(?, firstName)`, see http://stackoverflow.com/questions/2675968/sql-how-can-i-update-a-value-on-a-column-only-if-that-value-is-null — mario, Mar 08 '13 at 22:54
@mario Thanks for the tip! I'm not sure that will help my case, if `IFNULL(NULL,10);` returns `10`, I'd still need to know the value for `10`, correct? I'm simply trying to stop the `UPDATE` from happening if the value of an item is `NULL` — Paul Dessert, Mar 08 '13 at 23:04

score 6 · Accepted Answer · answered Mar 08 '13 at 22:56

6

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

answered Mar 08 '13 at 22:56

Wouter J

41,455
15
107
112

Thanks, I think that might do the trick. I'll play around with it and see. – Paul Dessert Mar 08 '13 at 23:05
1

Worked great, thanks! I just modified this `$updates = array_filter($updates, function ($value) { return null !== $value; });` to `$updates = array_filter($updates, 'strlen');` based on the comment here: http://www.php.net/manual/en/function.array-filter.php#111091 – Paul Dessert Mar 09 '13 at 00:19
1

@relentless ok, great! And you also know what is happening? I think that's more important than if the code works or not. – Wouter J Mar 09 '13 at 08:44
@WouterJ very nice `array_filter` trick, I was using just `foreach($array as $key => $value) if($value === null) unset($array[$key]);` until this solution. Thanks – dmnc Jul 25 '13 at 09:45
@relentless solution with `strlen` is unnecessary, try just `$updates = array_filter($updates);` ... omitting the callback does the same trick :) – dmnc Jul 25 '13 at 09:52

score 1 · Answer 2 · answered Mar 08 '13 at 22:57

The below can be optimized:

$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++;
    }
  }
}

UPDATE an array using PDO

2 Answers2