PDA

View Full Version : Multiple SQL queries in a PHP string?




MythicFrost
Aug 11, 2010, 02:40 AM
Hi,

Right now when I do I query its like this:

$sql = "INSERT INTO someTable (rowName) VALUES ('someText')";

Can I do this:

$sql = "INSERT INTO someTable (rowName) VALUES ('someText'); INSERT INTO anotherTable (rowName) VALUES ('someText')";
mysql_query($sql, $con);



angelwatt
Aug 11, 2010, 06:29 AM
Best way to find out is to give it a try. Also look into SQL "transactions."

MythicFrost
Aug 11, 2010, 07:23 AM
Thanks!

DJBenE
Aug 11, 2010, 11:12 AM
Hi,

Right now when I do I query its like this:

$sql = "INSERT INTO someTable (rowName) VALUES ('someText')";

Can I do this:

$sql = "INSERT INTO someTable (rowName) VALUES ('someText'); INSERT INTO anotherTable (rowName) VALUES ('someText')";
mysql_query($sql, $con);

In short...yes.

But, if you're inserting multiple rows into the same table:
$sql = "INSERT INTO someTable (rowName) VALUES ('someText'), ('someMoreText'), ('someMoreText');";

All together:
$sql = "INSERT INTO someTable (rowName) VALUES ('someText'), ('someMoreText'), ('someMoreText');
INSERT INTO anotherTable (rowName) VALUES ('someText');
INSERT INTO yetAnotherTable (rowName) VALUES ('someText'), ('someText');";
mysql_query($sql,$con);

MythicFrost
Aug 11, 2010, 06:52 PM
Thanks!

MythicFrost
Aug 12, 2010, 03:22 AM
Hi again,

I'm basically doing:
sql = "INSERT INTO table1 (row1) VALUES ('SomeValue'); INSERT INTO table2 (row2) VALUES ('SomeValue');";
if (!mysql_query($sql, $con)){
die(mysql_error());
}

And, all I get is an error that says:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO table2 (row2) VALUES ('0')' at line 1

Any idea whats wrong?

jSunbeam
Aug 12, 2010, 05:09 AM
Hi again,

I'm basically doing:
sql = "INSERT INTO table1 (row1) VALUES ('SomeValue'); INSERT INTO table2 (row2) VALUES ('SomeValue');";
if (!mysql_query($sql, $con)){
die(mysql_error());
}

And, all I get is an error that says:


Any idea whats wrong?

Yeah - you can't execute multiple SQL statements from PHP in the way that you're doing it (semi-colon separated), you just can't.
Have a look into using PDO for your database transactions: http://php.net/manual/en/book.pdo.php

microcolt
Aug 12, 2010, 05:13 AM
Whenever you do MySQL queries, try them in phpMyAdmin if possible and split everything up in lines until you zero-in on the problem.

MythicFrost
Aug 12, 2010, 08:00 AM
All right... thanks a lot guys :)

DJBenE
Aug 12, 2010, 01:40 PM
Hi again,

I'm basically doing:
sql = "INSERT INTO table1 (row1) VALUES ('SomeValue'); INSERT INTO table2 (row2) VALUES ('SomeValue');";
if (!mysql_query($sql, $con)){
die(mysql_error());
}

And, all I get is an error that says:


Any idea whats wrong?

Sorry, I was mistaken when showing you how to execute multiple queries using mysql_query, as I've been using mysqli for the past few years and have gotten used to it.
Using mysqli (http://www.php.net/manual/en/mysqli.multi-query.php)'s multi-query you can accomplish what you need, although you'll have to use mysqli instead of mysql.

MythicFrost
Aug 12, 2010, 06:46 PM
Sorry, I was mistaken when showing you how to execute multiple queries using mysql_query, as I've been using mysqli for the past few years and have gotten used to it.
Using mysqli (http://www.php.net/manual/en/mysqli.multi-query.php)'s multi-query you can accomplish what you need, although you'll have to use mysqli instead of mysql.
Oh ok, thanks. It's not a big deal, I've found a way to do it.