Insert different values in array in MySQL in the same columns with just 1 insert

Question

How can I do this:

for ($i=0; $i<$number; $i++)
{
    mysql_query("INSERT INTO blah (foo, bar) VALUES (".$array[$i].", 1)");
}

With just one INSERT?

Is it possible?

PS: I know mysql_query is deprecated.

INSERT INTO blah(foo, bar) VALUES (...), (...), (...), (...), ... — Lukasz Szozda
– Lukasz Szozda, Commented Oct 5, 2015 at 16:11
If $number is not a known constant, it is impossible to do this without looping at some stage. — Ben
– Ben, Commented Oct 5, 2015 at 16:12
either you loop and run the query multiple times, or you loop to build up an extended-syntax insert query and then call it once. either way, you're looping. what you want is not possible WITHOUT loops. — Marc B
– Marc B, Commented Oct 5, 2015 at 16:13
I'll update the question. My intent is not to make multiple Inserts. I want to use just 1 Insert. — Rafael
– Rafael, Commented Oct 5, 2015 at 17:20

Lukasz Szozda · Accepted Answer · 2015-10-05 17:20:53Z

1

You can pass multiple VALUES in INSERT statement like:

INSERT INTO blah(foo, bar) 
VALUES (...), (...), (...), (...),...

answered Oct 5, 2015 at 17:20

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Comments

Jeroen Flamman · Accepted Answer · 2015-10-05 16:19:04Z

0

You can do:

$stmt = "";

for ($i = 0; $i < $number; $i++) {
    $stmt .= "INSERT INTO blah (foo, bar) VALUES (" . $array[$i] . ", 1);";
}

//deprecated: mysql_multi_query($stmt);
mysqli_multi_query($stmt);

answered Oct 5, 2015 at 16:19

Jeroen Flamman

9556 silver badges10 bronze badges

1 Comment

Rafael Over a year ago

This way I'll still make multiple Inserts.

Collectives™ on Stack Overflow

Insert different values in array in MySQL in the same columns with just 1 insert

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related