使用内爆数组 PHP 更新 MySQL 记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5250128/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating MySQL record with imploded array PHP
提问by JHamill
I'm currently attempting to update existing records within a MySQL database. Prior to the updated information being sent to the 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.
我目前正在尝试更新 MySQL 数据库中的现有记录。在将更新的信息发送到数据库之前，它被放置在一个数组中，在使用 implode 函数之前通过验证函数运行，使我能够将数组插入到数据库中。这在添加新用户时工作正常，但我在使用内爆数组进行 UPDATE 查询时遇到困难。
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.
我目前有这样的事情，它给了我一个 SQL 错误 - 但是，我从没想过这会起作用，因为 SQL 语法是错误的。
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();
}
回答by Shoe
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();
}
回答by Stephen
First off. Please stop using the mysql* functions. PDO has numerous significant advantages.
首先。请停止使用 mysql* 函数。PDO 具有许多显着的优势。
As for your specific issue:
至于你的具体问题：
This is not a valid MySQL statement:
这不是有效的 MySQL 语句：
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.
使用 PDO 这仍然可以使用数组输入，因为 PDOStatement::execute() 方法接受值的关联数组，其中键对应于传递给 PDO::prepare 的 SQL 语句中的占位符。
回答by randomx
I'm just going to focus on fixing the PHP embedded SQL syntax error here.
我将在这里专注于修复 PHP 嵌入式 SQL 语法错误。
First, let's look at the legal syntax: http://dev.mysql.com/doc/refman/5.1/en/update.html
首先来看一下合法的语法：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
更新：如果可能在您的环境/框架中，请使用准备好的语句。 https://secure.php.net/manual/en/mysqli.quickstart.prepared-statements.php
回答by Daniel Ahern
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.
这假设您的信息来自表单 (method=POST)，其中输入框的字段名称与代码中的字段名称相同，并且它有一个隐藏的输入“userId”来确定“where”子句。
It uses PDO and binding parameters.
它使用 PDO 和绑定参数。
$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);
回答by Jon
Assuming PHP >= 5.3 for anonymous functions (otherwise, the callback would have to be written as a free function or as a string):
假设匿名函数的 PHP >= 5.3（否则，回调必须写为自由函数或字符串）：
$implodeArray = implode(',',
array_map(
function($item) { return "'".mysql_real_escape_string($item)."'"; },
$array));
Pleasedo not everput things in queries that have not been escaped first!
请永远不要把东西放在没有先转义的查询中！
