• From: India
  • Registered: 08/05/2013
  • Posts: 127

Topic: MySQL OR MySQLi Transaction

Can any one tell me

Try {} Catch {} Transaction for FA ?

I am using currently as :

db_query("START TRANSACTION");

$sqla ="INSERT INTO A () VALUES () ";
db_query($sqla);

$sqlb ="INSERT INTO B () VALUES () ";
db_query($sqlb);

$sqlCb ="UPDATE CB ()  SET ...   ";
db_query($sqlCb);

if ( $sqla and $sqlb and $sqlCb ) {
                db_query("COMMIT");
                echo display_notification_centered(_("Database transaction was successful."));
            } else {        
                db_query("ROLLBACK");
               echo display_notification_centered(_("transaction rolled back"));
                exit;
            }

I am Right ? ...

Can any one please tell me same in TRY .. CATCH ? ....

2 Reply by tester1 (edited by tester1 02/07/2014 07:58:09 am)

  • From: India
  • Registered: 08/05/2013
  • Posts: 127

Re: MySQL OR MySQLi Transaction

at-least admin should reply on this ...

  • From: Morocco
  • Registered: 11/13/2013
  • Posts: 31

Re: MySQL OR MySQLi Transaction

Checking the db_query() function shows that its returns false on an error. So use the return status. It will return false if it fails, which you can log (or whatever you want to do) and then continue.

$result_a = db_query($sqla);
$result_b = db_query($sqlb);
$result_Cb = db_query($sqlCb);

if ( $result_a && $result_b && $result_Cb ) {
                db_query("COMMIT");
                echo display_notification_centered(_("Database transaction was successful."));
            } else {       
                db_query("ROLLBACK");
               echo display_notification_centered(_("transaction rolled back"));
                exit;
            }

abdelghani

ostertagi's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: MySQL OR MySQLi Transaction

If the UPDATE returns 0 (zero) rows affected, it may evaluate to false.

apmuthu's Website

  • From: India
  • Registered: 08/05/2013
  • Posts: 127

Re: MySQL OR MySQLi Transaction

Let me try this one also

  • From: India
  • Registered: 08/05/2013
  • Posts: 127

Re: MySQL OR MySQLi Transaction

Below is what i have tried , please let me know which one is SAFE as i have many query and if any of my condition fails i need rollback + ERROR message ...


try {
  /* switch autocommit status to FALSE. Actually, it starts transaction */
  global $db;
 
  mysqli_autocommit($db, FALSE);

    $sqla ="INSERT INTO A () VALUES () ";
    $result_a = db_query($sqla);
   
    if($result_a){
   
    $sqlb ="INSERT INTO B () VALUES () ";
    $result_b =db_query($sqlb);
   
    if($result_b){
    $sqlCb ="UPDATE CB ()  SET ...   ";
    $result_Cb =db_query($sqlCb);
    }else { echo "ERROR" ; }
   
    } else { echo "ERROR" ; }

  mysqli_commit($db);
  echo 'Transaction completed successfully!';

} catch (Exception $e) {

  echo 'Transaction failed: ' . $e->display_notification_centered(_("transaction rolled back"));
  mysqli_rollback($db);
}

/* switch back autocommit status */
  mysqli_autocommit($db, TRUE);




//--------------------------------OR---------------------------------


begin_transaction();

$sqla ="INSERT INTO A () VALUES () ";
$result_a = db_query($sqla);

$sqlb ="INSERT INTO B () VALUES () ";
$result_b =db_query($sqlb);

$sqlCb ="UPDATE CB ()  SET ...   ";
$result_Cb =db_query($sqlCb);

if ( $result_a && $result_b && $result_Cb ) {
    commit_transaction();
    echo display_notification_centered(_("Database transaction was successful."));
} else {       
    cancel_transaction();
    echo display_notification_centered(_("transaction rolled back"));
    exit;
}


//--------------------------------OR---------------------------------



db_query("BEGIN");

$sqla ="INSERT INTO A () VALUES () ";
$result_a = db_query($sqla);

$sqlb ="INSERT INTO B () VALUES () ";
$result_b =db_query($sqlb);

