Transactions, SQL within PHP (multiple queries)

[colombian]

I have been reading up on advance SQL, but I am confused about a couple of things.

 

In SQL (MySQL) land you would do:

BEGIN;

Query1(Select table1...)
Query2(update table2...)
Query3(update table3...)

COMMIT;

ROLLBACK;

This code as I understand will execute all three desired queries, only if they are all successful - rollback any changes if any of the queries failed for whatever reason (DB crash, full hard drive, etc).

 

My issue is that within PHP, inserting this code in a query string will basically not update anything past the 1st query. I hear it's a security mechanism to avoid some very dangerous SQL injections.

 

How do I go about replicating the above code within PHP?

I have heard some conflicting information on AUTOCOMMIT.

 

Thanks in advance.

 

 

[craygo]

The code also depends on what mode you are running. Mysql runs with AUTOCOMMIT on by default. So as soon as you run a query the information is committed to disk. AUTOCOMMIT can, or should, only be turned of if you use a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster).

 

Usually with php you would just run your query and check to make sure it was executed before you run another one based on the first.

 

<?php
$sql = "SELECT SUM(`field`) AS fieldsum FROM `table` WHERE `catagory` = '1'";
$result = @mysql_query($sql);  // The @ sign will suppress the error code
if($result){
$r = mysql_fetch_assoc($result);
$sum = $r['fieldsum']
// run update queries below

} else {
echo "Could not run select query. Error: ".mysql_error();
}
?>

 

I hope I am answering your question.

 

Ray

[colombian]

Thanks for the answer - it helps - but I still want to know how to use begin/committ etc.

 

However, I am interested in the transaction aspect - the rollback and commit, which your example would  not be able to address.

 

The idea is this:

(I use InnoDB, and would turn AUTOCOMMT off)

 

I don't want the first result to commit, until all the queries are successful.

That's why I want to go with transactions, and the full BEGIN - COMMIT - ROLLBACK.

 

any ideas of how I could use begin/commit/rollback within PHP?

 

thanks.

 

 

 

[craygo]

here's a nice little article with some functions

 

http://www.devarticles.com/c/a/MySQL/Using-Transactions-with-MySQL-4.0-and-PHP/

 

Ray

[craygo]

can also just use mysqli functions

 

http://us2.php.net/manual/en/mysqli.rollback.php

 

Ray