php PDO MySQL：在一个查询中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10060721/
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
PDO MySQL: Insert multiple rows in one query
提问by Adam Ramadhan
Hello I am making a class for doing multiple insert in pdo.
你好，我正在制作一个在 pdo 中进行多次插入的类。
It is something like this
它是这样的
INSERT INTO $table (key1,key2,key3,etc) VALUE (value1,value2,value3,etc), (value1,value2,value3,etc), (value1,value2,value3,etc)
After searching I found out that I have to build something like
搜索后我发现我必须构建类似的东西
INSERT INTO $table (key1,key2,key3,etc) VALUE (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc)
then execute with this $this->execute($data);where $datais
然后用这个执行$this->execute($data);where $datais
0 =>
array
'key1' => 'value1'
'key2' => 'value2'
'key3' => 'value3'
1 =>
array
'key1' => 'value1'
'key2' => 'value2'
'key3' => 'value3'
etc
the problem is i still get an error Array to string conversionon $insert->execute($data);how can i fix that?
问题是我仍然收到Array to string conversion关于$insert->execute($data);如何解决这个问题的错误？
here's a snippet of what I'm making.
这是我正在制作的片段。
public function multipleInsert($table, $data = array())
{
# INSERT (name) VALUE (value),(value)
if (count($data) > 1)
{
$fieldnames = array_keys($data[0]);
$count_inserts = count(array_values($data));
$count_values = count(array_values($data[0]));
# array(????) untill x from first data
for($i = 0; $i < $count_values; $i++)
{
$placeholder[] = '?';
}
# array((????),(????),(????)) for query
for ($i=0; $i < $count_inserts; $i++)
{
$placeholders[] = '('. implode(',',$placeholder) . ')';
}
$query = 'INSERT INTO '. $table;
$query .= '(`'. implode('`, `', $fieldnames) .'`)';
$query .= ' VALUES '. implode(', ', $placeholders);
$insert = $this->start->prepare($query);
$i = 1;
foreach($data as $item)
{
foreach ($item as $key => $value)
{
$insert->bindParam($i++, $item[$key]);
}
}
echo $query;
$insert->execute();
$return['status'] = true;
$return['lastid'] = $this->start->lastInsertId();
return $return;
}
else
{
die('$data is less then two array, use single insert instead.');
}
}
回答by Starx
An easy way for this avoiding the complications would be something like this
避免并发症的一种简单方法是这样的
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
$stmt->bindValue(':a', $item[0]);
$stmt->bindValue(':b', $item[1]);
$stmt->bindValue(':c', $item[2]);
$stmt->execute();
}
However, this executes the statement multiple times. So, it is better if we create a long single query in order to do this.
但是，这会多次执行该语句。因此，最好创建一个长的单个查询来执行此操作。
Here is an example of how we can do this.
这是我们如何做到这一点的一个例子。
$query = "INSERT INTO foo (key1, key2) VALUES "; //Prequery
$qPart = array_fill(0, count($data), "(?, ?)");
$query .= implode(",",$qPart);
$stmt = $dbh -> prepare($query);
$i = 1;
foreach($data as $item) { //bind the values one by one
$stmt->bindValue($i++, $item['key1']);
$stmt->bindValue($i++, $item['key2']);
}
$stmt -> execute(); //execute
