Multiple SQL queries in a PHP string?

[MythicFrost]

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]

Best way to find out is to give it a try. Also look into SQL "transactions."

[MythicFrost]

Thanks!

[DJBenE]

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]

Thanks!

[MythicFrost]

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]

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]

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]

All right... thanks a lot guys :)

[DJBenE]

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]

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.