MySQL: Multiple Insert Commands in one query

[Arielladog]

Is there a way to have multiple MySQL Insert commands? Here's the code I am trying to use from PHP, but it won't work:

INSERT INTO Teachers (UserName,PassWord) VALUES ('pilky','pilky'); INSERT INTO Teachers (UserName,PassWord) VALUES ('moore','moore')

How can I have it so that I can insert more than one new row?

aDog

[Hierophant]

Assign your values to an Array and use a while loop to insert them into the database.

1 Insert for 1 row.

[freddydoesphp]

But really to answer Ariella's question no you cannot make to inserts with one query to the database.

[Arielladog]

Hey guys,

Actually, I am creating the insert the thing from a while() loop, so it won't be hard to mysql_query() each one.

Ok, I can do that. Is there a way to detect if all of the new rows have been inserted ok? Would I have to check each mysql_query() seperately?

aDog

[freddydoesphp]

You could do under the insert statement
$i = 1;
while() {
//your insert stuff
if (mysql_affected_rows($result) == 0) {
print "Error Printing Row".$i;
}
$i++;
}

[Arielladog]

Thanx, I'll do something similar to that

aDog

P.S.-You sure got a smart cat

[Kevin Yank]

Originally posted by freddydoesphp
But really to answer Ariella's question no you cannot make to inserts with one query to the database.

I beg to differ:

INSERT INTO tblName (ColName1,ColName2,...) VALUES (Col1Value1,Col2Value1,...), (Col1Value2,Col2Value2,...), ...

Or to use ADog's example:

INSERT INTO Teachers (UserName,PassWord) VALUES ('pilky','pilky'), ('moore','moore')

[necrominator]

so it can be done, I always used loops.

[Arielladog]

And I had just finished scripting my thing the other way I'll change it arround and use that way as it's probably better to query the server only once.

Thanx kev,
aDog

[Kevin Yank]

Loops aren't bad if you're just inserting a couple of entries, but beyond a handful of entries, the loop becomes pretty unneccessarily intensive. More than 50 and you're shooting yourself in the foot.

If you do need a loop (say, if you're inserting an indeterminate number of entries), then you're better off looping through the construction of the multi-INSERT query, then issuing the one query when you're done.

[Arielladog]

Make up your mind

I'm already doing the loop, but, one person will probably add all the teachers at once (in a big school, could be 100+), so I'll try the one query

Thanx again

aDog
<Edited by Arielladog on 12-30-2000 at 12:34 AM>

[Vinay]

now only if you could do multiple SEPARATE inserts in the same line ! .. separate meaning different queries altogether .. i've had that issue in the past, dont know if the new mysql's can handle it

[Kevin Yank]

By 'separate' do you mean 'into multiple tables'?

If so, you've crossed into the realm of stored procedures, which MySQL does not yet support. You'll need a beefier database solution, like MS-SQL server, that supports such advanced features.