inserting multiple rows in one query from an array

Question

i have an array from a post:

//this are arrays
$name    = $_POST["name"];
$age     = $_POST["age"];
$date    = $_POST["date"];

i have an insert query in PDO:

$stmt = $db->prepare("INSERT INTO staff (name, age, address) VALUES (:name, :age, :address)");

my question how can i generate/or achieve a query using php that looks like:

INSERT INTO staff (name, age, address) VALUES
($name[0], $age[0], $address[0]), ($name[1], $age[1], $address[1])... etc

I try to keep server load low.

The number of queries are not actually that what produce server-load. Also from your question it is not clear which concrete problem you're trying to solve? IIRC the PHP manual has a code-example how that works already, so I wonder what exactly does not work for you? Maybe you can clarify a little into which concrete problem you run? — hakre, May 05 '13 at 13:33

Denis de Bernardy · Answer 1 · 2013-05-05T12:55:00.853

4

You could prepare the appropriate statement, i.e. something like:

$sql = "INSERT INTO staff (name, age, address) VALUES ";
$values = array();

for ($i = 1; $i <= $count; $i++) {
  $values[] =  "(:name$i, :age$i, :address$i)"
}

$stmt = $db->prepare($sql . implode(', ', $values));

But, imho, you'll probably be just as well off lopping through each set of values. It's not much of a burden for the server, and your code will be simpler.

edited May 05 '13 at 12:55

answered May 05 '13 at 12:46

Denis de Bernardy

75,850
13
131
154

2

And if you turn of `EMULATE_PREPARES` you'll have the advantage of reusing an already parsed statement. – Marcel Korpel May 05 '13 at 12:50
1

small typo I guess :age$1, :address$1 – v0d1ch May 05 '13 at 12:52
i get Invalid parameter number: no parameters were bound – Viscocent May 05 '13 at 13:00
2

+1 for the sentence at the end. the only sane suggestion for this problem :) – hakre May 05 '13 at 13:25
@Viscocent: I haven't used pdo in a while, but if memory serves you then need to execute while passing an array like `array('name1'=>$name[0], 'age1' => $age[0], ...)`. It's not pretty, hence my suggestion at the very end: don't bother with this stuff, and send as many inserts as you have rows. – Denis de Bernardy May 05 '13 at 13:45
maybe `$stmt = $db->prepare($sql . implode(', ', $values));` is expecting some variables to be bound – Viscocent May 05 '13 at 13:54
@Viscocent: yeah. Each and every single last one of them, if you've bound your variables already. – Denis de Bernardy May 05 '13 at 13:57

score 3 · Accepted Answer · edited Jul 18 '13 at 15:16

It is not possible with prepared statements.

If you want to insert a variable amount of records in one query then the SQL query must be generated dynamically, which makes it impossible to prepare it beforehand.

If you use the database driver's escape_string function (that would be quote() for MySQL) then you can build the query and still get the security that prepared statements give you.

$query = "INSERT INTO table (a, b, c) VALUES";
$tuple = " ('%s', '%s', '%s'),";
foreach ($items as $item) {
    $a = $db->quote($item['a']);
    $b = $db->quote($item['b']);
    $c = $db->quote($item['c']);
    $query .= sprintf($tuple, $a, $b, $c);
}
$query = rtrim($query, ","); // Remove trailing comma
// Use the query...

Addendum:

If you are using prepared statements then you can insert records separately and not have to worry about inserting them in one go. That is actually the whole point of prepared statement.

Unlike good old SQL queries, which are a one-step process and are just sent and then forgotten...

$db->query("INSERT INTO table (a, b, c) VALUES ('a', 'b', 'c')");

...prepared statements are a two-step process.

First, you create the prepared statement. This statement is then sent to the database, telling it that "this is what you should be expecting". The database will often also optimize the query to make it faster. This step then gives you back a statement handle (often called $stmt in PHP documentation).

$stmt = $db->prepare('INSERT INTO table (a, b, c) VALUES (:a, :b, :c)');

Second, with this handle you can then proceed to insert stuff:

foreach ($records as $record) {
    $stmt->execute(array(
        ':a' => $record['a'],
        ':b' => $record['b'],
        ':c' => $record['c'],
    ));
}

Because the database already knows what to expect it can optimize the speed of the INSERTs, meaning that you do not have to go through the stuff that I mentioned above this addendum.

Wikipedia actually has a quite nice writeup of prepared statements.

That escape_string function is called [`quote`](http://php.net/manual/en/pdo.quote.php) in PDO and it already puts quotes around the string, apart from escaping special characters. — Marcel Korpel, May 05 '13 at 12:56
@Sverri i get: Call to undefined method PDO::escape_string() — Viscocent, May 05 '13 at 13:03

Opifexer · Answer 3 · 2013-05-05T13:24:32.963

If you are using PDO, you should prepare your statement, and execute it with different values:

$stmt = $db->prepare("INSERT INTO staff (name,age,address) VALUES (:name,:age,:address)");

for($i=0;$i<count($name);$i++)
{
    $stmt->execute(array(
        ":name" => $name[$i],
        ":age" => $age[$i],
        ":address" => $address[$i]));
}

However, if you would like to put it into a single query, you should use $db->exec():

$query = "INSERT INTO staff (name,age,address) VALUES ";
$values = array();

for($i=0;$i<count($names);$i++)
{
    array_push($values, "('" . $name[$i] . "','" . $age[$i] . "','" . $address . "')");
}

$db->exec($query . implode("," . $values));

Be warned this method, contrary to the prepare and execute method, is vulnerable since it does not validate the input

score 0 · Answer 4 · answered May 05 '13 at 12:47

Add the delayed keyword to the insert (insert delayed ...) to reduce serverload, and just loop over the prepared statement with all values. MySQL will queue it internally anyway as separate insert statements if you do a big bunch of inserts, the extended syntax is just syntactic sugar.

inserting multiple rows in one query from an array

4 Answers4