如何使用PDO在一次数据库访问中插入多个记录?

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

我有一个名为propconvenience的表，它包含两列amenity_id和property_id基本上表包含外键。

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

现在我必须为下面的语句生成一个名为占位符的PDO查询。

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

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?

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

2 个解决方案

#1

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

你可以给占位符取任何你想要的名字，这样你的SQL就可以这样做:

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

And then:

然后:

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

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 
(?, ?),
(?, ?),
(?, ?)

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

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

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

#2

-6

It is much simpler to not use a prepared query here, simply generate your query string using some implode()s and execute it. Of course, make sure your parameters are properly quoted (since they're ints, applying intval() is enough).

在这里不使用准备好的查询更简单，只需使用一些内爆()来生成查询字符串并执行它。当然，要确保您的参数被正确引用(因为它们是int类型，应用intval()就足够了)。

#1