Where to rollback a transaction in PDO?

my problem is , i have a database design from this link 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)");开发者_Python百科
    $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

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.

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.