Where to rollback a transaction in PDO ?

Question

my problem is , i have a database design from this link http://stackoverflow.com/questions/3305825/is-my-database-overdesigned

edit* ok maybe useing transaction ? but where should i put the rollback if it fails ?

 $dbConnect->beginTransaction();
 $RegisterInsert = $dbConnect->prepare("INSERT INTO companies (
    `name`, `address`, `email`, `phone`, `link`, `verified`) VALUES (
    :name, :address, :email, :phone, :link, :verified)");
    $RegisterInsert->execute($RegisterData);

    $RegisterData2['CID'] = $dbConnect->lastInsertId();  

    $RegisterInsert = $dbConnect->prepare("INSERT INTO users_companies (
    `UID`, `CID`, `role`) VALUES (
    :UID, :CID, :role)");
    $RegisterInsert->execute($RegisterData2);
    $dbConnect->commit();

where should i put the rollback ?

Thanks

Answer 1

A transaction should end with either a rollback() or a commit(), (only one of them)

Its usually used with an if...else statement as logically only one of them should be executed.

$dbConnect->beginTransaction();

//somecode
//$dbConnect->execute( $someInsert );
//some more code
//$result = $dbConnect->execute( $someSelect );
//$nextRow = $result->fetchRow();

//either commit or rollback!
if( $someResultCheck == true )
    $dbConnect->commit();
else
    $dbConnect->rollback();

Transactions are usually used when there is a complex logic involved with queries.

In case you are using MySQL, make sure you are not using MyISAM engine for tables, as it doesn't support transactions.

Answer 2

As soon as you know that the transaction as a whole is going to fail then you should rollback what you've done so far and not try any further updates - so in pseudo-code:

 function do_updates(array updates)
 { 
    PDO->beginTransaction();
    foreach (updates as statement) {
       run statement
       if failed {
         PDO->rollback(); 
         return false;
       }
    }
    return PDO->commit();

HTH

C.