PHP MySQL Transactions

Is there anyway to carry out an SQL transaction in one command? eg

mysql_query("
START TRANSACTION;
INSERT INTO table1 ....etc;
INSERT INTO table 2.....;
COMMIT;");

Or do you always hav开发者_JAVA百科e to use separate commands? eg

mysql_query("START TRANSACTION;");
mysql_query("INSERT INTO etc etc

Thanks

You can, by using PDO instead of pure mysql statements,

$connection = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$connection->beginTransaction();
$connection->exec('insert into table1...');
$connection->exec('insert into table2...');
$connection->commit();

Of course, this is just a quick example, you should use prepared statements instead and use bind variables for the user input so you won't have to worry that much about sql injections.

With the PHP mysql_query function it has to be separate calls.

MySql supports transactions if you use InnoDb as storage engine. The mysql api doesn't allow multiple statements to be passed to mysql_query, but since a transaction is per connection, you can just split it up over multiple calls.

If you don't want to use PDO you can use mysqli's multi_query commnad