php pdo 多数组插入

2022-01-09 00:00:00 arrays insert php 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.

对于多次插入:

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();
    }
}

单次插入

$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);

还有错误:

错误:SQLSTATE[HY093]:参数号无效:绑定变量的数量与标记的数量不匹配

Error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

推荐答案

利用 MySQL 中多次插入的插入速度(http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html )，你可以使用准备好的语句构建更大的查询.这确实增加了迭代方法的复杂性，因此可能只对高需求系统或大型数据集值得.

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();

相关文章