PHP PDO多阵列插入 [英] php pdo multi array insert
问题描述
我已经打了周围几个小时,并试图排序了这一点,但看起来像一个难啃的骨头。
我可以做一个单一阵列插入
$ =人阵('名'=>'温迪','年龄'=>'32');
但是如果我想多是这样的:
$人=阵列(阵列('名'=>'丹','年龄'=> '30'),阵列('名'=>约翰','年龄'=>'25'),阵列('名'=>'温迪','年龄'=>'32'));
它不工作?任何帮助将是AP preciated。
有关多项插入:
公共职能insertPdo($表,$数据){
尝试{
如果(!is_array($数据)||计数($数据)!)返回false; $绑定=':'。爆(':',array_keys($数据));
$ SQL =INSERT INTO。 $表。 '('。爆(',',array_keys($数据))。)。 值('。$困境。); 而$ sth = $这个 - > __ dbh-> prepare($的SQL);
$结果= $ sth->执行($的数据); }
赶上(PDOException $ E){
回声$ E->的getMessage();
}
}
有关单次插入
$ =人阵('名'=>'丹','年龄'=> '30');
$ DB-GT&; insertPdo('test_pdo',$人);//对于多插入,我试图在上面的函数中使用此
的foreach(如$行$数据){
$结果= $ sth->执行($行);
};$人=阵列(阵列('名'=>'丹','年龄'=> '30'),阵列('名'=>'约翰','年龄'=>'25') ,阵列('名'=>'温迪','年龄'=>'32'));
$ DB-GT&; insertPdo('test_pdo',$人);
和错误:
错误:SQLSTATE [HY093]:无效的参数编号:绑定变量的数目不匹配的令牌数量
块引用>解决方案要充分利用多个插入的插入速度在MySQL中(的 http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html ),您可以使用prepared是构建更大的查询语句。这确实增加了复杂性超过一个以上迭代的方法,所以可能只是值得的高需求的系统或稍大的数据集。
如果你,你提出了上面有你的数据:
$人=阵列(阵列('名'=>'丹','年龄'=> '30'),阵列('名'=>
'约翰','年龄'=> '25'),阵列('名'=>'温迪','年龄'=>'32'));我们正在寻找生成一个查询,看起来是这样的:
插入表(姓名,年龄)值,(?,?)(?,?)(?,?);
要拉了一起,你会想要的东西没有完全不像是:
$ pdo->的BeginTransaction()//也有助于加快您的刀片
$ INSERT_VALUES =阵列();
的foreach($某人为$ P){
$ question_marks [] ='(?,?)';
$ INSERT_VALUES = array_merge($ INSERT_VALUES,array_values($ P));
}$ SQL =INSERT INTO TABLE_NAME(姓名,年龄)值。爆(,,$ question_marks);$语句= $ pdo-> prepare($的SQL);
尝试{
$ stmt->执行($ INSERT_VALUES);
}赶上(PDOException $ E){
//做一些聪明一点?
}
$ pdo->提交();I've been playing around for few hours and trying to sort this out but looks like a hard nut to crack.
I'm able to do a single array insertion
$person = array('name' => 'Wendy', 'age' => '32');
but if I want multiple like this:
$person = array(array('name'=>'Dan', 'age'=>'30'), array('name' => 'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32'));
It's not working? Any help would be appreciated.
For multiple insertion:
public function insertPdo($table, $data){ try{ if (!is_array($data) || !count($data)) return false; $bind = ':' . implode(', :', array_keys($data)); $sql = 'INSERT INTO ' . $table . ' (' . implode(', ',array_keys($data)) . ') ' . 'values (' . $bind . ')'; $sth = $this->__dbh->prepare($sql); $result = $sth->execute($data); } catch(PDOException $e){ echo $e->getMessage(); } }
For Single Insertion
$person = array('name'=>'Dan', 'age'=>'30'); $db->insertPdo('test_pdo',$person); // For Multi Insertion, I'm trying to use this in above function foreach ($data as $row) { $result = $sth->execute($row); }; $person = array(array('name'=>'Dan', 'age'=>'30'), array('name' => 'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32')); $db->insertPdo('test_pdo',$person);
And the error:
Error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
解决方案To take advantage of the insert speed of multiple inserts in MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html ), you can use a prepared statement that builds the larger query. This does add complexity over an more iterative approach, so is probably only worth it for high-demand systems or largish data sets.
If you have your data as you proposed above:
$person = array(array('name'=>'Dan', 'age'=>'30'), array('name' => 'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32'));
We're looking to generate a query that looks something like this:
insert into table (name, age) values (?,?), (?,?), (?,?);
To pull this together you'll want something not totally unlike this:
$pdo->beginTransaction() // also helps speed up your inserts $insert_values = array(); foreach($person as $p){ $question_marks[] = '(?,?)'; $insert_values = array_merge($insert_values, array_values($p)); } $sql = "INSERT INTO table_name (name, age) VALUES " . implode(',', $question_marks); $stmt = $pdo->prepare ($sql); try { $stmt->execute($insert_values); } catch (PDOException $e){ // Do something smart about it... } $pdo->commit();
这篇关于PHP PDO多阵列插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!