Single INSERT query for multiple records using Prepared statement

Question

Here's a fairly basic PHP PDO snippet that accesses a MySQL table named "Users" and inserts rows of data to it using a prepared statement and multiple bindings:

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

// prepare sql and bind parameters
$stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email) 
VALUES (:firstname, :lastname, :email)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);

// insert a row
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

// insert another row
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();
....

As can be seen, this involves an indefinite number of queries since every row-insert takes an execute() of its own. I am attempting to reduce the number of queries and my goal is to have a single loop create a single INSERT query for all the records being inserted. Is it possible? Without prepared statement it's very easy and I can make it work but I am lost with prepared.

You want to make a loop, but only run one query, why do you then need a loop ?! — Rizier123, May 04 '15 at 10:00
Use batch insert http://stackoverflow.com/questions/15069962/php-pdo-insert-batch-multiple-rows-with-placeholders — M Khalid Junaid, May 04 '15 at 10:00
@Rizier123 The loop doesn't execute the query, just builds it. — TheLearner, May 04 '15 at 10:08
I just had a whole answer written out and now it's been closed! Damn! — Chris Evans, May 04 '15 at 10:16

score 0 · Answer 1 · answered May 04 '15 at 10:00

0

It's enough to concatenate strings with coma (,) to act as one parameter.

$stmt->bindParam(':firstname', $firstname[0] . ',' . $firstname[1]);

answered May 04 '15 at 10:00

Marcin Wesel

94
5

This gives you one row with `'Alice,Bob,Charles'`... – glglgl May 04 '15 at 10:04

Single INSERT query for multiple records using Prepared statement

1 Answers1