php 使用数组更新 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19157510/
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
Update MySQL using an array
提问by Junior
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.
是否可以在 PHP 中使用 MySQLi 在不知道字段名称、字段名称及其值的情况下使用数组更新表。
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.
我试过用准备好的语句在键和值字段 (?=?) 上插入一个问号。我在想是否可以在更新查询中使用 () VALUES () 我可能有一个解决方案，但我猜不存在这样的东西。
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);
?>
回答by Halcyon
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.
这确实假设所有值都是字符串。
回答by Scott Arciszewski
I once wrote a PDO wrapperthat does update/insert queries, given an array of column => value pairs. The same general strategy may be useful here too.
我曾经写过一个 PDO 包装器，它执行更新/插入查询，给定一个列 => 值对数组。同样的一般策略在这里也可能有用。
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 $valuein the foreach loop to specify a different type placeholder if you prefer. (PHP 5.6+ only, which are the only version currently supported anyway.)
这假设所有字符串。$value如果您愿意，您可以检测foreach 循环中的类型以指定不同类型的占位符。（仅限 PHP 5.6+，这是目前唯一支持的版本。）
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.
不用说，但我还是要说：不要存储明文密码。
回答by romaninsh
A use of a Query Builder can simplify your life:
使用查询生成器可以简化您的生活：
public function updateUserData($array, $id) {
global $db;
$q = new Query($db);
$q->table('users')
$q->set($array);
$q->where('id', $id);
$q->update();
return true;
}
A job of a query builder is to automatically escape field names to prevent injections and to allocate parametric variables for the values.
查询构建器的工作是自动转义字段名称以防止注入并为值分配参数变量。
There are many different Query Builders, I have used the syntax of DSQL.
有许多不同的查询构建器，我使用了DSQL的语法。
回答by Zain
$a=array("item1"=>"object1", "item2"=>"object2");
function update_data($a, $id)
{
$sql = "UPDATE Tbl_name SET ";
$sql .= urldecode(http_build_query($a,'',', '));
$sql .= " WHERE img_id='".$id."'";
//echo $sql;//this is only for testing.
}
echo update_data($a, $id);
output is: UPDATE images SET item1=object1, item2=object2
输出是： UPDATE images SET item1=object1, item2=object2
回答by aman
Short and sweet code
简短而甜蜜的代码
<?php
class UserHandler{
public function myfunction($v,$x)
{
return($x.'='.$v);
}
function updateUserData($array, $id) {
$set_str = implode(', ',array_map(array(new UserHandler(), 'myfunction'),$array, array_keys($array)));
$updStr = 'UPDATE users SET '.$set_str." where id=".$id;
// execute Something is wrong
}
}
$new = new UserHandler();
$update = $new->updateUserData(array(
'firstname' => 'Wanda',
'lastname' => 'Merritt',
'state' => 'IN',
'address' => '693 Pearcy Avenue',
'zipcode' => 46625,
), 45);
回答by mavrosxristoforos
If I wanted to do such a thing, I would loop through the array, like this:
如果我想做这样的事情，我会遍历数组，就像这样：
function updateUserData($array, $id) {
$query = 'UPDATE `table` SET ';
$sep = '';
foreach($array as $key=>$value) {
$query .= $sep.$key.' = "'.$value.'"';
$sep = ',';
}
$query .= ' WHERE `id` = "'.$id.'"';
// execute query
}
回答by ABalazs
I am new coder. This is my solution for Update Mysql by array. It checks if value is empty and protect against mysql injection.
我是新的编码员。这是我按数组更新 Mysql 的解决方案。它检查值是否为空并防止 mysql 注入。
$datas = array('column_name' => 'data');
function esemenyFeltolto(array $datas, $id){
$mysqli = getConnect();
foreach($datas as $key=>$value){
if(!empty($value)){
$key = $mysqli->real_escape_string($key);
$value = $mysqli->real_escape_string($value);
$data[] = $key.'="'.$value.'"';
}
}
$query = "UPDATE table SET ".implode(',',$data)." WHERE table_id = ".$id;
$mysqli->query($query);
}
