How do i Insert Multiple records in one database trip using PDO?

Question

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

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

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?

score 5 · Accepted Answer · answered Jun 04 '11 at 08:42

5

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)

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:

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

answered Jun 04 '11 at 08:42

mu is too short

426,620
70
833
800

wow, that was a very nice explanation, and moreover i guess it is better to use unnamed placeholders then the named one. – Ibrahim Azhar Armar Jun 04 '11 at 08:53
1

@Ibrahim: Thanks. Named versus unnamed depends on context. In this specific case, unnamed placeholders are probably easier but if you were inserting rows with more than two columns then named placeholders (with numeric suffixes as above) would probably be easier to understand at a glance. In general, I'd lean towards named placeholders as they make your code easier to understand and less error prone. OTOH, if you're building the placeholders and values using parallel arrays (one for question marks, one for values) then unnamed placeholders might be easier. So, it depends. – mu is too short Jun 04 '11 at 09:02

score -6 · Answer 2 · answered Jun 04 '11 at 08:39

-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).

answered Jun 04 '11 at 08:39

bobflux

11,123
3
27
27

1

-1, i see no reason on why you cannot achieve this using prepared statement? and since i am using OO approach for my project it is highly recommended to use PDO. – Ibrahim Azhar Armar Jun 04 '11 at 08:55
3

-1, suggesting that complex queries should avoid the standard safety mechanism is a Pandora's Box that leads to SQL injections. Consistency is valuable. – Tom Jun 04 '11 at 13:00

How do i Insert Multiple records in one database trip using PDO?

2 Answers2

Linked