0

I just wondering if there is a possibility to insert into the database two tables at once like,

$query = sprintf("INSERT INTO table ('tableid')values('somevaleu'),INSET INTO table1 ('table1id')values('somevalue')");

or I need to do it separately like,

$query1 = sprintf("INSERT INTO table ('tableid')values('somevaleu')");
$query2 = sprintf("INSET INTO table1 ('table1id')values('somevalue')");

Any suggestions?

thegrede
  • 492
  • 1
  • 6
  • 18
  • http://stackoverflow.com/questions/802437/how-to-execute-two-mysql-queries-as-one-in-php-mysql – iDev247 Jun 18 '12 at 20:11
  • Which MySQL extension are you using, the old `mysql_*` functions, mysqli, or PDO? Mysqli and PDO support multiple statements in one query. – drew010 Jun 18 '12 at 20:11
  • write querys separately is best procedure – SureshKumar Vegesna Jun 18 '12 at 20:11
  • If by `at once` you mean execute both queries at the same time in parallel then no it is not possible. And for readability, seperate looks clearer. – Ryan Jun 18 '12 at 20:12
  • @thegrede okay then no, you will have to issue them one at a time. You could insert multiple rows into the same table but not different tables in one query. – drew010 Jun 18 '12 at 20:13
  • @drew010 Thank you, I think this is the answer for my question, – thegrede Jun 18 '12 at 20:14
  • @thegrede ok, I added it as an answer w/ a bit more detail. – drew010 Jun 18 '12 at 20:18
  • @thegrede Please don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://news.php.net/php.internals/53799) (see the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)). Instead you should learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). – Ricardo Altamirano Jun 18 '12 at 20:22
  • @thegrede If you can't decide which, [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you. If you care to learn, [here is good PDO tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). Even though many beginner tutorials still use `mysql_*` functions, this doesn't mean they are acceptable. As a beginner you should learn the modern libraries that are both easier and more secure. – Ricardo Altamirano Jun 18 '12 at 20:23

4 Answers4

1

If you are using the old mysql extension (mysql_* functions), then you cannot issue multiple queries in one statement.

From the manual1:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

If you were using mysqli or pdo mysql then you can do multiple inserts in one query.

It would also be possible to insert multiple rows into the same table in one query using extended inserts, but not two different tables.

drew010
  • 68,777
  • 11
  • 134
  • 162
1

Why are you trying to do this? If it's just laziness, please just take the extra thirty seconds and use two separate statements. If, OTOH, you want to do this because you need to make both inserts at the same time to keep your database consistent (e.g. updating an "orders" table and a "billing" table at the same time), you want to look into transactions.

Basically, a transaction (started with BEGIN) is a bunch of queries that get bundled together and, when you're done, you can either COMMIT and make them stick or ROLLBACK to throw them away (such as if there's an error and you want to abort the process). They're not exactly executed at the same time but, on a COMMIT, they will either all succeed or none of them will go through.

Sean McSomething
  • 6,376
  • 2
  • 23
  • 28
  • 2
    And note that in order to use transactions, one must use InnoDB and not the more commonly default engine MyISAM. – drew010 Jun 18 '12 at 20:30
0

You can use begin to enclose multiple SQL statements:

$query = "BEGIN ";
$query .= "INSERT INTO table ('tableid')values('somevalue') INSERT INTO table1 ('table1id')values('somevalue') ";
$query .= "COMMIT";

Then run $query.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
0

It depends on how your database is being queried. A lot of the 3rd-party database abstraction libraries I've used only allow one SQL statement per execution. But MySQL can accept multiple queries separated by a semicolon, as you propose.

curtisdf
  • 4,130
  • 4
  • 33
  • 42