Inserting multiple rows at once. How can this be shortened?

Question

I have to insert a few rows at once and every row gets the same value. Is there a way to shorten that query?

$stmt = $db->prepare('
    INSERT INTO `tablename`
    (`value`) VALUES (?), (?), (?), (?), (?)
');
$stmt->execute(array($value, $value, $value, $value, $value));

Do you mean row (then see my answer below) or field? If the latter probably no. — LosManos
– LosManos, Commented Sep 11, 2013 at 15:39
That's what a fully qualified INSERT query looks like. Are you arguing that the SQL standard should be changed? — tadman
– tadman, Commented Sep 11, 2013 at 15:42
@tadman No, no, that's not my intention. The question is more like: "Can I re-use a value multiple times in a prepared statement". Hope this makes it clearer. — lampshade
– lampshade, Commented Sep 11, 2013 at 15:46

Mark Baker · Accepted Answer · 2013-09-11 15:45:29Z

2

$count = 5;
$value = 'HELLO';
$stmt = $db->prepare('
    INSERT INTO `tablename`
    (`value`) VALUES ' . implode(', ', array_fill(0, $count, '(?)')) );
$stmt->execute(array_fill(0, $count, $value));

Though I'm not sure I see much value in filling n rows of a table with identical values

answered Sep 11, 2013 at 15:45

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tadman Over a year ago

Shorter and more flexible at the expense of maintainability. But shorter.

lampshade Over a year ago

+1 I think I'll go with this, as it's more readable and even better maintainable then having lots of (?). :) Thanks.

sybear Over a year ago

@MarkBaker I know this is not the most appropriate way to ask and so on, but could you please take a look at my question related to PHPExcel? stackoverflow.com/questions/18742595/… I would be really grateful if you could help, though you do not have to answer.

tadman · Accepted Answer · 2013-09-11 15:45:18Z

1

If you can put the values in another table, you can repeatedly INSERT these:

CREATE TEMPORARY TABLE _values (v VARCHAR(255));
INSERT INTO _values ("x","x","x");

/* Repeat as necessary */
INSERT INTO tablename (`value`) SELECT v FROM _values;

answered Sep 11, 2013 at 15:45

tadman

212k23 gold badges237 silver badges266 bronze badges

1 Comment

lampshade Over a year ago

+1 This looks very interesting and is probbaly very fast when inserting lots of lots of rows? I'll keep that in mind, for later. Sorry that I can only accept one answer. Still many thanks for sharing this and for your effort.

George Brighton · Accepted Answer · 2013-09-11 15:42:09Z

0

Seeing as you're using prepared statements, why not call execute() on a loop?:

$rowsToInsert = 5;

$stmt = $db->prepare('INSERT INTO `tablename` (`value`) VALUES (?)');
for($i = 0; $i < $rowsToInsert; $i++) {
    $stmt->execute(array($value));
}

It's certainly much easier to control programmatically than a single query multi-row insert.

answered Sep 11, 2013 at 15:42

George Brighton

5,1719 gold badges30 silver badges36 bronze badges

3 Comments

tadman Over a year ago

This is also a lot slower since each INSERT is a transaction and will have MVCC and indexing overhead.

George Brighton Over a year ago

If performance is an issue, PDO's beginTransaction() and commit() could be used :/

tadman Over a year ago

There's still overhead for each statement. A mass-insert will win nearly every time, especially if there's a large number of rows being inserted.

LosManos · Accepted Answer · 2013-09-11 15:45:38Z

0

I am not sure if below works.
But at least it gives you the syntax for several insertions at once.

$stmt = $db->prepare('
INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (?, ?, ?, ?),
  (?, ?, ?, ?),
  (?, ?, ?, ?),
  (?, ?, ?, ?);
');
$stmt->execute(
    array(
        $value, $value, $value, $value, $value, 
        $value, $value, $value, $value, $value, 
        $value, $value, $value, $value, $value, 
        $value, $value, $value, $value, $value
));

edited Sep 11, 2013 at 15:45

answered Sep 11, 2013 at 15:38

LosManos

7,8126 gold badges62 silver badges120 bronze badges

3 Comments

tadman Over a year ago

This answer is not exactly answering anything. This is longer.

lampshade Over a year ago

This isn't really shorter, isn't it? ;) And I only need to set one value per row. But it gets very long when inserting 10 or even more rows.

LosManos Over a year ago

You can create the array with a function. Then you can create the SQL with a function too. That would make your code short and readable.

Collectives™ on Stack Overflow

Inserting multiple rows at once. How can this be shortened?

4 Answers 4

3 Comments

1 Comment

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related