Multiple INSERTs in mysql_query()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Multiple INSERTs in mysql_query()

Post by anjanesh »

I want to insert more than one row into a table. I can do the for loop but Im trying to avoid querying many times. But Im not able to get this to work:

Code: Select all

mysql_select_db("test");
$sql="
INSERT INTO `test1` VALUES ('q','w','e','5');
INSERT INTO `test1` VALUES ('q','w','e','6');
INSERT INTO `test1` VALUES ('q','w','e','7');
INSERT INTO `test1` VALUES ('q','w','e','8');
";
$res=mysql_query($sql);
if (!$res) echo $sql.'<br>'.mysql_error();
Any idea what to do ? I tried adding \r\n after the semi colon but no luck.
Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

INSERT INTO `test1`
VALUES('q','w','e','5'),
VALUES('q','w','e','6'),
VALUES('q','w','e','7'),
VALUES('q','w','e','8'),
VALUES('q','w','e','9')
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Oh. So we can execute multiple queries at a time ? Like if $sql were :
"
SELECT * FROM table1
DELETE FROM table2
";
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't think that would work.. it definitely won't work without semicolons to seperate the commands.. but I do know that mysql at least allows multiple inserts like how I wrote it.
Serberus
Forum Newbie
Posts: 5
Joined: Fri Aug 13, 2004 6:31 pm
Location: Hertfordshire, UK

Post by Serberus »

You can compound multiple inserts together as shown above, you can't issue a SELECT and a DELETE in the same statement though, what would be passed back to PHP? How do you know which part of you query failed (or succeeded)? You want the result set from the SELECT and a TRUE/FALSE from the DELETE.
Post Reply