6
\$\begingroup\$

This is my code for doing a roll back with multiple MySQLI prepared statement.

I want to know if anything is wrong with the code.

 $newRegistrationDataArray = array(
    "date" => $date,
    "name" => $name,
    "address" => $address,
    "country" => $country,
    "occupation" => $occupation,
    "emailAddress" => $emailAddress,
    "memberID" => $memberID,
    "usename" => $usename,
    "password" => $password,
    "salt" => $salt,
    "secretQuestion" => $secretQuestion,
    "secretAnswer" => $secretAnswer,
    "transactionCode" => $transactionCode,
    "perfectMoneyAccount" => $perfectMoneyAccount,
    "egoPayAccount" => $egoPayAccount,
    "payeerAccount" => $payeerAccount,
    "okPayAccount" => $okPayAccount,
    "bitcoinAccount" => $bitcoinAccount,
    "upline" => $upline,
    "userRegistrationIp" => $userRegistrationIp,
    "status" => $status
);

public function CreateNewAccount(&$newRegistrationDataArray){ ///note the &

    /* set autocommit to off */
    $this->dbConnection->autocommit(FALSE);

    $stmt1=$this->dbConnection->prepare("INSERT INTO `members` (date,name,address,country,occupation,emailAddress,memberID,usename,password,salt,secretQuestion,secretAnswer,transactionCode,perfectMoneyAccount,egoPayAccount,payeerAccount,okPayAccount,bitcoinAccount,upline,userRegistrationIp,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    $stmt2=$this->dbConnection->prepare("INSERT INTO `membertotalfinancialstatus` (memberId) VALUES (?)");

    /* variable needed */
    $memberId = $this->generateMemberId(); // a private function that generate member id
    $date = $newRegistrationDataArray['date'];
    $name = $newRegistrationDataArray['name'];
    $address = $newRegistrationDataArray['address'];
    $country = $newRegistrationDataArray['country'];
    $occupation = $newRegistrationDataArray['occupation'];
    $emailAddress = $newRegistrationDataArray['emailAddress'];
    $usename = $newRegistrationDataArray['usename'];
    $password = $newRegistrationDataArray['password'];
    $salt = $newRegistrationDataArray['salt'];
    $secretQuestion = $newRegistrationDataArray['secretQuestion'];
    $secretAnswer = $newRegistrationDataArray['secretAnswer'];
    $transactionCode = $newRegistrationDataArray['transactionCode'];
    $perfectMoneyAccount = $newRegistrationDataArray['perfectMoneyAccount'];
    $egoPayAccount = $newRegistrationDataArray['egoPayAccount'];
    $payeerAccount = $newRegistrationDataArray['payeerAccount'];
    $okPayAccount = $newRegistrationDataArray['okPayAccount'];
    $bitcoinAccount = $newRegistrationDataArray['bitcoinAccount'];
    $upline = $newRegistrationDataArray['upline'];
    $userRegistrationIp = $newRegistrationDataArray['userRegistrationIp'];
    $status = $newRegistrationDataArray['status'];
    /* variable needed */

    $stmt1->bind_param('sssssssssssssssssssss',$date,$name,$address,$country,$occupation,$emailAddress,$memberID,$usename,$password,$salt,$secretQuestion,$secretAnswer,$transactionCode,$perfectMoneyAccount,$egoPayAccount,$payeerAccount,$okPayAccount,$bitcoinAccount,$upline,$userRegistrationIp,$status);
    $stmt2->bind_param('s',$memberId);

    try
    {
        if( ($stmt1->execute() == false) OR ($stmt2->execute() == false)){

            /// close statement, roll back and throw an exception
            $stmt1->close();
            $stmt2->close();

            $this->dbConnection->rollback();
            throw new Exception("AN INTERNAL ERROR HAS OCCURED. TRY AGAIN LATER.");
        }
    }
    catch(Exception $e)
    {
        $error=$e->getMessage();
        throw new Exception($error);    
    }

    $this->closeDBConn;
}
\$\endgroup\$
7
  • \$\begingroup\$ Does it work as intended? \$\endgroup\$ – Mathieu Guindon Sep 9 '14 at 19:45
  • \$\begingroup\$ I am not just sure about rolling back the 2 statement. if( ($stmt1->execute() == false) OR ($stmt2->execute() == false)){ /// close statement, roll back and throw an exception $stmt1->close(); $stmt2->close(); $this->dbConnection->rollback(); throw new Exception("AN INTERNAL ERROR HAS OCCURED. TRY AGAIN LATER."); } \$\endgroup\$ – user1427112 Sep 9 '14 at 19:47
  • \$\begingroup\$ If it doesn't work then this is off-topic for CodeReview and belongs on StackOverflow (it's not very clear from your comment whether it does or not) \$\endgroup\$ – Dan Sep 9 '14 at 19:52
  • \$\begingroup\$ +1 for nicely written, easy to read PHP. I'm sure you have your reasons for writing this in PHP, but have you looked into MySQL built-in transaction language? \$\endgroup\$ – Phrancis Sep 9 '14 at 19:52
  • \$\begingroup\$ @DanPantry. it is not about working, I only wanted to be sure if what i wrote is OKAY. \$\endgroup\$ – user1427112 Sep 9 '14 at 19:55
2
\$\begingroup\$
function CreateNewAccount(&$newRegistrationDataArray){ ///note the &


    /* variable needed */
    $memberId = $this->generateMemberId(); // a private function that generate member id

    // that is lots of repetitive code, lazy way is use extract,
    // although can be dangerous if you can't trust data in $newRegistrationDataArray
    extract($newRegistrationDataArray);

//    $date = $newRegistrationDataArray['date'];
//    $name = $newRegistrationDataArray['name'];
//    $address = $newRegistrationDataArray['address'];
//    $country = $newRegistrationDataArray['country'];
//    $occupation = $newRegistrationDataArray['occupation'];
//    $emailAddress = $newRegistrationDataArray['emailAddress'];
//    $usename = $newRegistrationDataArray['usename'];
//    $password = $newRegistrationDataArray['password'];
//    $salt = $newRegistrationDataArray['salt'];
//    $secretQuestion = $newRegistrationDataArray['secretQuestion'];
//    $secretAnswer = $newRegistrationDataArray['secretAnswer'];
//    $transactionCode = $newRegistrationDataArray['transactionCode'];
//    $perfectMoneyAccount = $newRegistrationDataArray['perfectMoneyAccount'];
//    $egoPayAccount = $newRegistrationDataArray['egoPayAccount'];
//    $payeerAccount = $newRegistrationDataArray['payeerAccount'];
//    $okPayAccount = $newRegistrationDataArray['okPayAccount'];
//    $bitcoinAccount = $newRegistrationDataArray['bitcoinAccount'];
//    $upline = $newRegistrationDataArray['upline'];
//    $userRegistrationIp = $newRegistrationDataArray['userRegistrationIp'];
//    $status = $newRegistrationDataArray['status'];
    /* variable needed */


    try
    {
        /* set autocommit to off */
        $this->dbConnection->autocommit(FALSE);

        $stmt1=$this->dbConnection->prepare("INSERT INTO `members` (date,name,address,country,occupation,emailAddress,memberID,usename,password,salt,secretQuestion,secretAnswer,transactionCode,perfectMoneyAccount,egoPayAccount,payeerAccount,okPayAccount,bitcoinAccount,upline,userRegistrationIp,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt1->bind_param('sssssssssssssssssssss',$date,$name,$address,$country,$occupation,$emailAddress,$memberID,$usename,$password,$salt,$secretQuestion,$secretAnswer,$transactionCode,$perfectMoneyAccount,$egoPayAccount,$payeerAccount,$okPayAccount,$bitcoinAccount,$upline,$userRegistrationIp,$status);

        // failure is unexpected, so lets use exceptions to handle it
        if ($stmt1->execute() == false) {
            throw new Exception('Statement 1 Failed');
        }

        $stmt2=$this->dbConnection->prepare("INSERT INTO `membertotalfinancialstatus` (memberId) VALUES (?)");
        $stmt2->bind_param('s',$memberId);

        if ($stmt2->execute() == false) {
            throw new Exception('Statement 2 Failed');
        }

        // tidy up
        $stmt1->close();
        $stmt2->close();

        // is it normal to close connection at this level? or do you leave it open for other requests
        $this->closeDBConn;
    }
    catch(Exception $e)
    {
        // any sort of exception and we should rollback?
        // either that or subclass exception and handle each exception type differently
        $this->dbConnection->rollback();

        // whats the point of this, just re-throw the same exception?
        // $error=$e->getMessage();
        // throw new Exception($error);
        throw $e;
    } finally {
       // if you have php 5.5 you can do your tidy up here, although if 
       // an exception has occured, you probably don't care about freeing stmt's as
       // they all end up in the garbage anyway

        // tidy up
        //$stmt1->close();
        //$stmt2->close();
    }

}
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.