How to insert multiple records in one trip to the database using PDO?

Question

I have a table called propAmenities , which contains two columns amenity_id and property_id , basically the table contains foreign keys.

now I need to generate a PDO request using a named placeholder for the statement below.

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

I tried using the following syntax, but I'm 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'm not sure how to use bindParam () PDO? how can i deal with this situation? Using PDO? Am I using the wrong PDO placeholders?

Answer 1

You can provide placeholders with any names you want 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, unnamed placeholders may be easier to work with, although:

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

And then you can skip your values ​​and call execute with the appropriate array:

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

Answer 2

It is much easier not to use the prepared query here, just generate the query string with some implode () s and execute it. Of course, make sure your parameters are correctly quoted (since they are int, using intval () is enough).