$sqlCb ="UPDATE CB ()  SET ...   ";
$result_Cb =db_query($sqlCb);

if ( $result_a && $result_b && $result_Cb ) {
    db_query("COMMIT");
    echo display_notification_centered(_("Database transaction was successful."));
} else {       
    db_query("ROLLBACK");
    echo display_notification_centered(_("transaction rolled back"));
    exit;
}

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: MySQL OR MySQLi Transaction

Check out these links to prove that the UPDATE query check is not functioning as needed:

http://stackoverflow.com/questions/14908087/should-mysqli-update-queries-return-a-result
http://stackoverflow.com/questions/12990164/how-to-check-if-an-update-mysqli-query-is-correctly-executed
http://stackoverflow.com/questions/12744953/mysqli-returns-true-for-insert-query-row-not-inserted

UPDATE will return 0 rows affected if the changed state already exists and the mysql_query function will still return true. Hence remove the UPDATE check in the if statement.

apmuthu's Website

  • From: India
  • Registered: 08/05/2013
  • Posts: 127

Re: MySQL OR MySQLi Transaction

@apmuthu

Can you modify above code and let me know what exact you mean to say , as i got to know that :

mysql_affected_rows() should be more useful to return true update and insert.

Please help me with above example in your way. so that i would be more clear what you want to say.

9 Reply by tester1 (edited by tester1 02/14/2014 09:01:04 am)

  • From: India
  • Registered: 08/05/2013
  • Posts: 127

Re: MySQL OR MySQLi Transaction

What say NOW :


function autocommitfalse(){
      global $db;
      /* switch autocommit status to FALSE. Actually, it starts transaction */
      return mysqli_autocommit($db, FALSE);
}

function autocommittrue(){
      global $db;
      return mysqli_autocommit($db, TRUE);
}


function commitquery(){
     global $db;
     return mysqli_commit($db);
}

function rollbackedquery(){
     global $db;
     return mysqli_rollback($db);
}


======

try {
    
     autocommitfalse();

    $SQLSELECT = "SELECT * FROM BC ";
    $result_as = db_query($SQLSELECT);

    if(!$result_as ){
            throw new Exception('Wrong SQL SELECT: ' . $SQLSELECT_a. ' Error: '.db_error_msg($db) . db_error_no());
     }
 

    $sqla ="INSERT INTO A () VALUES () ";
    $result_a = db_query($sqla);

    if(!$result_a){
            throw new Exception('Wrong SQL SELECT: ' . $result_a. ' Error: '.db_error_msg($db) . db_error_no());
     }
 
   
    $sqlb ="INSERT INTO B () VALUES () ";
    $result_b =db_query($sqlb);
   
    if(!$result_b){
            throw new Exception('Wrong SQL SELECT: ' . $result_a. ' Error: '.db_error_msg($db) . db_error_no());
     }
  
    $sqlCb ="UPDATE CB ()  SET ...   ";
    $result_Cb =db_query($sqlCb);

    if($result_Cb === false  &&  mysqli_affected_rows($db) == 0 ){
            throw new Exception('Wrong SQL SELECT: ' . $result_a. ' Error: '.db_error_msg($db) . db_error_no());
     }
     

    commitquery();
    echo 'Transaction completed successfully!';

} catch (Exception $e) {

        echo"<br \>";
        echo "<table align=center><tr><td>";
        echo $e->getMessage();
        echo "</td></tr></table>";
        echo"<br \>";

        echo display_error(_("Transaction failed: transaction rolled back"));
        rollbackedquery();
   }        

        autocommittrue();

If something wrong ..please let me know with change in it ( above code ) ..so that i am more clear ....

Ohh i think still not rollback... hmm admin please help me guys please

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: MySQL OR MySQLi Transaction

Please be clear on what checks you want before a commit is envisaged:

Each insert must be successful - define what you mean by successful:
- whether the record was inserted or
- whether it already exists and did not need the insert or
- that the insert values were not acceptable for other reasons like field type and size checks.

Similarly about the definition of the UPDATE sql being successful:
- was the update successful in general
- was the update unnecessary as the record was already in the updated state
- was the update not acceptable at all for reasons like field type and size check failure.

apmuthu's Website