如何使用PDO在一个数据库行程中插入多个记录? [英] How do i Insert Multiple records in one database trip using PDO?

问题描述

我有一个名为propAmenities的表，该表包含两列amenity_id和property_id，该表基本上包含外键.

i have a table called propAmenities which holds two column amenity_id and property_id basically the table holds the foreign keys.

现在我必须使用命名占位符为以下语句生成PDO查询.

now i have to generate a PDO query using named placeholder for the below statement.

INSERT INTO propAmenities (amenity_id, property_id) VALUES (1, 1), (2, 1), (3, 1)

我尝试使用以下语法，但是不确定是否可以使用.

i tried using the following syntax but i am not sure if this will work.

$sth->$this->dbh->prepare('INSERT INTO 
                           propAmenities 
                           (amenity_id, property_id) 
                           VALUES 
                           (:amenity_id, :property_id), 
                           (:amenity_id, :property_id), 
                           (:amenity_id, :property_id)');

对于上述查询，我​​不确定如何使用PDO的bindParam()吗?如何使用PDO处理这种情况?我使用了错误的PDO占位符吗?

and for the above query i am not sure how do i use PDO's bindParam() ? how do i handle this situation Using PDO? am i using the wrong PDO's Placeholders?

推荐答案

您可以为占位符提供所需的任何名称，以便在SQL中使用以下名称:

You can give the placeholders whatever names you want so something like this for your SQL:

INSERT INTO propAmenities 
(amenity_id, property_id) 
VALUES 
(:amenity_id1, :property_id1), 
(:amenity_id2, :property_id2), 
(:amenity_id3, :property_id3)

然后:

$stmt->bindParam(':amenity_id1',  1);
$stmt->bindParam(':property_id1', 1);
$stmt->bindParam(':amenity_id2',  2);
$stmt->bindParam(':property_id2', 1);
$stmt->bindParam(':amenity_id3',  3);
$stmt->bindParam(':property_id3', 1);

或者，当然，为execute构建适当的数组.在这种情况下，未命名的占位符可能更易于使用:

Or, of course, build the appropriate array for execute. In this case, non-named placeholders might be easier to work with though:

INSERT INTO propAmenities 
(amenity_id, property_id) 
VALUES 
(?, ?),
(?, ?),
(?, ?)

然后您可以遍历值并使用适当的数组调用execute:

And then you can loop through your values and call execute with the appropriate array:

$stmt->execute(array(1, 1, 2, 1, 3, 1));

这篇关于如何使用PDO在一个数据库行程中插入多个记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！