PHP + MySQL transactions examples

Question

I really haven't found normal example of PHP file where MySQL transactions are being used. Can you show me simple example of that?

And one more question. I've already done a lot of programming and didn't use transactions. Can I put a PHP function or something in header.php that if one mysql_query fails, then the others fail too?

Answer 1

The idea I generally use when working with transactions looks like this (semi-pseudo-code) :

try {
    // First of all, let's begin a transaction
    $db->begin();

    // A set of queries ; of one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed ; and we can commit the transaction
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
}

Note that, with this idea, if a query fails, an Exception must be thrown :

• PDO can do that, depending on how you configure it
  • See PDO::setAttribute
  • and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
• else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved : you cannot just put an instruction somewhere and have transactions done automatically : you still have to specific which group of queries must be executed in a transaction.

For example, quite often, you'll have a couple of queries before the transaction (before the begin), and another couple of queries after the transaction (after either commit or rollback) ; and you'll want those queries executed no matter what happened (or not) in the transaction.