CODEWITHSUNDEEP
phpmysqlpdo
lampshade
lampshade

Reputation: 2796

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

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));

Upvotes: 0

Views: 134

Answers (4)

LosManos
LosManos

Reputation: 7692

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
));

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212412

$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

Upvotes: 2

tadman
tadman

Reputation: 211590

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;

Upvotes: 1

George Brighton
George Brighton

Reputation: 5151

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.

Upvotes: 0

Related Questions