CODEWITHSUNDEEP
phpmysql
Dan
Dan

Reputation: 4150

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

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

Thanks

Upvotes: 0

Views: 219

Answers (4)

AntonioCS
AntonioCS

Reputation: 8506

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

Upvotes: 0

troelskn
troelskn

Reputation: 117615

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.

Upvotes: 0

Jimmy Stenke
Jimmy Stenke

Reputation: 11220

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.

Upvotes: 1

Greg
Greg

Reputation: 321854

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

Upvotes: 0

Related Questions