PHP + MySQL Commit?

[xrvel]

I have several MySQL queries which must be done perfectly on one execution.
I heard about "transaction" which supports "commit" and "rollback" or such.

But i don't know what is the PHP code to do this.
I heard about Pear DB but i read on their website that it is somewhat "outdated".

Does anyone know what code should i use?

 

[qbert220]

To use transactions, you just need to execute the right queries from your PHP script (obviously make sure your database supports transactions):

mysql_query("START TRANSACTION");
mysql_query("UPDATE t1 SET field='value1'");
mysql_query("UPDATE t2 SET field='value2'");
mysql_query("COMMIT");

If your requirement is that you don't want other MySQL processes reading part modified tables, it may be better to use a table write lock, like this:

mysql_query("LOCK TABLES t1 WRITE, t2 WRITE");
mysql_query("UPDATE t1 SET field='value1'");
mysql_query("UPDATE t2 SET field='value2'");
mysql_query("UNLOCK TABLES t1, t2");

No other MySQL process can access the t1 or t2 tables whilst they are locked.

Alternativly, if your transactions are simple, combine them into a single MySQL transaction. For example:

mysql_query("UPDATE t1, t2 SET t1.field='value1', t2.field='value2'");

 

[xrvel]

Thanks, i didn't know that before :tu:
I'll try it for sure :tu:

 

[Kate]

If you have mySQL 5+ have a look at stored procedures.