PHP MySQL Transactions

Question

Hi, 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 have to use separate commands? eg

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

Thanks

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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