Update MySQL using an array

Question

Is it possible in PHP using MySQLi to update a table with an array without knowing the field names, how many there is and their values.

I've tried with prepared statements inserting a question mark on key and value field (?=?). I was thinking if it's possible to use () VALUES () in a update query I might have a solution but no such thing exists I guess.

Look at my script:

<?php
    class UserHandler {
        public function updateUserData($array, $id) {
            global $mysqli;
            $stmt = $mysqli->prepare("UPDATE users SET ?=? WHERE id = ?");
            $stmt->bind_param('ssi', $array[0], $array[1], $id);
            $stmt->execute();
            return true;
        }
    }

    $users = new UserHandler;
?>

I want the usage to be like this:

<?php
    $users->updateUserData(array(
        'firstname' => 'Wanda',
        'lastname' => 'Merritt',
        'state' => 'IN'
        'address' => '693 Pearcy Avenue',
        'zipcode' => 46625,
    ), 45);

    $users->updateUserData(array(
        'firstname' => 'Stanley',
        'lastname' => 'Lewis',
        'password' => '123123'
    ), 159);
?>

Answer 1

One query, with prepared statements:

public function updateUserData($array, $id) {
    global $mysqli;
    $query = "UPDATE `users` SET ";
    $parts = array();
    foreach ($array as $key => $value) {
        $parts[] = "`" . $key . "` = ?";
    }
    $query = $query . implode(",", $parts) . " WHERE id = ?";

    $stmt = $mysqli->prepare($query);

    foreach ($array as $key => $value) {
        $stmt->bind_param('s', $value);
    }
    $stmt->bind_param('i', $id);
    $stmt->execute();
    return true;
}

This does assume that all values are strings.

Answer 2

I once wrote a PDO wrapper that does update/insert queries, given an array of column => value pairs. The same general strategy may be useful here too.

What you want to do is something like this:

public function updateUserData($array, $id)
{
    // Instead of a global, consider dependency injection and object properties?
    global $mysqli;

    if (empty($array)) {
        return false;
    }

    // Build the update query:
    $binding = '';
    $columns = '';
    $params = [];
    foreach ($array as $key => $value) {
        $binding .= 's';
        // Note: A whitelist is better here than escaping:
        $columns .= ' `' . preg_replace('/[^a-z_A-Z0-9]/', '', $key) . '` = ?,';
        $params []= (string) $value;
    }
    $params []= $id;

    $stmt = $mysqli->prepare(
        "UPDATE users SET " . rtrim($columns, ',') . " WHERE id = ?"
    );
    $stmt->bind_param(
        $binding . 'i', 
        ...$params
    );
    return $stmt->execute();
}

(Where possible, I adopted the code provided in the question. I personally would tackle this problem more generically, like I did with the wrapper above, then just use the abstraction.)

This assumes all strings. You could detect the type of $value in the foreach loop to specify a different type placeholder if you prefer. (PHP 5.6+ only, which are the only version currently supported anyway.)

In the sample above, the strings it build should look like:

<?php
/*
$users->updateUserData(array(
    'firstname' => 'Wanda',
    'lastname' => 'Merritt',
    'state' => 'IN'
    'address' => '693 Pearcy Avenue',
    'zipcode' => 46625,
), 45);
*/

# Query string: 
  "UPDATE users SET  `firstname` = ?, `lastname` = ?, `state` = ?, `address` = ?, `zipcode` = ? WHERE id = ?"
# Binding:
  "sssssi"
# Parameters
  [
    'wanda',
    'Merritt',
    'IN',
    '693 Pearcy Avenue',
    '46625',
    45
  ]

And then:

/*
$users->updateUserData(array(
    'firstname' => 'Stanley',
    'lastname' => 'Lewis',
    'password' => '123123'
), 159);
*/
# Query String:
  "UPDATE users SET  `firstname` = ?, `lastname` = ?, `password` = ? WHERE id = ?"
# Binding:
  "sssi"
# Parameters:
  [
    'Stanley',
    'Lewis',
    '123123'
  ]

It should go without saying, but I'll say it anyway: Don't store plaintext passwords.