inserting multiple rows in one query from an array [duplicate]

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.

Answer 1

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.