Updating MySQL record with imploded array PHP

I'm currently attempting to update existing records within a MySQL database. Prior to the updated information being sent to t开发者_运维技巧he database, it is placed in an array, run through a validation function before using the implode function to enable me to insert the array into the database. This works fine when adding new users, but I am having difficulty using the imploded array for a UPDATE query.

Can I specify individual strings from the imploded array, so that I can SET username to the username string contained within the original array?

I currently have something like this which is giving me an SQL error - however, I never expected this to work as the SQL syntax is wrong.

public function editUser($array, $userID) {
    $edited = 'User Ammended';
    $array['password'] = $this->hashPassword($array['password']);
    $implodeArray = '"'.implode( '","', $array ).'"';
    $sql = ('UPDATE user (email, password, firstName, lastName, officeID, departmentID, managerID, roleID, username) WHERE userID=$userID VALUES ('.$implodeArray.')');
    mysql_query($sql,$this->_db) or die(mysql_error());
    mysql_close();
}

This should work:

public function editUser($array, $userID) {
        $edited = 'User Ammended';
        $array['password'] = $this->hashPassword($array['password']);
        /*Assuming array keys are = to database fileds*/
        if (count($array) > 0) {
            foreach ($array as $key => $value) {

                $value = mysql_real_escape_string($value); // this is dedicated to @Jon
                $value = "'$value'";
                $updates[] = "$key = $value";
            }
        }
        $implodeArray = implode(', ', $updates);
        $sql = ("UPDATE user WHERE userID=$userID SET $implodeArray");
        mysql_query($sql,$this->_db) or die(mysql_error());
        mysql_close();
}

First off. Please stop using the mysql* functions. PDO has numerous significant advantages.

As for your specific issue:

This is not a valid MySQL statement:

UPDATE table (col1, col2) WHERE 1 VALUES('foo', 'bar')

You need to use something like:

UPDATE table SET col1 = 'foo', col2 = 'bar' WHERE 1

Using PDO this could still make use of array input, as the PDOStatement::execute() method accepts an associative array of values, with the keys corresponding to placeholders in the SQL statement passed to PDO::prepare.

I'm just going to focus on fixing the PHP embedded SQL syntax error here.

First, let's look at the legal syntax: http://dev.mysql.com/doc/refman/5.1/en/update.html

Next, let's look at the broken form:

$sql = ('UPDATE user (email, password, firstName, lastName, officeID, departmentID, managerID, roleID, username) WHERE userID=$userID VALUES ('.$implodeArray.')');

Finally, since the array is already an ordered list (you're imploding it), let's repair and replace with something like:

$sql = "UPDATE user 
SET email = '$array[0]', 
password = '$array[1]', 
firstName = '$array[2]', 
lastName = '$array[3]', 
officeID = '$array[4]', 
departmentID = '$array[5]', 
managerID = '$array[6]', 
roleID = '$array[7]', 
username = '$array[8]' 
WHERE userID = '$userID.'";

I'm also assuming the input values are already escaped, filtered and checked.

UPDATE: If possible in your environment/framework, use prepared statements. https://secure.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Assuming PHP >= 5.3 for anonymous functions (otherwise, the callback would have to be written as a free function or as a string):

$implodeArray = implode(',',
    array_map(
        function($item) { return "'".mysql_real_escape_string($item)."'"; },
        $array));

Please do not ever put things in queries that have not been escaped first!

This assumes your information is coming from a form (method=POST) where the field names of the input boxes are the same as the field names in the code, and it has a hidden input 'userId' that determines the 'where' clause.

It uses PDO and binding parameters.

$query = "update users set ";

foreach($_POST as $key=>$value) {
    if($key != 'userId') {
    $inputs[] = " $key = ? ";
    $valueArray[] = $value; }
    }
$query .= implode( ',', $inputs );

$query .= " where id = " . $_POST['userId'];

$sth = $pdo->prepare($query);
$sth->execute($valueArray);