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'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'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.

 

[jayanti]

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

i am getting same error
ERROR: 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
how can i remove it and i didnt understand what you want to say about this link

 

[IHelpId10t5]

This is a purposeful restriction on the latest version of PHP to prevent SQL injection attacks. The answer and solution to your question is available here:

https://secure.php.net/manual/en/mysqli.quickstart.multiple-statement.php