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?

Answer 1

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