从数组 MySQL 和 PHP 构建插入查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7051639/
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
Build insert query from array MySQL and PHP
提问by Dave
I'm writing a small web service in PHP and I'm having a bit of trouble getting my head around the following scenario.
我正在用 PHP 编写一个小型 Web 服务，但在处理以下场景时遇到了一些麻烦。
My plan is to be able to send an array of data to a function, which would then build a query for MySQL to run. In the array I plan for the key to be the column name, and the value to be the value going in to the columns.. i.e.
我的计划是能够将一组数据发送到一个函数，然后该函数将为 MySQL 构建一个查询以运行。在数组中，我计划将键作为列名，将值作为进入列的值.. 即
$myData = array('userName'=>'foo','passWord'=>'bar');
$myClass = new users();
$myClass->addUser($myData);
Then, in my function I currently have:
然后，在我的功能中，我目前有：
function addUser($usrData){
foreach($usrData as $col => $val){
// This is where I'm stuck..
}
}
Basically, I am wondering how I can separate the keys and values so that my query would become:
基本上，我想知道如何将键和值分开，以便我的查询变为：
INSERT INTO `myTable` (`userName`,`passWord`) VALUES ('foo','bar');
I know I could probably do something like:
我知道我可能会做这样的事情：
function addUser($usrData){
foreach($usrData as $col => $val){
$cols .= "," . $col;
$values .= ",'".$val."'";
}
}
But I thought there might be a more elegant solution.
但我认为可能有更优雅的解决方案。
This is probably something really simple, but I have never came across this before, so I would be grateful for any help and direction..
这可能是非常简单的事情，但我以前从未遇到过这种情况，因此如果您能提供任何帮助和指导，我将不胜感激。
Thanks,
谢谢，
Dave
戴夫
回答by Matt Bradley
Try this:
尝试这个：
function addUser($usrData) {
$count = 0;
$fields = '';
foreach($usrData as $col => $val) {
if ($count++ != 0) $fields .= ', ';
$col = mysql_real_escape_string($col);
$val = mysql_real_escape_string($val);
$fields .= "`$col` = $val";
}
$query = "INSERT INTO `myTable` SET $fields;";
}
回答by Sudhi
EDIT:
Oops ! forgot quotation around VALUES( ), removing the old code
编辑：
哎呀！忘记引用 VALUES()，删除旧代码
$query = "INSERT INTO `mytable` ( ".
mysql_real_escape_string(implode(' , ',array_keys( $userData))).
") VALUES ( '".
mysql_real_escape_string(implode("' , '", $userData)).
"' )";
回答by gray_15
Elegant solution:
优雅的解决方案：
function create_insert_query($tablename, $array) {
$key = array_keys($array);
$val = array_values($array);
//sanitation needed!
$query = "INSERT INTO $tablename (" . implode(', ', $key) . ") "
. "VALUES ('" . implode("', '", $val) . "')";
return($query);
}
回答by Brad
FYI, your code is wide open to SQL injection currently.
仅供参考，您的代码目前对 SQL 注入是开放的。
Use prepared queries with PDO. You can define your parameter names, and just pass an associative array to do the inserting.
将准备好的查询与 PDO 一起使用。您可以定义参数名称，只需传递一个关联数组即可进行插入。
You won't be able to stick an arbitrary array in there, as you will need to name your parameters appropriately (such as :userNameinstead of userName), but I don't think you want to do that anyway.
您将无法在其中粘贴任意数组，因为您需要适当地命名您的参数（例如:userName代替userName），但我认为您无论如何都不想这样做。
回答by David Chincharashvili
My Bigginer (Easy) way to do it. It needs some refactoring, but it's quiet understandable for me.
我的 Bigginer (Easy) 方法来做到这一点。它需要一些重构，但对我来说是可以理解的。
public function insertArr( $table, $paramsArr) {
$sql = "INSERT INTO {$table} (";
foreach ( $paramsArr as $name=>$value ) {
$sql .="`$name`,";
}
$sql = substr($sql, 0, strlen($sql)-1);
$sql .= ") VALUES (";
foreach ($paramsArr as $name => $value){
$value === null? $sql .= "null," : $sql .= "'$value',";
}
$sql = substr($sql, 0, strlen($sql)-1);
$sql .= ");";
$this->link->query($sql);
return $this->link->affected_rows;
}
回答by Mohit Kamboj
//This Will Help You To Insert Data Into Database by Array
$myData = array('user'=>'foo','name'=>'bar','player'=>'Sachin');
$get->adddd('tabelname',$myData);
function insert($tabel,$usrData) {
$count = 0;
$fields = '';
foreach($usrData as $col => $val) {
if ($count++ != 0) $fields .= ', ';
if($count==1){
$field .= $col;
$value .= "'".$val."'";
}
else{
$field .= ','.$col;
$value .= ",'".$val."'";
}
$fields .= "`$col` = $val";
}
mysql_query($query = "INSERT INTO $tabel ($field) VALUES ($value)");
}
回答by Madara's Ghost
How about this one?
这个怎么样？
function addUser($usrData){
$query = "INSERT INTO `myTable` (`userName`, `passWord`) VALUES (:userName, :passWord);";
$stmt = $pdo->prepare($query);
foreach($usrData as $col => $val){
$stmt->bindValue(':'.$col, $val);
}
$stmt->execute();
}
It should do the job for you.
它应该为您完成这项工作。
回答by MikeRogers0
Here is the code I tend to use for an insert query:
这是我倾向于用于插入查询的代码：
<?php
// Extend the PDO class, adding support for array to query binding
class db extends pdo{
// This makes the SQL insert query
function insert($keyValue){
if(is_array($keyValue)){
foreach($keyValue as $key => $value){
$fields[] = '`'.$key.'`';
$values[] = ':'.$key;
}
return '('.implode(' , ',$fields).') VALUES '.'('.implode(' , ',$values).')';
}
return '';
}
// Change the key to be :key to stop injections
function bind($keyValue){
if(is_array($keyValue)){
foreach($keyValue as $key => $value){
if(is_array($value)){ // if the value is array, lets assume I want an OR statement.
$count = -1;
foreach($value as $sValue){
$count++;
$where[':'.$key.$count] = $sValue;
}
} else {
$where[':'.$key] = $value;
}
}
return $where;
}
return array();
}
}
// It can be used like
try {
// Call the PDO class (Connect to the database).
$db= new PDO('mysql:host='.$host.';dbname='.$dbname, $user, $pass);
} catch(PDOException $e) {
// If something goes wrong, PDO throws an exception with a nice error message.
echo $e->getMessage();
}
// The values you want to Add
$values = array('username' => $username, 'otherdata' => $otherdata);
$db->prepare('INSERT INTO `users` '.$db->insert($values).';') // The SQL statement.
->execute($db->bind($values)); // Bind the values to the query (Stopping SQL injections)
?>
