• We generally use when working with transactions looks like this (semi-pseudo-code):
jQuery Code 
try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if 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();
}
[ad type=”banner”]

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.

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.

jQuery Code 
mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and $a2) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}

  • we made a function to get a vector of queries and do a transaction, maybe someone will find out it useful:
jQuery Code 
function transaction ($con, $Q){
        mysqli_query($con, "START TRANSACTION");

        for ($i = 0; $i < count ($Q); $i++){
            if (!mysqli_query ($con, $Q[$i])){
                echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>';
                break;
            }   
        }

        if ($i == count ($Q)){
            mysqli_query($con, "COMMIT");
            return 1;
        }
        else {
            mysqli_query($con, "ROLLBACK");
            return 0;
        }
    }

  • One more procedural style example with mysqli_multi_query, assumes $query is filled with semicolon-separated statements.
jQuery Code 
mysqli_begin_transaction ($link);

for (mysqli_multi_query ($link, $query);
    mysqli_more_results ($link);
    mysqli_next_result ($link) );

! mysqli_errno ($link) ?
    mysqli_commit ($link) : mysqli_rollback ($link);

  • check which storage engine you are using. If it is MyISAM, then Transaction(‘COMMIT’,’ROLLBACK’) will not be supported because only the InnoDB storage engine, not MyISAM, supports transactions.

PHP MySQL transaction example

  • First, execute the following statement to create the accounts table:
jQuery Code 
CREATE TABLE accounts (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR (50)    NOT NULL,
    amount DECIMAL (19, 4) NOT NULL
);
Second, insert two rows into the accounts table:

[ad type=”banner”]

  • Second, insert two rows into the accounts table:
jQuery Code 
INSERT INTO accounts(name,amount)
VALUES('John',25000),
      ('Mary',95000);
  • Third, query the accounts table:

SELECT * FROM accounts;

Categorized in:

MYSQLPHP

Tagged in:

, , , , , , , , , , , ,